约束Constraints--主键约束、外键约束、唯一约束、检查约束、默认约束、NOT NULL约束、列约束与表约束、创建约束、删除约束

约束 Including Constraints

　　以下内容转自：https://www.cnblogs.com/wcl2017/p/7043939.html和http://blog.csdn.net/shaderdx/article/details/77184924

在数据库中使用约束(constraints)是为了在该数据库中实施所谓的"业务规则"其实就是防止非法信息进入数据库,满足管理员和应用开发人员所定义的规则集.

ORACLE使用完整性约束(integrity constraints)防止不合法的数据写入数据库,管理员和开发人员可以定义完整性规则,增强商业规则,限制数据表中的数据.如果一个DML语句执行的任何结果破坏了完整性约束,ORACLE就会回滚语句,返回错误信息.

　约束是通过使用CREATE TABLE或ALTER TABLE语句生成的.(建立表时或者表建立后修改都可)

　如果相关的约束定义在单列上,可以在列这一级指定约束的定义;多列约束必须定义在数据表级,相关的列要在括号中指定,用逗号分隔。

　如果没有为约束提供一个名字,那么ORACLE会分配一个系统生成的唯一名字,以SYS_开头,你可以使用关键字CONSTRAINTS后面跟随相关的约束名字来为约束指定名字。

ORACLE支持五种类型的完整性约束：

1.—-主键约束（Primay Key Coustraint）唯一性，非空性；一个表只能有一个主键约束

2.—-唯一约束（Unique Counstraint）唯一性，可以空，但只能有一个

3.—-检查约束 (Check Counstraint) 对该列数据的范围、格式的限制（如：年龄、性别等）

4.—-默认约束 (Default Counstraint) 该数据的默认值

5.—-外键约束 (Foreign Key Counstraint) 需要建立两表间的关系并引用主表的列

1.主键约束：

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

alter table emp add constraint ppp primary key (id)

在创建表时添加约束

create table LITTLE_EVENT_STATE_HISTORY
(
   ID                   CHAR(32)             not null,
   LITTLER_EVENT_ID     VARCHAR(32)          not null,
   OPERATOR_ID          VARCHAR(32),
   OPERATOR_NAME        VARCHAR2(32),
   UPDATE_TIME          DATE                 not null,
   COMPLETE_DESCR       VARCHAR2(300),
   COMPLETE_STATUS      NUMBER(2)            not null,
   DEL_STATUS           NUMBER(2)            not null,
   constraint PK_LITTLE_EVENT_STATE_HISTORY primary key (ID)
);

唯一性约束指定一个或多个列的组合的值具有唯一性，以防止在列中输入重复的值。唯一性约束指定的列可以有NULL属性。由于主关键字值是具有唯一性的，因此主关键字列不能再设定唯一性约束。

2. 唯一约束

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

比方说可以给ename列加个unique，让ename列的数据不重复

例子：

alter table emp add constraint qwe unique(ename)

创建表时定义创建约束：

CREATE TABLE employees
                  (employee_id NUMBER(6),
                      last_name     VARCHAR2(25) NOT NULL, 
                      salary          NUMBER(8,2),
                      commission_pct NUMBER(2,2),
                      hire_date DATE NOT NULL,
                      CONSTRAINT emp_email_uk UNIQUE(email));

3.检查约束 check

就是给一列的数据进行了限制

比方说，年龄列的数据都要大于20的

表名（emp）列名（age）

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

例子：

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

在创建表时在列级别上添加检查约束的实例如下：

CREATE TABLE employees
                  (...,
                      salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary>0),
                      ...);

格式为：[CONSTRAINT <constraint name>] CHECK (<condition>)

这里的CHECK子句中的CONDITION应该求值为一个布尔值结果，并且可以引用相同行的其他列的值，不能包含子查询，序列，环境函数（SYSDATE,UID,USER,USERENV）和伪列（ROWNUM,LEVEL,CURRVAL,NEXTVAL），一个列上可以定义多个CHECK约束，如果所定义的条件为FALSE，则语句将回滚。

CREATE TABLE employees
                  (...,
                      salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary>0),
                      ...);

4.默认约束

让此列的数据默认为一定的数据。

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

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

alter table emp add constraint jfsd default 10000 for gongzi

5.外键约束

在父级表和子表中，相关的列的数据类型必须匹配，外部键列和引用键列可以位于相同的表中（自引用完整性约束）

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

例子：一个表名称叫dept 里面有2列数据一列是ID一列是ENAME

id：表示产品的编号

ename：表示产品的名称

另外一个表格名称是emp 里面有2列数据，一列是ID 一列是DID

id:表示用户号

did：表示购买的产品号

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

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

创建表时创建外键约束：

