Oracle 约束

1.约束的作用：定义规则、确保数据的完整性

2.非空约束
(1)在创建表时设置非空约束
CREATE TABLE table_name(column_name datatype NOT NULL,…);
SQL:create table userinfo_1
(id number(6,0),
username varchar2(20) not null,
userpwd varchar2(20) not null);
(2)在修改表时添加非空约束
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
SQL:alter table userinfo modify username varchar2(20) not null;
(3)在修改表时去除非空约束
ALTER TABLE table_name MODIFY column_name datatype NULL;
SQL:alter table userinfo modify username varchar2(20) null;

3.主键约束
作用：确保表当中每一行数据的唯一性
设置了主键约束的字段首要它是非空的，它的值是唯一的；一张表只能设计一个主键约束，主键约束可以由多个字段构成（联合主键或复合主键）。
(1)在创建表时设置主键约束
CREATE TABLE table_name(column_name datatype PRIMARY KEY,…);
SQL:create table userinfo_p
(id number(6,0) primary key,
username varchar2(20),
userpwd varchar2(20));
CONSTRAINT constraint_name PRIMARY KEY(column_name1,…);(构成联合主键)
SQL:cteate table userinfo_p1
(id number(6,0),
username varchar2(20),
userpwd varchar2(20),
constraint pk_id_username primary key(id,username));
查找约束名
SQL:select constraint_name from user_constraint where table_name=‘USERINFO_P1’;
(2)在修改表时添加主键约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column_name1,…);
SQL:alter table userinfo add constraint pk_id peimary key(id);
(3)更改约束的名称
ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name;
SQL:alter table userinfo rename constraint pk_id to new_pk_id;
(4)删除主键约束
禁用/启用当前的约束
ALTER TABLE table_name DISABLE|ENABLE CONSTRAINT constraint_name;
SQL:alter table userinfo disable constraint new_pk_id;
查看约束状态信息
select constraint_name,status from user_constraints where table_name=‘USERINFO’;
删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
SQL:alter table userinfo drop constraint new_pk_id;
ALTER TABLE table_name DROP PRIMARY KEY[CASADE]
SQL:alter table userinfo_p drop primary key;

4.外键约束
(1)在创建表时设置外键约束
CREATE CREATE table1(column_name datatype REFERENCES table2(column_name),…);
table1:从表 table2:主表
设置外键约束时，主表的字段必须是主键;
主从表中相应的字段必须是同一个类型数据;
从表中外键字段的值必须来自主表中的相应字段的值，或者为null值。
SQL:create table typeinfo
(typeid varchar2(10) primary key,
typename varchar2(20));
SQL:create table userinfo_f
(id varchar2(10) primary key,
username varchar2(20),
typeid_new varchar2(10) references typeinfo(typeid));
SQL:insert into typeinfo values(1,1);
SQL:insert into userinfo_f(id,typeid_new) values(1,1);
CONSTRAINT constraint_name FOREING KEY(column_name) REFERENCES table_name(column_name) [ON DELETE CASCADE];
SQL:create table userinfo_f2
(id varchar2(10) primary key,
username varchar2(20),
typeid_new varchar2(10),
constraint fk_typeid_new foreing key(typeid_new) references typeinfo(typeid);
SQL:create table userinfo_f3
(id varchar2(10) primary key,
username varchar2(20),
typeid_new varchar2(10),
constraint fk_typeid_new1 foreing key(typeid_new) references typeinfo(typeid) on delete cascade;
ON DELETE CASCADE:级联删除
ON UPDATE CASCADE:级联更新
(2)在修改表时添加外键约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREING KEY(column_name) REFERENCES table_name(column_name) [on DELETE CASCADE];
SQL:create table userinfo_f4
(id varchar2(10) peimary key,
username varchar2(20),
typeid_new varchar2(10));
SQL:alter table userinfo_f4 add constraint pk_typeid_alter foreing key(typeid_new) references typeinfo(typeid);
(3)删除外键约束
禁用|启用外键约束
DISABLE|ENABLE CONSTRAINT constraint_name;
SQL:select constraint_name,constraint_type,status from user_constraints where table_name=‘USERINFO_F4’;
SQL:alter table userinfo_f4 disable constraint FK_TYPEID_ALTER;
彻底删除外键约束
DROP CONSTRAINT constraint_name;
SQL:alter table userinfo_f4 drop constraint FK_TYPEID_ALTER;

5.唯一约束
作用:保证字段值的唯一性
唯一约束和主键约束的区别:
1)主键字段值必须是非空的,唯一约束允许有一个空值;
2)主键在每张表中只能有一个,唯一约束在每张表中可以有多个;
(1)在创建表时设置唯一约束
CREATE TABLE table_name(column_name datatype UNIQUE,…);
SQL:create table userinfo_u
(id varchar2(10) peimary key,
username varchar2(20) unique,
userpwd varchar2(20));
CONSTRAINT constraint_name UNIQUE(column_name)
SQL:create table userinfo_u1
(id varchar2(10) peimary key,
username varchar2(20) unique,
userpwd varchar2(20),
constraint un_username unique(username));
(2)在修改表时添加唯一约束
ADD CONSTRAINT constraint_name UNIQUE(column_name);
SQL:create table userinfo_u2
(id varchar2(10) peimary key,
username varchar2(20) unique,
userpwd varchar2(20));
SQL:alter table userinf0_u2 add constraint un_username_new unique(username);
(3)删除唯一约束
禁用|启用唯一约束
DISABLE|ENABLE CONSTRAINT constraint_name;
彻底删除唯一约束
DROP CONSTRAINT constraint_name;

6.检查约束
(1)在创建表时设置检查约束
CREATE TABLE table_name(column_name datatype CHECK(expressions),…);
SQL:create table userinfo_c
(id varchar2(10) primary key,
username varchar2(20),
salary number(5,0) check(salary>0));
SQL:insert into userinfo_c values(1,‘aaa’,-20);
CONSTRAINT constraint_name CHECK(expressions);
SQL:create table userinfo_c1
(id varchar2(10) primary key,
username varchar2(20),
salary number(5,0),
constraint ck_salary check(salary>0));
(2)在修改表时添加检查约束
ADD CONSTRAINT constraint_name CHECK(expressions);
SQL:create table userinfo_c2
(id varchar2(10) primary key,
username varchar2(20),
salary number(5,0));
SQL:alter table userinfo_c3 add constraint ck_salary_new check(salary>0);
(3)删除检查约束
禁用|启用检查约束
DISABLE|ENABLE CONSTRAINT constraint_name;
彻底删除检查约束
DROP CONSTRAINT constraint_name;

数据字典(user_constraints)