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; --删除约束,若要删除主键约束,必须要级联