Oracle笔记15——Oracle约束

约束

约束	说明
NOT NULL	非空约束，指定某列的所有行数据不能包含空值
UNIQUE	唯一约束，指定列或者列的组合的所有行数据必须唯一
PRIMARY KEY	主键约束，表的每行的唯一性标识，指定列或者列的组合的所有行数据必须唯一
FOREIGN KEY	外键约束，在列及引用列上建立的一种强制依赖关系
CHECK	检查性约束，在列上指定一个必须满足的条件

一、

NOT NULL(非空约束):不能为NULL 或者 ''
1.约束的列值不能为NULL
2.只能定义列级别的约束
3.可以不用指定约束名称

1.创建表emp_bak,指定ename不能为NULL
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10) NOT NULL,--添加非空约束
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--测试
INSERT INTO emp_bak(empno, ename) VALUES(1001, '');--添加数据失败，ename不能为空
INSERT INTO emp_bak(empno, ename) VALUES(1002, 'Lynn');--添加数据成功
SELECT * FROM emp_bak;
--删除表
DROP TABLE emp_bak;

二、

UNIQUE(唯一约束、唯一键):
1.约束的列值必须唯一，但是可以为NULL
2.既能定义列级别约束（单列QNIQUE约束），也可以定义表级别的约束（多列组合UNIQUE约束）
3.建议指定约束名称
4.创建唯一约束时，默认给所有约束的列添加索引INDEX,用于优化查询效率
5.一个表可以同时定义多个UNIQUE唯一约束
6.可以给一列或多列定义UNIQUE唯一约束

1.创建表emp_bak,指定empno必须唯一、同时ename必须唯一，不指定约束名称
CREATE TABLE emp_bak(
       empno NUMBER(4) UNIQUE,--不指定约束名称
       ename VARCHAR2(10) UNIQUE,--不指定约束名称
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--测试
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Tom');--添加失败，违反唯一约束条件(SCOTT.SYS_C0010811)
INSERT INTO emp_bak(empno, ename) VALUES(1002, 'Lynn');--添加失败，违反唯一约束条件(SCOTT.SYS_C0010812)
--删除表
DROP TABLE emp_bak;

2.创建表emp_bak,指定empno必须唯一、同时ename必须唯一，指定约束名称
CREATE TABLE emp_bak(
       empno NUMBER(4) CONSTRAINT emp_bak_empno_unique UNIQUE,--指定约束名称(列级别约束)
       ename VARCHAR2(10) CONSTRAINT emp_bak_ename_unique UNIQUE,--指定约束名称(列级别约束)
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--相当于：
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empno_unique UNIQUE(empno),--指定约束名称(表级别约束)
       CONSTRAINT emp_bak_ename_unique UNIQUE(ename)--指定约束名称(表级别约束)
);
--测试
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Tom');--添加失败，违反唯一约束条件(SOCTT.EMP_BAK_EMPNO_UNIUQE)
INSERT INTO emp_bak(empno, ename) VALUES(1002, 'Lynn');--添加失败，违反唯一约束条件(SOCTT.EMP_BAK_ENAME_UNIUQE)
--删除表
DROP TABLE emp_bak;
SELECT * FROM emp_bak;

--同时给多列定义组合唯一约束
1.创建表emp_bak,指定empno、ename组合唯一
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empnoandename_unique UNIQUE(empno, ename)--组合约束，只能定义表级别约束
);
--测试
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Lynn');--插入数据成功
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Tom');--插入数据成功
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');--违反唯一约束条件(SOCTT.EMP_BAK_EMPNOANDENAME_UNIUQE)
--删除表
DROP TABLE emp_bak;

三、

PRIMARY KEY(主键约束):相当于UNIQUE + NOT NULL
1.约束的列值必须唯一，且不可以为NULL
2.既能定义列级别约束（单列PK约束），也可以定义表级别的约束（多列组合PK约束）
3.建议指定约束名称
4.创建唯一约束时，默认给所有约束的列添加索引index,用于优化查询效率
5.一个表只能有一个primary key主键约束
6.可以给一列或多列定义primary key主键约束

1.创建表emp_bak,指定empno为主键
CREATE TABLE emp_bak(
       empno NUMBER(4) CONSTRAINT emp_bak_empno_pk PRIMARY KEY,--empno设为主键(列级别约束)
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--相当于：
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empno_pk PRIMARY KEY(empno)--empno设为主键(表级别约束)
);
--测试
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES('', 'Lynn');--插入数据失败，主键不能为空
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Tom');--插入数据失败，主键要唯一
--删除表
DROP TABLE emp_bak;

