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);
--删除部门编号为1
DELETE 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'));