SQL查询-约束「建议收藏」 - 全栈程序员必看

大家好，又见面了，我是你们的朋友全栈君。

在慕课网上学习时记的关于oracle中约束概念的基础笔记，当初一直对约束的概念不清不楚的，所以找了些资料来看

约束是数据库用来确保数据满足业务规则的手段

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

1.非空约束

非空约束作用的列也叫强制列。顾名思义，强制键列中必须有值

创建表时设置非空约束

create table table_name (
column_name datatype not null,
...
)

如下

修改表时添加非空约束

alter table table_name modify column_name datatype not null;
注意：在设置非空约束之前，表中不应该有数据

修改表时去除非空约束

alter table table_name modify column_name datattype null;

2.主键约束：

确保表中每一行数据的唯一性(设置了主键约束的字段，要求这个字段是非空的+唯一的)

所以如果一个字段被设置为主键，则查看表结构时，会发现这个字段为非空，即 NOT NULL；

一张表只能设计一个主键约束，但是主键约束可以由多个字段构成(联合主键或复合主键)

在创建表时设置主键约束

方式一：
create table table_name(
column_name datatype PRIMARY KEY,...
);

如下

方式二：（创建联合主键）
constraint constraint_name primary key(column_name1,...)

如下

如果忘记一个表的约束名称了，可以从user_constraints表中查询所有表的约束，如下

在修改表时添加主键约束

alter table table_name add constraint constraint_name primary key(column_name1,...);

设置主键之前，表中这个字段最好没有数据，如果有数据则值必须是唯一的，且不能有空值

如下

更改约束名称

alter table table_name rename constraint old_name to new_name

删除主键约束

alter table table_name disable|enable constraint constraint_name; （禁用或启用约束）
alter table table_name drop constraint constraint_name; （通过主键名删除约束）
alter table table_name drop primary key[cascade]; （因为一个表只有一个主键，所以也可以直接这样删除主键；可选参数cascade用于级联删除，比如有外键的情况）

3.外键约束：

1）在Oracle数据库中，外键是用来实现参照完整性的方法之中的一个。打个形象的比喻，外键是指定义外键的表的列的值必须在另一个表中出现。

2）被參照的表称之为父表（parent table)，创建外键的表称之为子表（child table)。子表中的外键关联了父表中的主键。

3）外键能够在创建表时定义或者通过ALTER TABLE语句创建。

在创建表时设置外键约束

方式一(列集)

create table table1(从表)
(
column_name datatype references table2(主表)(column_name),...
);
设置外键约束时，主表的字段必须是主键；主从表中相应的字段必须是同一个数据类型；从表中外键字段的值必须来自主表中相应字段的值，或者为null
例子
先创建一个主表
create table typeinfo
(
typeid varchar2(10) primary key, 设置主键
typename varchar2(20)
);
接下来创建一个从表
create table userinfo
(
id varchar2(10) primary key, 设置主键
username varchar2(20),
typeid_new varchar2(10) references typeinfo(typeid)
（将 typeid_new 设为外键，引用的是 typeinfo 表的主键）
);

方式二(表集)

constraint contraint_name(约束名) foreign key(column_name) references table_name(column_name) [on delete cascade]
[]中的东西表示级联删除，添加以后，则删除主表的字段值，从表的外键也会删除
create table userinfo
(
id varchar2(10) primary key, 设置主键
username varchar2(20),
typeid_new varchar2(10),
constraint fk_typeid_new foreign key(typeid_new) references typeinfo(typeid)
);
 
在修改表时添加外键约束
create table userinfo
(
id varchar2(10) primary key, 设置主键
username varchar2(20),
typeid_new varchar2(10)
);
alter table userinfo
add constraint fk_typeid_alter foreign key(typeid_new)references typeinfo(typeid);

删除外键约束

(1)禁用外键约束

disable|enable constraint constraint_name （放到alter table 后面使用）
通过user_constraints表查看有哪些约束
select table_name,constraint_name,constraint_type,status from user_constraints where table_name='APPR_ITM_DEFINE_EX'

constraint_type的含义

禁用约束

alter table userinfo disable constraint fk_typeid_alter;

(2)删除外键约束

drop constraint constraint_name;（放到alter table 后面使用）

如下：
alter table userinfo drop constraint fk_typeid_alter;

4.唯一约束

作用：保证字段值的唯一性

与主键约束的区别：

1.主键约束的主键字段必须是非空的，而唯一约束允许有一个空值

2.主键在每一张表中只能有一个，而唯一约束在每张表中可以有多个

在创建表时设置唯一约束

方式一(列集)
create table table_name(column_name datatype unique,...)
 
create table userinfo
(
id varchar2(10) primary key, 设置主键
username varchar2(20) unique, 设置唯一约束
typeid_new varchar2(10)
);
 
方式二（表集）
constraint constraint_nameunique(column_name)
 
create table userinfo
(
id varchar2(10) primary key, 设置主键
username varchar2(20) ,
typeid_new varchar2(10)，
constraint un_username unique(username) 设置唯一约束
);
如果要设置多个唯一约束，需要继续添加constraint constraint_nameunique(column_name)才行，不能写在一个constraint中

在修改表时添加唯一约束

create table userinfo
(
id varchar2(10) primary key, 设置主键
username varchar2(20) ,
typeid_new varchar2(10)
);
alter table userinfo add constraint un_username unique(username);

删除唯一约束

1.禁用唯一约束
disable|enable constraint constraint_name （放到alter table 后面使用）
 
禁用约束
alter table userinfo disable constraint un_username;
 
2.删除唯一约束
drop constraint constraint_name;（放到alter table 后面使用）
如下：
alter table userinfo drop constraint un_username;