oracle约束
oracle约束
- 非空
- 默认
- 唯一
- 检查
- 自增(序列)
- 主键
- 外键
一,非空约束
- 被约束的列不能为空
1建表时在类型长度后面加not null,指明非空
SQL> create table con_test1
2 (
3 con_id number(6) not null,
4 con_name varchar(60),
5 con_age number(3)
6 );
表已创建。
SQL> insert into con_test1 values(null,'gent',null);
ORA-01400: 无法将 NULL 插入 ("SCOTT"."CON_TEST1"."CON_ID")
SQL> insert into con_test1 values(1,'gent',null);
已创建 1 行。
2更改列属性指定非空
删除原有数据
SQL> delete from con_test1 where con_id=1;
已删除 1 行。
更改列属性,指定非空
SQL> alter table con_test1 modify con_age not null;
表已更改。
SQL> insert into con_test1 values(1,'gent',null);
ORA-01400: 无法将 NULL 插入 ("SCOTT"."CON_TEST1"."CON_AGE")
3移除非空约束
SQL> alter table con_test1 modify con_age null;
表已更改。
SQL> insert into con_test1 values(1,'gent',null);
已创建 1 行。
二,默认约束
- 默认约束指定某列的默认值
如果男性同学较多,性别就可以默认为'男'
当插入一条新的记录时,没有为这个字段赋值,那么系统就会自动为这个字段赋值为'男'
1建表时设置
SQL> create table default_test1
2 (
3 dt_id number(6),
4 dt_name varchar(60),
5 dt_date date,
6 dt_sex varchar(10) default '男'
7 );
表已创建。
SQL> insert into default_test1 values(1,'gent',sysdate,'');
已创建 1 行。
SQL> insert into default_test1 values(2,'land',sysdate,null);
已创建 1 行。
SQL> insert into default_test1 values(3,'teng',sysdate,'女');
已创建 1 行。
SQL> insert into default_test1(dt_id,dt_name,dt_date) values(4,'rund',sysdate);
已创建 1 行。
SQL> select * from default_test1;
DT_ID DT_NAME DT_DATE DT_SEX
---------- -------------------- -------------- ------------
1 gent 27-11月-19
2 land 27-11月-19
3 teng 27-11月-19 女
4 rund 27-11月-19 男
2建表后设置
SQL> alter table default_test1 modify dt_date default sysdate;
表已更改。
SQL> insert into default_test1(dt_id,dt_name) values(5,'lauy');
已创建 1 行。
SQL> select * from default_test1
2 where dt_id=5;
DT_ID DT_NAME DT_DATE DT_SEX
---------- ----------------- -------------- ------------
5 lauy 27-11月-19 男
3删除设置的默认约束
SQL> alter table default_test1 modify dt_sex default null;
表已更改。
SQL> insert into default_test1(dt_id,dt_name) values(6,'auny');
已创建 1 行。
SQL> select * from default_test1
2 where dt_id=6;
DT_ID DT_NAME DT_DATE DT_SEX
---------- ------------------- -------------- -------------
6 auny 27-11月-19
三,唯一约束
- 被约束的列是唯一的
- 可以多行为空
1建表时设置
- 建表时在类型长度后加
constraint 约束名 unique; - 建表语句新加一行,
constraint 约束名 unique(列名);
SQL> create table con_test2
2 (
3 con_id number(6) constraint unique_contest2_id unique,
4 con_name varchar(60),
5 con_age number(3),
6 constraint unique_contest_name unique(con_name)
7 );
表已创建。
2建表后通过alter设置
语法
alter table 表名 add constraint 约束名 unique(约束列名);
SQL> alter table con_test2 add constraint unique_contest2_age unique(con_age);
表已更改。
移除唯一约束
SQL> alter table con_test2 drop constraints unique_contest2_age;
表已更改。
四,检查约束
- 规定每一列能够输入的值,从而确保数值的正确性
当姓名字段中规定只能输入男或者女,此时可以用到检查约束
1建表时创建
sex字段只能为男或者女
age字段值的范围为1-165,包含边界值
SQL> create table check_test1
2 (
3 id number(6) primary key,
4 name varchar(60) not null,
5 sex varchar(6) constraint check_sex check(sex='男' or sex='女'),
6 age number(3),
7 email varchar(200),
8 constraint check_age check(age between 1 and 165)
9 );
表已创建。
2建表后添加
设置email字段值中必须包含@符号
SQL> alter table check_test1 add constraint check_email check(email like '%@%');
表已更改。
3删除检查约束
SQL> alter table check_test1 drop constraints check_age;
表已更改。
五,自增(序列)约束
- 序列是一串有序的数字
- 在oracle12c新增自增功能,通过为表主键添加
generated by default as identity - 在oracle12c之前可以通过序列实现自增功能
SQL> create table seq_table
2 (
3 id number(6),
4 name varchar(60)
5 );
表已创建。
SQL> create sequence seq_test
2 start with 1
3 increment by 1
4 nomaxvalue
5 nocycle
6 cache 10;
序列已创建。
SQL> insert into seq_table(id,name) values(seq_test.nextval,'张三');
已创建 1 行。
SQL> insert into seq_table(id,name) values(seq_test.nextval,'李四');
已创建 1 行。
SQL> select * from seq_table;
ID NAME
---------- ------
1 张三
2 李四
| 关键字 | 描述 |
|---|---|
| create sequence | 序列名--序列的名字,以SEQ_开头 |
| start with 数值 | --起始值(可不写) |
| minvalue 数值 | --序列最小值 |
| maxvalue 数值 | --序列最大值(nomaxvalue不设置最大值) |
| increment by 数值 | --每次增长大小,也叫步长 |
| cache 数值 | --缓冲池大小,若表中插入数据较多,则cache可以设置为1000. ceil(最大值-最小值)/ABS(步长) |
| cycle; | --是否循环(即达到最大值后重新从最小值开始),不写默认为不循环. |
删除序列
SQL> drop sequence seq_test;
序列已删除。
六,主键
- 是不为空且不重复的一列数据
- 确定一条记录的唯一性
1单列主键
1.1创建表时设置
列级别
不能自定义表名称
SQL> create table pktest1
2 (
3 id1 number(8) primary key,
4 name1 varchar(60)
5 );
表已创建。
表级别
SQL> create table pktest2
2 (
3 id2 number(8),
4 name2 varchar(60),
5 constraint PK_pktest2_id2 primary key(id2)
6 );
表已创建。
PK_pktest2_id2为主键名称
1.2更改属性时设置
SQL> create table pktest3
2 (
3 id3 number(8),
4 name3 varchar(60)
5 );
表已创建。
SQL> alter table pktest3 add constraint PK_pktest3_id3 primary key(id3);
表已更改。
1.3先设置表索引,再设置主键,能够优化查询速度
SQL> create table pktest4
2 (
3 id4 number(8),
4 name4 varchar(60)
5 );
表已创建。
SQL> create unique index PK_pktest4_id4 on pktest4(id4);
索引已创建。
SQL> alter table pktest4 add constraint PK_pktest4_id4 primary key(id4);
表已更改。
索引名称可以和主键名称相同
复合主键
- 如果表中没有单列能够确定数据的唯一性,可以指定多列来确定数据的唯一性
2.1创建表时设置
SQL> create table pktest5
2 (
3 id5 number(8),
4 name5 varchar(60),
5 age number(3),
6 constraint PK_pktest5_idname5 primary key(id5,name5)
7 );
表已创建。
2.2更改属性时设置
SQL> create table pktest6
2 (
3 id6 number(8),
4 name6 varchar(60),
5 age number(3)
6 );
表已创建。
SQL> alter table pktest6 add constraint PK_pktest6_idname6 primary key(id6,name6);
表已更改。
七,外键
- 子表的数据依赖于父表数据,所依赖的父表列必须是主键或者有唯一约束
- 外键以FK_开头,名字唯一
- 没有写on delete cascade和on delete set null时,只有外键没有相关联的数据时,才能删除主表
- 有on delete cascade(级联删除)删除父表数据,子表数据也会跟随删除;
- 有on delete set null时,删除父表数据,子表数据约束列会被置空;
SQL> create table FKtest1
2 (
3 id1 number(6),
4 name1 varchar(60)
5 );
表已创建。
SQL> create table FKtest2
2 (
3 id2 number(6),
4 name2 varchar(60)
5 );
表已创建。
SQL> alter table FKtest1 add constraint PK_FKtest1_id1 primary key(id1);
表已更改。
SQL> insert into FKtest1 values(1,'张三');
已创建 1 行。
SQL> insert into FKtest1 values(2,'李四');
已创建 1 行。
SQL> insert into FKtest1 values(3,'王五');
已创建 1 行。
SQL> insert into FKtest2(id2,name2) select id1,name1 from FKtest1;
已创建3行。
1简单写法
没有on delete casecade或者set null时,子表数据必须在父表中(约束列),删除时须先删除子表,再删除父表;
语法
alter table 表名 add constraint FK_外键名 foreign key(列名) references 主表表名(列名);
范例
SQL> alter table FKtest2 add constraint FK_FKtest2_id2 foreign key(id2) references FKtest1(id1);
表已更改。
发现不能往test2表中插入主表列id没有的数据
SQL> insert into FKtest2 values(4,'赵六');
ORA-02291: 违反完整约束条件 (SCOTT.FK_FKTEST2_ID2) - 未找到父项关键字
SQL> insert into FKtest1 values(4,'赵六');
已创建 1 行。
SQL> insert into FKtest2 values(4,'赵六');
已创建 1 行。
删除时需要先删除字表数据,再删除父表数据
SQL> delete from FKtest1 where id1=4;
ORA-02292: 违反完整约束条件 (SCOTT.FK_FKTEST2_ID2) - 已找到子记录
SQL> delete from FKtest2 where id2=4;
已删除 1 行。
SQL> delete from FKtest1 where id1=4;
已删除 1 行。
2级连删除
有on delete cascade(级联删除)删除父表数据,子表数据也会跟随删除;
删除上面范例中的外键约束
SQL> alter table FKtest2 drop constraints FK_FKtest2_id2;
表已更改。
新建级联约束
SQL> alter table FKtest2 add constraint FK_FKtest2_id2 foreign key(id2) references FKtest1(id1) on delete cascade;
表已更改。
发现同样不能往test2表中插入主表列id没有的数据
SQL> insert into FKtest2 values(4,'赵六');
ORA-02291: 违反完整约束条件 (SCOTT.FK_FKTEST2_ID2) - 未找到父项关键字
SQL> insert into FKtest1 values(4,'赵六');
已创建 1 行。
SQL> insert into FKtest2 values(4,'赵六');
已创建 1 行。
删除主表的数据时,test2表中的数据同时被也被删除了
SQL> delete from FKtest1 where id1=4;
已删除 1 行。
SQL> delete from FKtest2 where id2=4;
已删除0行。
3删除制空
删除上面创建的级联约束
SQL> alter table FKtest2 drop constraints FK_FKtest2_id2;
表已更改。
新建制空外键约束
SQL> alter table FKtest2 add constraint FK_FKtest2_id2 foreign key(id2) references FKtest1(id1) on delete set null;
表已更改。
同样不能不能往test2表中插入主表列id没有的数据
SQL> insert into FKtest2 values(4,'赵六');
ORA-02291: 违反完整约束条件 (SCOTT.FK_FKTEST2_ID2) - 未找到父项关键字
SQL> insert into FKtest1 values(4,'赵六');
已创建 1 行。
SQL> insert into FKtest2 values(4,'赵六');
已创建 1 行。
删除主表数据时,test2表会删除对应外键列的值
SQL> delete from FKtest1 where id1=1;
已删除 1 行。
SQL> select * from FKtest2;
ID2 NAME2
--------------
张三
2 李四
3 王五