--同时给多列定义组合主键约束
1.创建表emp_bak,指定empno、ename组合为主键
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empno_pk PRIMARY KEY(empno, ename)--组合约束，只能定义表级别约束
);
--测试
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');
INSERT INTO emp_bak(empno, ename) VALUES(1001, 'Lynn');--插入数据成功
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Tom');--插入数据成功
INSERT INTO emp_bak(empno, ename) VALUES(1000, 'Lynn');--插入数据失败，主键要唯一
--删除表
DROP TABLE emp_bak;

四、

FOREIGN KEY(外键约束):
1.外键列的值必须在引用列值的范围内，或者为NULL
2.外键参照的是列必须是主键或者唯一键
3.主键表主键值被外键表参照时，主键表记录不允许被删除

4.建议指定约束名称
5.既能定义列级别约束，也可以定义表级别的约束
6.一个表可以有多个外键约束

级联删除 ON DELETE CASCADE
级联清空 ON DELETE SET NULL

1.创建表emp_bak,指定deptno为外键，参考dept表的deptno主键列
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2) CONSTRAINT emp_bak_deptno_fk REFERENCES dept(deptno)
);
--相当于
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno)
);
--测试
INSERT INTO emp_bak(deptno, ename) VALUES(10, 'Lynn');
INSERT INTO emp_bak(deptno, ename) VALUES(NULL, 'Lynn');--插入数据成功
INSERT INTO emp_bak(deptno, ename) VALUES(50, 'Lynn');--违反完整约束条件(SOCTT.EMP_BAK_DEPTNO_FK)未找到父项关键字，因为dept表中dept没有50
DELETE FROM dept WHERE deptno = 10;--删除数据失败，违反完整约束条件(SCOTT,FK_DEPTNO)-已找到子记录
--删除表
DROP TABLE emp_bak;

--级联删除
1.创建表emp_bak,指定deptno为外键，参考dept表的deptno主键列；当dept表删除一个deptno时，emp_bak表对应的记录也一并删除
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2) CONSTRAINT emp_bak_deptno_fk REFERENCES dept(deptno) ON DELETE CASCADE--列级别约束的级联删除
);
--相当于
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE--标记别约束的级联删除
);
--测试
SELECT * FROM dept;
SELECT * FROM emp_bak;
INSERT INTO dept VALUES(88, 'STUDENT', 'ZHANJIANG');
INSERT INTO emp_bak(deptno, ename) VALUES(88, 'Lynn');
INSERT INTO emp_bak(deptno, ename) VALUES(88, 'Tom');
INSERT INTO emp_bak(deptno, ename) VALUES(40, 'Amy'); 
DELETE FROM dept WHERE deptno = 88;--此时emp_bak只剩deptno为40的一条数据
--删除表
DROP TABLE emp_bak;

--级联清空
1.创建表emp_bak,指定deptno为外键，参考dept表的deptno主键列；当dept表删除一个deptno时，emp_bak表对应记录的deptno设为空
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2) CONSTRAINT emp_bak_deptno_fk REFERENCES dept(deptno) ON DELETE SET NULL--列级别约束的级联清空
);
--相当于
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE SET NULL--标记别约束的级联清空
);
--测试
SELECT * FROM dept;
SELECT * FROM emp_bak;
INSERT INTO dept VALUES(88, 'STUDENT', 'ZHANJIANG');
INSERT INTO emp_bak(deptno, ename) VALUES(88, 'Lynn');
INSERT INTO emp_bak(deptno, ename) VALUES(88, 'Tom');
INSERT INTO emp_bak(deptno, ename) VALUES(40, 'Amy'); 
DELETE FROM dept WHERE deptno = 88;--此时emp_bak中，原本deptno为88的数据，改为deptno为空
--删除表
DROP TABLE emp_bak;

五、

CHECK(检查性约束):
1.约束的列取值范围,但是可以为NULL
2.建议指定约束名称
3.既能定义列级别约束，也可以定义表级别的约束
4.一个表可以有多个CHECK键约束

1.创建表emp_bak,约束sal必须大于等于3000
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2) CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--相当于
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000)
);
--测试
INSERT INTO emp_bak(empno, sal) VALUES(1001, NULL);--插入数据成功
INSERT INTO emp_bak(empno, sal) VALUES(1001, 2900);--插入数据失败，违反检查约束条件(SCOTT.EMP_BAK_SAL_CHECK)
INSERT INTO emp_bak(empno, sal) VALUES(1001, 3000);--插入数据成功
--删除表
DROP TABLE emp_bak;