CREATE TABLE employees
                  (employee_id NUMBER(6),
                      last_name     VARCHAR2(25) NOT NULL, 
                      salary          NUMBER(8,2),
                      commission_pct NUMBER(2,2),
                      hire_date DATE NOT NULL,
                      deparment_id NUMBER(4),
                      CONSTRAINT emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id),
                      CONSTRAINT emp_email_uk UNIQUE(email));

上例是在表级定义外部键约束，如果在列级定义，参考例子如下：

CREATE TABLE employees
                  (...,
                      department_id NUMBER(4) CONSTRAINT emp_deptid_fk REFERENCES departments(department_id),
                  ...);
                   //没有关键字FOREIGN KEY

格式为：列名列类型 constraint 约束名 references 所引用的表名称（所引用的列名称）

外键约束的其他细节：

外关键字约束定义了表之间的关系，当一个表中的一个列或多个列的组合和其他表中的主关键字定义相同时，就可以将这些列或列组合定义为外关键字，并设定它适合哪个表中哪些列相关联。这样，当在定义主关键字约束的表中更新列值时其他表中有与之相关联的外关键字约束的表中的外关键字列也将被相应的做相同的更新。外关键字约束的作用还体现在，当向含有外关键字的表插入数据时，如果与之相关联的表的列中无与插入的外关键字列值相同的值时，系统会拒绝插入数据。与主关键字相同，不能使用一个定义为TEXT 或IMAGE 数据类型的列创建外关键字。外关键字最多由16个列组成。

CONSTRAINT constraint_name

FOREIGN KEY （column_name1[, column_name2,…,column_name16]）

REFERENCES ref_table [ （ref_column1[,ref_column2,…, ref_column16] ）]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ] ]

[ NOT FOR REPLICATION ]

ref_table：指定要建立关联的表的名称。

ref_column：指定要建立关联的表中的相关列的名称。

ON DELETE {CASCADE | NO ACTION}：

指定在删除表中数据时，对关联表所做的相关操作。在子表中有数据行与附表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在删除父表（被引

用表）数据行时会将子表中对应的数据行删除；如果指定的是NO ACTION，则数据库会产生一个错误，将父表中的删除操作回滚。NO ACTION是缺省值。

ON UPDATE {CASCADE | NO ACTION}

指定在更新表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在更新父表数据行时会将子表中对应的数据行更新；如果指定的是NO ACTION，则SQL Server 会产生一个错误，并将父表中的更新操作回滚。NO ACTION 是缺省值。

NOT FOR REPLICATION

指定列的外关键字约束在把从其它表中复制的数据插入到表中时不发生作用。

NOT NULL约束

NOT NULL约束只能定义在列级，不能定义在表级。

需要在创建表时添加此约束：

CREATE TABLE employees
                  (employee_id NUMBER(6),
                      last_name     VARCHAR2(25) NOT NULL, //没有指定名字,将用系统SYS_命名
                      salary          NUMBER(8,2),
                      commission_pct NUMBER(2,2),
                      hire_date    DATE CONSTRAINT emp_hire_date_nn NOT NULL);   //用户定义了名字

可以看到例子当中第一个NOT NULL约束没有指定名字，将用系统SYS_命名；第二个NOT NULL约束用户自定义了名字。

删除约束

ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;

ALTER TABLE departments DROP PRIMARY KEY CASCADE; //由于departments被employees.department_id引用了

对于NOT NULL约束，用ALTER TABLE MODIFY子句来删除：

ALTER TABLE employees MODIFY last_name NULL;

关闭约束

ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE;   //如果没有被引用则不需CASCADE关键字

当你生成一个约束时,约束自动打开(除非你指定了DISABLE子句0,当用DISABLE关闭UNIQUE或者PRIMARY KEY约束时,ORACLE会自动删除相关的唯一

索引,再次打开时,ORACLE又会自动建立的.

打开约束

ALTER TABLE employees 
ENABLE CONSTRAINT emp_emp_id_pk; //注意,打开一个先前关闭的被引用的主键约束,并不能自动打开相关的外部键约束

不太能区分什么时候该用删除约束，什么时候该用关闭约束

列约束和表约束

对于数据库来说，约束又分为列约束和表约束，列约束作为列定义的一部分只能作用于此列本身，表约束作为表定义的一部分，可以作用于多个列。例子如下：

create table products (
p_id char(8) ,
p_name char(10) ,
price money default 0.01 ,
quantity smallint check (quantity>=10) , /* 列约束 */
constraint pk_p_id primary key (p_id, p_name) /* 表约束 */

posted @ 2018-03-05 17:20 Garcia11 阅读(1114) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Garcia11

约束Constraints--主键约束、外键约束、唯一约束、检查约束、默认约束、NOT NULL约束、列约束与表约束、创建约束、删除约束

公告