数据库约束概念

来源：互联网发布：软件开发的前景编辑：程序博客网时间：2024/05/20 21:56

这个主要就是增加约束的

以下几种约束、并一一列举：

1.主键约束：
要对一个列加主键约束的话，这列就必须要满足的条件就是分空
因为主键约束：就是对一个列进行了约束，约束为（非空、不重复）
以下是代码要对一个列加主键，列名为id,表名为emp

格式为：
alter table 表格名称 add constraint 约束名称增加的约束类型（列名）

例子：
alter table emp add constraint ppp primary key (id)

————————————————————————————————

2.check约束：
就是给一列的数据进行了限制
比方说，年龄列的数据都要大于20的
表名（emp）列名（age）

格式：
alter table 表名称 add constraint 约束名称增加的约束类型（列名）

例子：
alter table emp add constraint xxx check(age>20)

______________________________________________________________________

3.unique约束：
这样的约束就是给列的数据追加的不重复的约束类型

格式：
alter table 表名 add constraint 约束名称约束类型（列名）
比方说可以给ename列加个unique，让ename列的数据不重复
例子：
alter table emp add constraint qwe unique(ename)

————————————————————————————————

4.默认约束：
意思很简单就是让此列的数据默认为一定的数据

格式：
alter table 表名称 add constraint 约束名称约束类型默认值） for 列名

比方说：emp表中的gongzi列默认为10000

alter table emp add constraint jfsd default 10000 for gongzi

————————————————————————————————

5.外键约束：
这个有点难理解了，外键其实就是引用
因为主键实现了实体的完整性，
外键实现了引用的完整性，
应用完整性规定，所引用的数据必须存在！

其实就是个引用，
比方说一个表名称叫dept 里面有2列数据一列是ID一列是ENAME
id：表示产品的编号
ename：表示产品的名称

另外一个表格名称是emp 里面有2列数据，一列是ID 一列是DID
id:表示用户号
did：表示购买的产品号

要让emp表中的did列去引用dept表中的id

可以用下面的方法

格式：
alter table 表名 add constraint 约束名称约束类型 (列名) references 被引用的表名称（列名）

例子：
alter table emp add constraint jfkdsj foreign key (did) references dept (id)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--=============================

--SQL基础--> 约束(CONSTRAINT)

--=============================

一、几类数据完整性

实体完整性：表中记录不重复(任何两条记录不全等)并且每条记录都有一个非空主键

域完整性：表中字段值必须与字段数据类型、格式、有效范围相吻合

参照完整性：不能引用不存在的值

自定义完整性：根据特定业务领域定义的需求完整性

保证数据完整性的几种常用方法

约束(最常用)

过程

函数

触发器

实体完整性：primary key、unique、索引(index)

域完整性：check、foreign key、not null、数据类型

参照完整性：foreign key

自定义完整性：根据业务选用相应的约束类型

二、约束

约束是表、列级的强制规定、是防止那些无效或有问题的数据输入到表中。当对该表进行DML

操作时，如果操作违反约束条件或规则，ORACLE就会拒绝执行，并给出提示。

约束放置在表中，以下五种约束:

NOT NULL 非空约束C 指定的列不允许为空值

UNIQUE 唯一约束U 指定的列中没有重复值，或该表中每一个值或者每一组值都将是唯一的

PRIMARY KEY 主键约束P 唯一的标识出表的每一行,且不允许空值值,一个表只能有一个主键约束

FOREIGN KEY 外键约束R 一个表中的列引用了其它表中的列，使得存在依赖关系，可以指向引用自身的列

CHECK 条件约束C 指定该列是否满足某个条件

约束命名规则

如果不指定约束名Oracle server 自动按照SYS_Cn 的格式指定约束名，也可手动指定，

推荐的约束命名是：约束类型_表名_列名。

NN：NOT NULL 非空约束，比如nn_emp_sal

UK：UNIQUE KEY 唯一约束

PK：PRIMARY KEY 主键约束

FK：FOREIGN KEY 外键约束

CK：CHECK 条件约束

何时创建约束:

建表的同时

建表之后

可以在表级或列级定义约束

列级约束：只能引用一个列并且它属于列定义的一部分，可定义成任意类型的完整性约束。

表级约束：可引用一个或多个列，并且它属于表定义的一部分，可定义除NOT NULL外的其它约束。

可以通过数据字典视图查看约束

user_constraints

dba_constraints

all_constraints

user_cons_columns 列级上的约束

