--约束 --创建部门表 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'));
Oracle表约束的sql操作
最新推荐文章于 2021-04-12 08:04:38 发布