【oracle】完整性约束

CREATE TABLE STUDENT

 (SNOCHAR(10) ,

 SNAME CHAR(25),

 SSEX CHAR(5),

 SAGE NUMBER(2),              --CONSTRAINT,为约束对象取个别名

 SDEPT CHAR(25),

 CONSTRAINT PK_SNO PRIMARY KEY(SNO),

 CONSTRAINT UK_SNAME UNIQUE(SNAME)

);

 

CREATE TABLE COURSE

 (CNOCHAR(5),

 CNAME CHAR(50) NOT NULL,

  CPNOCHAR(5),                   --非空约束（NK）出错时会出现完整的出错提示，所以不需要用CONSTRAINT来取别名

 CCREDIT INT,

 CONSTRAINT PK_CNO PRIMARY KEY(CNO)

);

 

CREATE TABLE SC

 (SNOCHAR(10),

  CNOCHAR(5),

 GRADE INT,

 CONSTRAINT PK_SNO_CNO PRIMARY KEY(SNO,CNO),        --主码有两个属性构成

 CONSTRAINT FK_SNO FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),

 CONSTRAINT FK_CNO FOREIGN KEY(CNO) REFERENCES COURSE(CNO)

);

 

CREATE TABLE MEMBER

(MID NUMBER,

 NAMEVARCHAR2(200) NOT NULL,

 EMAIL VARCHAR2(50),

 AGENUMBER,

 SEXVARCHAR2(10)，

 CONSTRAINT PK_MID_NAME PRIMARY KEY(MID,NAME),

 CONSTRAINT UK_EMAIL UNIQUE(EMAIL),

 CONSTRAINT CK_SEX CHECK(SEX IN('男','女')),        --检查约束

 CONSTRAINT CK_AGE CHECK(AGE BETWEEN 0 AND 200)

);

 

SELECT *

FROM user_constraints                                          --通过数据字典查看约束

WHERE TABLE_NAME='MEMBER'; 

 

ALTER TABLE MEMBER

ADD CONSTRAINT PK_MID        --增加主键约束

PRIMARY KEY(MID);

 

ALTER TABLE MEMBER

ADD CONSTRAINT CK_AGE        --增加检查约束

CHECK(AGE BETWEEN 0 AND 200);

 

ALTER TABLE MEMBER

MODIFY(NAME VARCHAR2(20) NOT NULL);        --只能通过此方法增加非空约束

 

ALTER TABLE MEMBER

DISABLE CONSTRAINT UK_EMAIL;      --禁用UK_EMAIL约束

 

ALTER TABLE STUDENT 

DISABLE CONSTRAINT PK_SNO CASCADE;  --字表中有约束，必须使用CASCADE才能成功

 

ALTER TABLE MEMBER

ENABLE CONSTRAINT UK_EMAIL;   --启用约束,若想启用，必须删掉表中违反约束的数据

 

ALTER TABLE STUDENT

ENABLE CONSTRAINT PK_SNO;     --启用约束

 

ALTER TABLE MEMBER

DROP CONSTRAINT UK_EMAIL;         --删除约束，若要删除主键约束，必须要级联