dba_cons_columns 列级上的约束

建表时约束定义的基本格式：

字段定义constraint 约束名约否类型（字段名）－－>unique,primary key,check

字段定义constraint 约否名foreingn key (字段名）references 表名（字段名）--->foreign

三、建表时约束定义

1.定义各种不同的约束

--创建一个用于作外键的表tb_dept

SQL> CREATE TABLE tb_dept

2 (

3 deptno NUMBER(4) PRIMARY KEY,

4 deptname VARCHAR2(20),

5 loc VARCHAR(50)

6 );

--建表时创建约束，没有指定约束名，则系统将自动命名约束名

SQL> CREATE TABLE tb_constraint_1

2 (

3 empno NUMBER PRIMARY KEY, --主键约束

4 ename VARCHAR2(20) NOT NULL, --非空约束

5 email VARCHAR2(60) UNIQUE, --唯一约束

6 sal NUMBER(5) CHECK(sal>1500), --核查约束

7 deptno NUMBER(4) REFERENCES tb_dept(deptno) --外键约束

8 );

--建表时指定了约束名

SQL> CREATE TABLE tb_constraint_2

2 (

3 empno NUMBER CONSTRAINT pk_tb_cons2_empno PRIMARY KEY,

4 ename VARCHAR2(20) CONSTRAINT nn_tb_cons2_empno NOT NULL,

5 email VARCHAR2(60) CONSTRAINT un_tb_cons2_email UNIQUE,

6 sal NUMBER(5) CONSTRAINT ck_tb_cons2_sal CHECK(sal>1500),

7 deptno NUMBER(4) CONSTRAINT fk_tb_cons2_dept REFERENCES tb_dept(deptno)

8 );

2.查看约束的定义信息

--查看表的约束

SQL> SELECT owner,constraint_name,constraint_type,table_name,status,deferrable,validated

2 FROM user_constraints

3 ORDER BY table_name;

OWNER CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED

------------- ------------------- - -------------------- -------- -------------- ---------

ROBINSON SYS_C005543 C TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON SYS_C005545 P TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON SYS_C005546 U TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON SYS_C005544 C TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON SYS_C005547 R TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON UN_TB_CONS2_EMAIL U TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON PK_TB_CONS2_EMPNO P TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON NN_TB_CONS2_EMPNO C TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON CK_TB_CONS2_SAL C TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON FK_TB_CONS2_DEPT R TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON SYS_C005542 P TB_DEPT ENABLED NOT DEFERRABLE VALIDATED

--查看列的约束

SQL> SELECT * FROM user_cons_columns;

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION

--------------- ---------------------- ---------------------- --------------- ----------

ROBINSON FK_TB_CONS2_DEPT TB_CONSTRAINT_2 DEPTNO 1

ROBINSON CK_TB_CONS2_SAL TB_CONSTRAINT_2 SAL

ROBINSON UN_TB_CONS2_EMAIL TB_CONSTRAINT_2 EMAIL 1

ROBINSON NN_TB_CONS2_EMPNO TB_CONSTRAINT_2 ENAME

ROBINSON PK_TB_CONS2_EMPNO TB_CONSTRAINT_2 EMPNO 1

ROBINSON SYS_C005542 TB_DEPT DEPTNO 1

ROBINSON SYS_C005547 TB_CONSTRAINT_1 DEPTNO 1

ROBINSON SYS_C005544 TB_CONSTRAINT_1 SAL

ROBINSON SYS_C005546 TB_CONSTRAINT_1 EMAIL 1

ROBINSON SYS_C005543 TB_CONSTRAINT_1 ENAME

ROBINSON SYS_C005545 TB_CONSTRAINT_1 EMPNO 1

3.定义符合主键约束

--定义复合主键

SQL> CREATE TABLE tb_constraint_3

2 (

3 empno NUMBER,

4 ename VARCHAR2(20),

5 email VARCHAR2(20) UNIQUE,

6 CONSTRAINT pk_tb_cons3_empno_ename PRIMARY KEY(empno,ename)

7 );

--查询TB_CONSTRAINT_3的约束信息

SQL> SELECT owner,constraint_name,constraint_type,table_name,status,deferrable,validated

2 FROM user_constraints

3 WHERE table_name = 'TB_CONSTRAINT_3';

OWNER CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED

------------ ------------------------- - ----------------- -------- -------------- ---------

ROBINSON PK_TB_CONS3_EMPNO_ENAME P TB_CONSTRAINT_3 ENABLED NOT DEFERRABLE VALIDATED

ROBINSON SYS_C005554 U TB_CONSTRAINT_3 ENABLED NOT DEFERRABLE VALIDATED

--两列上具有相同的约束名

SQL> SELECT * FROM user_cons_columns WHERE table_name = 'TB_CONSTRAINT_3';

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION

------------------ ---------------------- ------------------- --------------- ----------

ROBINSON SYS_C005554 TB_CONSTRAINT_3 EMAIL 1

ROBINSON PK_TB_CONS3_EMPNO_ENAME TB_CONSTRAINT_3 ENAME 2

ROBINSON PK_TB_CONS3_EMPNO_ENAME TB_CONSTRAINT_3 EMPNO 1

4.几种不同约束的冲突提示

--创建一个序列用于产生主键

SQL> CREATE SEQUENCE cons_sequence

2 INCREMENT BY 1

3 START WITH 100

4 MAXVALUE 200

5 NOCACHE

6 NOCYCLE;

--为表tb_dept插入记录

SQL> INSERT INTO tb_dept

2 SELECT 10,'Development','ShenZhen' FROM DUAL

3 UNION ALL

4 SELECT 20,'Customer','ShangHai' FROM DUAL;

2 rows created.

--为表tb_constraint_2插入记录

SQL> INSERT INTO tb_constraint_2

2 VALUES(cons_sequence.nextval,'Robinson','Robinson@hotmail.com',2000,10);

1 row created.

a.主键约束提示

--下面使用currval值，提示主键冲突，从PK_TB_CONS2_EMPNO即可得知是主键列冲突，这就是自定义约束名的好处

SQL> INSERT INTO tb_constraint_2

2 VALUES(cons_sequence.currval,'Jack','Jack@hotmail.com',2200,10);

INSERT INTO tb_constraint_2

ERROR at line 1:

ORA-00001: unique constraint (ROBINSON.PK_TB_CONS2_EMPNO) violated

b.非空约束提示

--注意在Oracle中，空字符串('')被当成空值，下面的错误提示即是，什么原因不清楚

SQL> INSERT INTO tb_constraint_2

2 VALUES(cons_sequence.nextval,'','Jack@hotmail.com',2200,10);

VALUES(cons_sequence.nextval,'','Jack@hotmail.com',2200,10)

ERROR at line 2:

ORA-01400: cannot insert NULL into ("ROBINSON"."TB_CONSTRAINT_2"."ENAME")

--下面这条记录插入的才是ename为空值的插入语句

SQL> INSERT INTO tb_constraint_2

2 VALUES(cons_sequence.nextval,NULL,'Jack@hotmail.com',2200,10);

VALUES(cons_sequence.nextval,NULL,'Jack@hotmail.com',2200,10)

ERROR at line 2:

ORA-01400: cannot insert NULL into ("ROBINSON"."TB_CONSTRAINT_2"."ENAME")

--------------------------------------------------------------------------------------------

--下面是在SQL server 2005中的演示，不存在上述出现的问题

--理论上空字符串('')并不等于NULL,不知道为什么在Oracle 10g中出现了错误提示

CREATE TABLE tb_constraint_1

(

empno int PRIMARY KEY, --主键约束

ename VARCHAR(20) NOT NULL, --非空约束

email VARCHAR(60) UNIQUE, --唯一约束

sal int CHECK(sal>1500) --核查约束

-- deptno NUMBER(4) REFERENCES tb_dept(deptno) --外键约束

);

INSERT INTO tb_constraint_1

SELECT 15,'Andy','Andy@hotmail.com',1800;

INSERT INTO tb_constraint_1 --ename为''的记录插入成功

SELECT 16,'','John@hotmail.com',1800;

SELECT * FROM tb_constraint_1 WHERE ename IS NOT NULL;

empno ename email sal

----------- -------------------- ----------------------------- ---------

15 Andy Andy@hotmail.com 1800

16 John@hotmail.com 1800

(2 row(s) affected)

---------------------------------------------------------------------------------------------

c.唯一约束提示

--ORACLE在唯一键列上自动生成一个唯一索引以实现唯一性

--提示email字段唯一性冲突

SQL> INSERT INTO tb_constraint_2

2 VALUES(cons_sequence.nextval,'Jack','Robinson@hotmail.com',2400,20);

INSERT INTO tb_constraint_2

ERROR at line 1:

ORA-00001: unique constraint (ROBINSON.UN_TB_CONS2_EMAIL) violated

d.CHECK约束提示

--提示check约束sal字段冲突

SQL> INSERT INTO tb_constraint_2

2 VALUES(cons_sequence.nextval,'Henry','Henry@hotmail.com',1350,40);

INSERT INTO tb_constraint_2

ERROR at line 1:

ORA-02290: check constraint (ROBINSON.CK_TB_CONS2_SAL) violated

e.外键约束提示

--提示不符合外键约束

SQL> INSERT INTO tb_constraint_2

2 VALUES(cons_sequence.nextval,'Henry','Henry@hotmail.com',1550,40);

INSERT INTO tb_constraint_2

ERROR at line 1:

ORA-02291: integrity constraint (ROBINSON.FK_TB_CONS2_DEPT) violated - parent

key not found

5.补充

CHECK 约束

定义每一行必须满足的条件

以下的表达式是不允许的:

–出现CURRVAL, NEXTVAL, LEVEL, 和ROWNUM 伪列

–使用SYSDATE, UID, USER, 和USERENV 函数

–在查询中涉及到其它列的值

FOREIGN KEY 约束

外键约束是用来维护从表和主表的引用完整性的，所以外键约束要涉及两个表。

FOREIGN KEY: 在表级指定子表中的列

REFERENCES: 标示在父表中的列

ON DELETE CASCADE: 当父表中的列被删除时，子表中相对应的列也被删除

ON DELETE SET NULL: 子表中相应的列置空

如果子表在建外键时，该列的数据并不在父表，则无法创建该约束。

--外键约束对delete语句的影响：

SQL> DELETE FROM tb_constraint_2;

2 rows deleted.

SQL> ROLLBACK;

Rollback complete.

--子表tb_constraint_2中有记录存在，故不能删除父表中的相关记录

SQL> DELETE FROM tb_dept;

DELETE FROM tb_dept

ERROR at line 1:

ORA-02292: integrity constraint (ROBINSON.FK_TB_CONS2_DEPT) violated - child record found

外键约束对insert语句的影响：

插入数据的外键字段值必须在主表中存在，只有从表才有可能违反约束，主表不会。

外键约束对delete语句的影响：

删除主表数据时，如果从表有对该数据的引用，要先将从表中的数据处理好。主表才有可能违反约束。

外键约束对update语句的影响：

主从表都有可能违反外键约束，操作一个表必须将另一个表的数据处理好。

外键约束对DDL语句的影响：

删除主表时，才有可能违约约束。

ON DELETE SET NULL 和　ON DELETE CASCADE对外键约束的影响

ON DELETE SET NULL 子句的作用是，当主表中的一行数据被删除时，ORACLE自动将从表中依赖于

它的记录外键值改为空。

--为从表删除约束后新增带on delete set null的外键约束

SQL> ALTER TABLE tb_constraint_2

2 DROP CONSTRAINT FK_TB_CONS2_DEPT ;

SQL> ALTER TABLE tb_constraint_2

2 ADD CONSTRAINT fk_tb_cons2_deptno

3 FOREIGN KEY(deptno) REFERENCES tb_dept(deptno)

4 ON DELETE SET NULL;

SQL> SELECT * FROM tb_constraint_2;

EMPNO ENAME EMAIL SAL DEPTNO

---------- -------------------- --------------------------------------- ---------- ----------

113 Robinson Robinson@hotmail.com 2000 10

114 Mark Mark@hotmail.com 3000 20

--删除主表中deptno为的记录

SQL> DELETE FROM tb_dept WHERE deptno = 10 ;

1 row deleted.

--从表中deptno为的被置为NULL

SQL> SELECT * FROM tb_constraint_2;

EMPNO ENAME EMAIL SAL DEPTNO

---------- -------------------- ------------------------------------------ ---------- ----------

113 Robinson Robinson@hotmail.com 2000

114 Mark Mark@hotmail.com 3000 20

ON DELETE CASCADE 子句的作用是，当主表中的一行数据被删除时，ORACLE自动将从表中依赖于它的记录外键也删除。

--为从表删除约束后新增带on delete cascade的外键约束

SQL> ALTER TABLE tb_constraint_2

2 DROP CONSTRAINT fk_tb_cons2_deptno;

SQL> ALTER TABLE tb_constraint_2

2 ADD CONSTRAINT fk_tb_cons2_deptno

3 FOREIGN KEY(deptno) REFERENCES tb_dept(deptno)

4 ON DELETE CASCADE;

--主表中deptno 为的记录被删除

SQL> DELETE FROM tb_dept WHERE deptno = 20 ;

1 row deleted.

--从表中deptno 为的记录被删除

SQL> SELECT * FROM tb_constraint_2;

EMPNO ENAME EMAIL SAL DEPTNO

---------- -------------------- ------------------------------- ---------- ----------

113 Robinson Robinson@hotmail.com 2000

四、建表后的约束定义

使用ALTER TABLE 语句:

添加或删除约束, 但是不能修改约束

有效化或无效化约束

添加NOT NULL 约束要使用MODIFY 语句

SQL> DROP TABLE tb_constraint_2;

Table dropped.

SQL> CREATE TABLE tb_cons2

2 (

3 empno NUMBER,

4 ename VARCHAR2(20),

5 email VARCHAR2(60),

6 sal NUMBER(5),

7 deptno NUMBER(4)

8 );

1.添加主键约束

SQL> ALTER TABLE tb_cons2

2 ADD CONSTRAINT pk_tb_cons2_empno PRIMARY KEY(empno);

2.添加非空约束

--注意添加非空约束使用的是modify 而非add

SQL> ALTER TABLE tb_cons2

2 ADD CONSTRIANT nn_tb_cons2_ename NOT NULL(ename);

ADD CONSTRIANT nn_tb_cons2_ename NOT NULL

ERROR at line 2:

ORA-00902: invalid datatype

SQL> ALTER TABLE tb_cons2

2 ADD CONSTRIANT nn_tb_cons2_ename ename NOT NULL;

ADD CONSTRIANT nn_tb_cons2_ename ename NOT NULL

ERROR at line 2:

ORA-01735: invalid ALTER TABLE option

SQL> ALTER TABLE tb_cons2

2 MODIFY (ename CONSTRAINT nn_tb_cons2_ename NOT NULL);

3.添加唯一约束

SQL> ALTER TABLE tb_cons2

2 ADD CONSTRAINT uk_tb_cons2_email UNIQUE(email);

4.添加CHECK约束

SQL> ALTER TABLE tb_cons2

2 ADD CONSTRAINT ck_tb_cons2_sal CHECK(sal>1500);

5.添加外键约束

SQL> ALTER TABLE tb_cons2

2 ADD CONSTRAINT fk_tb_cons2_tb_dept_deptno

3 FOREIGN KEY(deptno) REFERENCES tb_dept(deptno)

4 ON DELETE CASCADE;

6.禁用约束

默认情况下创建的约束是启用的

--添加一个新列comm以及一个check约束并将其置为禁用模式

SQL> ALTER TABLE tb_cons2

2 ADD comm NUMBER(4) CONSTRAINT ck_cons2_comm CHECK(comm>0) DISABLE;

SQL> SELECT owner,constraint_name,constraint_type,table_name,status,deferrable,validated

2 FROM user_constraints

3 WHERE table_name = 'TB_DEPT';

OWNER CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED

--------------- ------------------------- - ------------------- -------- -------------- ----------

ROBINSON SYS_C005542 P TB_DEPT ENABLED NOT DEFERRABLE VALIDATED

--下面禁用tb_dept表的主键约束,提示存在依赖性,不能成功禁用该约束

SQL> ALTER TABLE tb_dept

2 DISABLE CONSTRAINT SYS_C005542 ;

ALTER TABLE tb_dept

ERROR at line 1:

ORA-02297: cannot disable constraint (ROBINSON.SYS_C005542) - dependencies exist

--通过增加CASCADE来实现级联禁用约束

SQL> ALTER TABLE tb_dept

2 DISABLE CONSTRAINT SYS_C005542 CASCADE;

--下面的查询可以看到基于tb_dept表存在外键约束的tb_cons2 ,tb_constraint_1上的外键列约束都被禁用

SQL> SELECT constraint_name,constraint_type,table_name,status,deferrable,validated

2 FROM user_constraints

3 WHERE validated ='NOT VALIDATED';

CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED

------------------------------ - ---------------------- -------- -------------- -------------

CK_CONS2_COMM C TB_CONS2 DISABLED NOT DEFERRABLE NOT VALIDATED

FK_TB_CONS2_TB_DEPT_DEPTNO R TB_CONS2 DISABLED NOT DEFERRABLE NOT VALIDATED

SYS_C005542 P TB_DEPT DISABLED NOT DEFERRABLE NOT VALIDATED

SYS_C005547 R TB_CONSTRAINT_1 DISABLED NOT DEFERRABLE NOT VALIDATED

UK_TB_CONS2_EMAIL U TB_CONS2 DISABLED NOT DEFERRABLE NOT VALIDATED

7.启用约束

ENABLE子句可将当前无效的约束启用

当定义或启用UNIQUE或PRIMARY KEY约束时系统会自动创建UNIQUE或PRIMARY KEY索引

--启用约束时不支持CASCADE，对被级联禁用的约束应根据需要逐个启用

SQL> ALTER TABLE tb_dept

2 ENABLE CONSTRAINT SYS_C005542 CASCADE;

ENABLE CONSTRAINT SYS_C005542 CASCADE

ERROR at line 2:

ORA-00933: SQL command not properly ended

SQL> ALTER TABLE tb_dept

2 ENABLE CONSTRAINT SYS_C005542 ;

--可以使用ENABLE NOVALIDATE，实现只对新数据应用某个约束

--约束默认的是ENABLE VALIDATE，即对所有的行实现约束检查

SQL> ALTER TABLE tb_cons2

2 ENABLE NOVALIDATE CONSTRAINT ck_cons2_comm;

8.删除约束：

SQL> ALTER TABLE tb_cons2

2 DROP CONSTRAINT uk_tb_cons2_email;

使用下面的方法可以级联删除主表主键及从表的外键

ALTER TABLE table_name DROP PRIMARY KEY CASCADE

--使用drop primary key cascade删除主表主键及从表外键

SQL> ALTER TABLE tb_dept

2 DROP PRIMARY KEY CASCADE;

--删除后可以看到不存在tb_dept主键约束及tb_cons2外键的记录

SQL> SELECT constraint_name,constraint_type,table_name,status,deferrable,validated

2 FROM user_constraints

3 ORDER BY table_name;

CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED

------------------------------ - ------------------- -------- -------------- -------------

CK_TB_CONS2_SAL C TB_CONS2 ENABLED NOT DEFERRABLE VALIDATED

NN_TB_CONS2_ENAME C TB_CONS2 ENABLED NOT DEFERRABLE VALIDATED

PK_TB_CONS2_EMPNO P TB_CONS2 ENABLED NOT DEFERRABLE VALIDATED

UK_TB_CONS2_EMAIL U TB_CONS2 DISABLED NOT DEFERRABLE NOT VALIDATED

SYS_C005546 U TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED

SYS_C005545 P TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED

SYS_C005543 C TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED

SYS_C005544 C TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED

PK_TB_CONS3_EMPNO_ENAME P TB_CONSTRAINT_3 ENABLED NOT DEFERRABLE VALIDATED

SYS_C005554 U TB_CONSTRAINT_3 ENABLED NOT DEFERRABLE VALIDATED

9.级联约束

CASCADE CONSTRAINTS子句在DROP COLUMN子句中使用

该子句会删除涉及到在已删除列上定义的主键或唯一关键字的所有引用完整性约束

该子句也将删除在已删除列上定义的所有多列约束

SQL> CREATE TABLE tb_cons3

2 (

3 empno NUMBER PRIMARY KEY,

4 sal NUMBER ,

5 comm NUMBER,

6 mgr NUMBER,

7 CONSTRAINT fk_tb_cons3 FOREIGN KEY(mgr) REFERENCES tb_cons3(empno),

8 CONSTRAINT ck_tb_cons3_sal CHECK(empno > 0 AND sal > 0),

9 CONSTRAINT ck_tb_cons3_comm CHECK(comm > 0)

10 );

Table created.

--下面提示主键列不能删除

SQL> ALTER TABLE tb_cons3 DROP COLUMN empno;

ALTER TABLE tb_cons3 DROP COLUMN empno

ERROR at line 1:

ORA-12992: cannot drop parent key column

--下面提示sal被多列约束，也不能删除

SQL> ALTER TABLE tb_cons3 DROP COLUMN sal;

ALTER TABLE tb_cons3 DROP COLUMN sal

ERROR at line 1:

ORA-12991: column is referenced in a multi-column constraint

--使用带有CASCADE CONSTRAINTS的DROP COLUMN 该表中的pk,fk,及ck_sal都将被删除

SQL> ALTER TABLE tb_cons3 DROP COLUMN empno CASCADE CONSTRAINTS;

Table altered.

10.延迟约束

指仅当事物被提交时强制执行约束

在添加约束时可以使用DEFERRABLE子句来指定约束为延迟约束

对于已经存在的约束不能修改为DEFERRABLE延迟约束，只能删除后重建时指定DEFERRABLE子句

使用DEFERRABLE子句时可以使用INITIALY IMMEDIATE或INITIALY DEFERRED

INITIALY IMMEDIATE：缺省的行为，即实时实施约束行为

INITIALY DEFERRED：延迟约束行为到提交时予以检查

--创建tb_cust表

SQL> CREATE TABLE tb_cust

2 (

3 custid NUMBER(4) NOT NULL,

4 custname VARCHAR2(20)

5 );

Table created.

--为表添加主键约束并启用延迟约束

SQL> ALTER TABLE tb_cust

2 ADD CONSTRAINT pk_tb_cust_custid PRIMARY KEY(custid)

3 DEFERRABLE INITIALLY DEFERRED;

Table altered.

--插入条记录后提交，给出违反了约束并出现回滚

SQL> INSERT INTO tb_cust SELECT 10,'Jay' FROM DUAL;

1 row created.

SQL> INSERT INTO tb_cust SELECT 10,'SAM' FROM DUAL;

1 row created.

SQL> COMMIT;

COMMIT

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (ROBINSON.PK_TB_CUST_CUSTID) violated

--将约束置为实时启用

SQL> SET CONSTRAINT pk_tb_cust_custid IMMEDIATE;

Constraint set.

--插入两条新纪录，未执行commit前时约束已起作用

SQL> INSERT INTO tb_cust SELECT 10,'Robinson' FROM DUAL;

1 row created.

SQL> INSERT INTO tb_cust SELECT 10,'Jack' FROM DUAL;

INSERT INTO tb_cust SELECT 10,'Jack' FROM DUAL

ERROR at line 1:

ORA-00001: unique constraint (ROBINSON.PK_TB_CUST_CUSTID) violated

SQL> COMMIT;

Commit complete.

--查看最后插入的记录

SQL> SELECT * FROM tb_cust;

CUSTID CUSTNAME

---------- --------------------

10 Robinson

----------------------------------------------------------------------------------------------------------------------------------------------------------

在SQL Server中有5种约束：
主键约束（primary key constraint）
唯一性约束（unique constraint）
检查约束（check constraint）
缺省约束（default constraint）
外部键约束（foreign key constraint）

在SQL SERVER中，
（1）      对于基本表的约束分为列约束和表约束
约束是限制用户输入到表中的数据的值的范围，一般分为列级约束与表级约束。
         列级约束有六种：主键Primary key、外键foreign key 、唯一 unique、检查 checck 、默认default 、非空/空值 not null/ null
         表级约束有四种：主键、外键、唯一、检查

列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；
表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用’,’分隔，定义表约束时必须指出要约束的那些列的名称。完整性约束的基本语法格式为：
              [ CONSTRAINT <约束名> ] <约束类型>
约束名：约束不指定名称时，系统会给定一个名称。

（2）列级约束与表级约束的区别
如果完整性约束涉及到该表的多个属性列，必须定义在表级上，否则既可以定义在列级也可以定义在表级。
简而言之：
列级约束:列级约束是行定义的一部分，只能应用于一列上。
表级约束:表级约束是独立于列的定义，可以应用在一个表中的多列上。

（3）列级约束与表级约束在SQL中的用法（即如何在SQL中定义约束）
在创建表时定义约束：
           CREATE TABLE table_name
                 ({<column_definition> -------列级约束定义
                |column_name AS computed_column_expression -------计算列定义
                |<teble_constraint> ------表级约束定义
                   }[,….n]
                   )

一个约束定义为列级约束还是表级约束？？？
根据实际需要和设计者思路确定。如primary key，当只涉及到一列时，定义为列级约束；当涉及到多列时，则定义为表级约束。
Prinmary key定义为列级约束时，相应SQL语句：
Stu_id上建立的主键pk_1为列级约束

CREATE TABLE student
(Stu_id int constraint pk_1 primary key,
Stu_name varchar(8),
….)
当定义为表级约束时，相应SQL语句：
CREATE TABLE student
(Stu_id int NOT NULL,
Stu_id上建立的主键pk_1为的表级约束

Stu_name varchar(8),
Constraint pk_1 primary key (Stu_id),
….)

各约束具体说明：
1. 主键约束
PRIMARY KEY约束
PRIMARY KEY约束用于定义基本表的主键，起唯一标识作用，其值不能为NULL，也不能重复，以此来保证实体的完整性。
PRIMARY KEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别：
①在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束；
②对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。
注意：不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。
PRIMARY KEY既可用于列约束，也可用于表约束。
PRIMARY KEY用于定义列约束时，其语法格式如下：
              CONSTRAINT <约束名> PRIMARY KEY
PRIMARY KEY用于定义表约束时，即将某些列的组合定义为主键，其语法格式如下：
     [CONSTRAINT <约束名>]S PRIMARY KEY (<列名>[{<列名>}])

2. 唯一性(UNIQUE)约束
UNIQUE约束用于指明基本表在某一列或多个列的组合上的取值必须唯一。
定义了UNIQUE约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。
唯一键允许为空，但系统为保证其唯一性，最多只可以出现一个NULL值。
UNIQUE既可用于列约束，也可用于表约束。
UNIQUE用于定义列约束时，其语法格式如下：
              [CONSTRAINT <约束名>] UNIQUE
唯一性约束用于指定一个或者多个列的组合的值具有唯一性，以防止在列中输入重复的值。当使用唯一性约束时，需要考虑以下几个因素：
①使用唯一性约束的字段允许为空值。
②一个表中可以允许有多个唯一性约束。
③可以把唯一性约束定义在多个字段上。
④唯一性约束用于强制在指定字段上创建一个唯一性索引。
⑤缺省情况下，创建的索引类型为非聚簇索引。

unique约束是用来确保不受主键约束列上的数据的唯一性.
unique与primary key的区别在于:
    （1）unique约束主要用于非主键的一列或多列上要求数据唯一.
    （2） unique约束允许该列上存在NULL值,而主键决不允许出现.
    （3）可以在一个表创建多个unique约束,而在一个表上只能够设置一个主键

3. 检查约束
CHECK约束用来检查字段值所允许的范围，如，一个字段只能输入整数，而且限定在0-100的整数，以此来保证域的完整性。
CHECK既可用于列约束，也可用于表约束，
其语法格式为：
       [CONSTRAINT <约束名>] CHECK (<条件>)
一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关。
一个表中可以定义多个检查约束。
每个CREATE TABLE语句中每个字段只能定义一个检查约束。
在多个字段上定义检查约束，则必须将检查约束定义为表级约束。
当执行INSERT语句或者UPDATE语句时，检查约束将验证数据。
检查约束中不能包含子查询。

4. 缺省约束
使用缺省约束时，应该注意以下几点：
每个字段只能定义一个缺省约束。
如果定义的缺省值长于其对应字段的允许长度，那么输入到表中的缺省值将被截断。
不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上。
如果字段定义为用户定义的数据类型，而且有一个缺省绑定到这个数据类型上，则不允许该字段有缺省约束。

5. 外部键约束
外部键约束用于强制参照完整性，提供单个字段或者多个字段的参照完整性。 FOREIGN KEY约束指定某一个列或一组列作为外部键，其中，包含外部键的表称为从表(参照表)，包含外部键所引用的主键或唯一键的表称主表(被参照表)。
系统保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值，要么取空值。以此保证两个表之间的连接，确保了实体的参照完整性。
FOREIGN KEY既可用于列约束，也可用于表约束，
其语法格式为：
       [CONSTRAINT <约束名>] FOREIGN KEY                    REFERENCES <主表名> (<列名>[{<列名>}])
当使用外部键约束时，应该考虑以下几个因素：
①外部键约束提供了字段参照完整性。
②外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配。
③外部键约束不能自动创建索引，需要用户手动创建。
④用户想要修改外部键约束的数据，必须有对外部键约束所参考表的SELECT权限或者REFERENCES权限。
⑤参考同一表中的字段时，必须只使用REFERENCES子句，不能使用外部键子句。
⑥一个表中最多可以有31个外部键约束。
⑦在临时表中，不能使用外部键约束。
⑧主键和外部键的数据类型必须严格匹配。

6. NULL 约束
（1）NULL/NOT NULL
是否允许该字段的值为NULL。
NULL值不是0也不是空白，更不是填入字符串“NULL”，而是表示“不知道”、“不确定”或“没有数据”的意思。
当某一字段的值一定要输入才有意义的时候，则可以设置为NOT NULL。
如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用
只能用于定义列约束，
其语法格式如下：
              [CONSTRAINT <约束名> ][NULL|NOT NULL]