ORACLE 表的CHECK约束问题

Question

CREATE TABLE ly_csee_tab
(  tno                    NUMBER        NOT NULL,
   cno                     NUMBER     NOT NULL,
   cname                    VARCHAR2(50)      NOT NULL,
   ckind                   VARCHAR2(100)         NOT NULL CHECK(ckind='必修课' OR ckind='选修课') ,
   ccredit                 NUMBER                NOT NULL,
   ccount                  NUMBER                NOT NULL CHECK(ccount>0) ,
   cadr                    VARCHAR2(100)         NOT NULL,
   cmon                    VARCHAR2(50)          NULL,
   ctur                    VARCHAR2(50)          NULL,
   cwen                    VARCHAR2(50)          NULL,
   scat                    VARCHAR2(50)          NULL,
   cfri                    VARCHAR2(50)          NULL,
   cpeo                    NUMBER                NOT NULL CHECK(cpeo>=0 AND cpeo<=ccount))
TABLESPACE &purc_data
STORAGE (&normal)
/
ALTER TABLE ly_csee_tab
    ADD ( CONSTRAINT ly_csee_pk PRIMARY KEY ( cno)
      USING INDEX
            TABLESPACE &purc_index
            STORAGE (&normal))
最后的CHECK（cpeo<=0 AND cpeo<=ccount) 调试有错~~

Answer 1

ccount要是个常量吧...你把cpeo<=ccount换常量看

Answer 2

ccount表示一个课程规定的人数，
cpeo表示一个课程已选的人数。ccount是个变量啊，换成常量就失去意义了。。

Answer 3

CREATE TABLE ly_csee_tab
(  tno                    NUMBER        NOT NULL,
   cno                     NUMBER     NOT NULL,
   cname                    VARCHAR2(50)      NOT NULL,
   ckind                   VARCHAR2(100)         NOT NULL CHECK(ckind='必修课' OR ckind='选修课') ,
   ccredit                 NUMBER                NOT NULL,
   ccount                  NUMBER                NOT NULL CHECK(ccount>0) ,
   cadr                    VARCHAR2(100)         NOT NULL,
   cmon                    VARCHAR2(50)          NULL,
   ctur                    VARCHAR2(50)          NULL,
   cwen                    VARCHAR2(50)          NULL,
   scat                    VARCHAR2(50)          NULL,
   cfri                    VARCHAR2(50)          NULL,
   cpeo                    NUMBER                NOT NULL,
 CHECK(cpeo>=0 AND cpeo<=ccount))
TABLESPACE &purc_data
STORAGE (&normal)
搞定了~~把最后的约束搞成表级约束就可以了~~