不是oracle约束的是,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 王五