Oracle表约束的sql操作

--约束--创建部门表CREATE TABLE DEPARTMENT(       DEPTNO NUMBER(10),       DEPTNAME VARCHAR2(30),       DEPTADDRESS VARCHAR2(30),       DESCRB VARCHAR2(100));INSERT INTO DEPARTMENT(DEPTNO,DEPTNAME,DEPTADDRESS,DESCRB) VALUES(1,'java','长沙','很好');INSERT INTO DEPARTMENT(DEPTNO,DEPTNAME,DEPTADDRESS,DESCRB) VALUES(2,'C++','深圳','很棒');INSERT INTO DEPARTMENT(DEPTNO,DEPTNAME,DEPTADDRESS,DESCRB) VALUES(3,'PHP','北京','很酷');INSERT INTO DEPARTMENT(DEPTNO,DEPTNAME,DEPTADDRESS,DESCRB) VALUES(4,'HTML','长沙','很帅');INSERT INTO DEPARTMENT(DEPTNO,DEPTNAME,DEPTADDRESS,DESCRB) VALUES(5,'CSS','深圳','很靓');--创建职工表CREATE TABLE EMPLOY(       EMPNO NUMBER(10),       ENAME VARCHAR2(20),       AGE NUMBER(3),       SAL NUMBER(8,2),       PHONE VARCHAR2(11),       DEPTNO NUMBER(10));--添加数据INSERT INTO EMPLOY(EMPNO,ENAME,AGE,SAL,PHONE,DEPTNO) VALUES(10,'luckyfrog',18,10000,'18888888888',1); INSERT INTO EMPLOY(EMPNO,ENAME,AGE,SAL,PHONE,DEPTNO) VALUES(20,'青青',18,10000,'18888888888',2);UPDATE DEPARTMENT SET DEPTADDRESS='深圳' WHERE DEPTNO=3;SELECT * FROM DEPARTMENT;SELECT * FROM EMPLOY;--添加主键 非空且不可重复ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPTNO PRIMARY KEY(DEPTNO);ALTER TABLE DEPARTMENT ADD PRIMARY KEY(DEPTNO);ALTER TABLE EMPLOY ADD CONSTRAINT PK_EMPNO PRIMARY KEY(EMPNO);--删除主键ALTER TABLE DEPARTMENT DROP CONSTRAINT PK_DEPTNO;--非空约束 不能为空--创建非空约束ALTER TABLE DEPARTMENT MODIFY DEPTNAME NOT NULL;ALTER TABLE EMPLOY MODIFY ENAME NOT NULL;ALTER TABLE EMPLOY ADD CONSTRAINT CK_ENAME CHECK(ENAME IS NOT NULL);--删除约束ALTER TABLE DEPARTMENT DROP CONSTRAINT sys_c0011107;--唯一约束ALTER TABLE DEPARTMENT ADD CONSTRAINT UN_DESCRB UNIQUE(DESCRB);--检查约束ALTER TABLE DEPARTMENT ADD CONSTRAINT CK_ADDRESS CHECK(DEPTADDRESS IN('长沙','深圳'));ALTER TABLE EMPLOY ADD CONSTRAINT CK_AGE CHECK(AGE>0 AND AGE<300);ALTER TABLE EMPLOY ADD CONSTRAINT CK_SAL CHECK(SAL>5000);ALTER TABLE EMPLOY ADD CONSTRAINT CK_PHONE CHECK(PHONE>10000000000);--外键约束1 当主表中的内容有子记录的时候，数据不能被删除ALTER TABLE EMPLOY ADD CONSTRAINT FK_EMPDEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO);--删除部门编号为1DELETE FROM DEPARTMENT WHERE DEPTNO=1;DELETE FROM DEPARTMENT WHERE DEPTNO=2;--删除外键ALTER TABLE EMPLOY DROP CONSTRAINT FK_EMPDEPTNO;--外键约束2 删除的时候，当主表中的内容有子记录的时候，子表中的内容同样被删除ALTER TABLE EMPLOY ADD CONSTRAINT FK_EMPDEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO) ON DELETE CASCADE;--外键约束3 删除的时候，当主表中的内容有子记录的时候,子表中的外键字段值置为空ALTER TABLE EMPLOY ADD CONSTRAINT FK_EMPDEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO) ON DELETE SET NULL;--删除主表同时将子表中的外键删除DROP TABLE DEPARTMENT CASCADE CONSTRAINT;--约束状态--禁止无效 将约束关闭，对旧数据和新数据都不进行约束检查ALTER TABLE DEPARTMENT DISABLE NOVALIDATE CONSTRAINT  CK_ADDRESS;ALTER TABLE DEPARTMENT MODIFY CONSTRAINT CK_ADDRESS DISABLE NOVALIDATE;--激活无效 对旧数据不进行约束检查，对新数据进行约束检查ALTER TABLE DEPARTMENT ENABLE NOVALIDATE CONSTRAINT CK_ADDRESS;ALTER TABLE DEPARTMENT MODIFY CONSTRAINT CK_ADDRESS ENABLE NOVALIDATE;--禁止有效 对旧数据进行约束检查的，不能对表中数据进行增删改ALTER TABLE DEPARTMENT DISABLE VALIDATE CONSTRAINT CK_ADDRESS;ALTER TABLE DEPARTMENT MODIFY CONSTRAINT CK_ADDRESS DISABLE VALIDATE;--激活有效 对旧数据和新数据都进行约束检查 ALTER TABLE DEPARTMENT ENABLE VALIDATE CONSTRAINT CK_ADDRESS;ALTER TABLE DEPARTMENT MODIFY CONSTRAINT CK_ADDRESS ENABLE VALIDATE;--延迟性约束ALTER TABLE DEPARTMENT ADD CONSTRAINT UN_DESCRB UNIQUE(DESCRB) INITIALLY DEFERRED;--非延迟性约束ALTER TABLE DEPARTMENT ADD CONSTRAINT UN_DESCRB UNIQUE(DESCRB) INITIALLY IMMEDIATE;--创建表的时候就创建约束--创建一张房间表CREATE TABLE HOUSE(       HOUSENO NUMBER(5) CONSTRAINT PK_HOUSENO PRIMARY KEY,       HOUSENAME VARCHAR2(30) CONSTRAINT UN_NAME UNIQUE,       ADDRESS VARCHAR2(30) NOT NULL,       AREA NUMBER(5) NOT NULL CONSTRAINT CK_AREA CHECK(AREA>20));--创建一张书本表CREATE TABLE BOOK(       BOOKID NUMBER(10) CONSTRAINT PK_BOOKID PRIMARY KEY,       BOOKNAME VARCHAR2(30) CONSTRAINT UN_NAME1 UNIQUE,       BOOKAUTHOR VARCHAR2(30) CONSTRAINT CK_AUTHOR CHECK(BOOKAUTHOR IN('江南风','LOCKYFROG','青青')),       HOUSENO NUMBER(5),       PRICE NUMBER(5,2) CONSTRAINT CK_PRICE CHECK(PRICE>20),       CONSTRAINT FK_HOUSENO FOREIGN KEY(HOUSENO) REFERENCES HOUSE(HOUSENO) ON DELETE CASCADE);--修改约束名ALTER TABLE BOOK RENAME CONSTRAINT UN_NAME1 TO UNBOOKNAME; --删除唯一约束ALTER TABLE BOOK DROP CONSTRAINT UNBOOKNAME;--更改成检查约束ALTER TABLE BOOK ADD CONSTRAINT CK_BOOKNAME CHECK(BOOKNAME IN('JAVA','HTML','JS','ORACLE'));