--综合定义五类约束
--列级别约束
CREATE TABLE emp_bak(
       empno NUMBER(4) CONSTRAINT emp_bak_empno_pk PRIMARY KEY,--主键约束
       ename VARCHAR2(10) CONSTRAINT emp_bak_ename_unique UNIQUE,--唯一约束
       job VARCHAR2(9) CONSTRAINT emp_bak_job_nn NOT NULL,--非空约束
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2) CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000),--检查性约束
       comm NUMBER(7, 2),
       deptno NUMBER(2) CONSTRAINT emp_bak_deptno_fk REFERENCES dept(deptno)--外键约束
);
--表级别约束
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9) CONSTRAINT emp_bak_job_nn NOT NULL,--非空约束
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2),
       CONSTRAINT emp_bak_empno_pk PRIMARY KEY(empno),--主键约束
       CONSTRAINT emp_bak_ename_unique UNIQUE(ename),--唯一约束
       CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000),--检查性约束
       CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno)--外键约束
);
--删除表
DROP TABLE emp_bak;

六、

追加约束
语法：
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型 (列名);

删除约束

语法：
ALTER TABLE 表名 DROP PRIMARY KEY | UNIQUE(列名) | CONSTRAINT 约束名 [CASCADE];

禁用约束
语法：
ALTER TABLE 表名 DISABLE CONSTRAINT 约束名 [CASCADE];

启用约束
语法：
ALTER TABLE 表名 ENABLE CONSTRAINT 约束名;

1.为emp_bak追加约束后，再禁用和启动约束，最后删除这些约束（empno为主键约束，job为非空约束,ename为唯一约束，sal为检查性约束并且sal要大于等于3000），deptno为唯一约束关联表dept中的deptno
CREATE TABLE emp_bak(
       empno NUMBER(4),
       ename VARCHAR2(10),
       job VARCHAR2(9),
       mgr NUMBER(4),
       hiredate DATE,
       sal NUMBER(7, 2),
       comm NUMBER(7, 2),
       deptno NUMBER(2)
);
--追加约束
ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_empno_pk PRIMARY KEY(empno);--主键约束
ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_ename_unique UNIQUE(ename);--唯一约束
ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_sal_check CHECK(sal >= 3000);--检查性约束
ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno);--外键约束

ALTER TABLE emp_bak ADD CONSTRAINT emp_bak_job_notnull NOT NULL(job);--追加失败
ALTER TABLE emp_bak MODIFY job NOT NULL;--非空约束
ALTER TABLE emp_bak MODIFY (job CONSTRAINT emp_bak_job_not_null NOT NULL);--同上，追加非空约束

--禁用约束
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_empno_pk;--主键约束
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_ename_unique;--唯一约束
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_sal_check;--检查性约束
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_deptno_fk;--外键约束
ALTER TABLE emp_bak DISABLE CONSTRAINT emp_bak_job_not_null;--非空约束

--启用约束
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_empno_pk;--主键约束
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_ename_unique;--唯一约束
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_sal_check;--检查性约束
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_deptno_fk;--外键约束
ALTER TABLE emp_bak ENABLE CONSTRAINT emp_bak_job_not_null;--非空约束

--删除约束
ALTER TABLE emp_bak DROP CONSTRAINT emp_bak_empno_pk;--主键约束
ALTER TABLE emp_bak DROP CONSTRAINT emp_bak_ename_unique;--唯一约束
ALTER TABLE emp_bak DROP CONSTRAINT emp_bak_sal_check;--检查性约束
ALTER TABLE emp_bak DROP CONSTRAINT emp_bak_deptno_fk;--外键约束 
ALTER TABLE emp_bak MODIFY (job NULL);--非空约束

七、

数据字典： P(PRIMARY KEY) R(FOREIGN KEY) C(CHECK, NOT NULL属于C) U(UNIQUE)

SELECT * FROM USER_CONSTRAINTS;--查看当前用户下所有的约束
SELECT * FROM USER_CONS_COLUMNS;--查看约束关联的列信息

posted @ 2021-11-11 19:08 `青红造了个白` 阅读(114) 评论(0) 收藏举报

刷新页面返回顶部

`青红造了个白`

课堂笔记

Oracle笔记15——Oracle约束

约束

一、

二、

三、

四、

五、

六、

七、

公告