约束条件

数据必须遵循的规则或限制.

primary key 主键 PK

foreign key 外键 FK

not null 非空 NN

unique 唯一 UK

check  检查 CK

--建表时,建立主键约束条件

--id列受限,

create table student_ning(

id number(4) primary key,

name char(10)

);

--1.ok

insert into student_ning

values(1, 'peter');

--2.ok

insert into student_ning

values(2, 'tom');

--3.错 ORA-00001: 唯一约束被违反,主键重复

--constraint: 约束

insert into student_ning

values(2, 'jerry');

--4.错, 主键列不允许有空值

insert into student_ning(name)

values('zhangsan');

某些数据库: 主键自增长

mysql  / sql server

oracle : 主键发生器: 序列

drop table student_ning;

--建表时,设置name列为Not Null约束

create table student_ning(

id number primary key,

name char(10) not null);

--1.ok,插入记录

insert into student_ning values(1,'peter');

--2.错,name不能插入null值

insert into student_ning(id) values(2);

--insert into student_ning values(2, null);

drop table student_ning;

--建表时,给email列创建唯一约束

create table student_ning(

id number(4) primary key,

name char(10) not null,

email char(20) unique

);

--1.ok

insert into student_ning

values(1, 'zhangsan','zs@163.com');

--2.错,email相同, 违反唯一约束, ORA-00001

insert into student_ning

values(2, 'zhangsanf', 'zs@163.com')

drop table student_ning;

--gender:性别, 只允许:'M', 'F'

create table student_ning(

id number(4) primary key,

name char(10) not null,

email char(20) unique,

gender char(1) check (gender in ('F','M'))

);

--1.ok

insert into student_ning

values(1,'zhangsan', 'zs@163.com','M');

--2.

insert into student_ning

values(2,'zhangsanf', 'zsf@163.com','X');

--查询约束条件

 select constraint_name,

        constraint_type

 from user_constraints

 where table_name = 'STUDENT_NING';

--命名规则:表名_列名_约束条件类型

--列级约束

create table student_ning1(

id number(4)

  constraint stu_n_id_pk primary key,

name char(10)

  constraint stu_n_name_nn not null,

email char(20)

  constraint stu_n_email_uk unique,

gender char(1)

  constraint stu_n_gender_ck

    check (gender in ('F','M'))

);

--表级约束

--非空,只能定义在列级.其他约束可以定义在表级create table student_ning2(

id number(4),

name char(10)

    constraint stu_n2_name_nn not null,

email char(20),

gender char(1),

constraint stu_n2_id_pk primary key (id),

constraint stu_n2_email_uk unique (email),

constraint stu_n2_gender_ck

    check (gender in ('F','M'))

);

--建表,除了非空以外的约束,全部放在建表后再建

create table student_ning3(

id number(4),

name char(10) not null,

email char(20),

gender char(1)

);

alter table student_ning3 add

constraint stu_n3_id_pk primary key (id);

alter table student_ning3 add

constraint stu_n3_email_uk unique (email);

alter table student_ning3 add

constraint stu_n3_gender_ck

    check (gender in ('F','M'));

select constraint_name, constraint_type

from user_constraints

where table_name = 'STUDENT_NING3';

--专业表

create table major_ning(

id number(2) primary key,

name char(20) not null);

insert into major_ning values(1, 'java');

insert into major_ning values(2, 'oracle');

insert into major_ning values(3, 'c++');

insert into major_ning values(4, 'android');

commit;

select * from major_ning;

create table student_ning4(

id number(4),

name char(10) not null,

mid number(2));

alter table student_ning4 add

  constraint stu_n4_id_pk primary key (id);

alter table student_ning4 add

  constraint stu_n4_mid_fk foreign key (mid)

  references major_ning(id);

insert into student_ning4

values(1, 'peter', 1);

insert into student_ning4

values(2, 'tom', 3);

--错:parent key not found, 父表中没有9这个id

insert into student_ning4

values(3, 'jerry', 9);

--错:parent key not found

update student_ning4

set mid = 8 where id = 1;

--错:child record found

--major_ning表的子表,有参照id=3的记录

delete major_ning where id = 3;

主键 PK = Not Null + Unique

外键 FK : 表间的一对多关系

非空 Not Null

唯一 Unique

检查 Check

create table student(

last_name char(20),

first_name char(20),

score number,

age number(2)

);

alter table student add

  constraint stu_ln_fn_pk primary key  (last_name, first_name);

alter table student add

  constraint stu_age_ck check (age > 17);

last_name    first_name

张            三

张            三丰

smith         john

smith         tom

李            三

--把约束条件重建,加on delete set null短语

alter table student_ning4 drop

  constraint stu_n4_mid_fk;

alter table student_ning4 add

  constraint stu_n4_mid_fk foreign key (mid)

  references major_ning(id)

  on delete set null;

--删除主表(major_ning)id=2的记录,成功,把子表中(student_ning)所有专业2的学生,mid列设置为null

delete major_ning where id = 2;

 

--把约束条件重建,加on delete cascade短语

--cascade: 级联,株连

alter table student_ning4 drop

  constraint stu_n4_mid_fk;

alter table student_ning4 add

  constraint stu_n4_mid_fk foreign key (mid)

  references major_ning(id)

  on delete cascade;

--当删除主表(major_ning)的记录时,子表中所有参照该条记录的记录全部被级联删除.

delete major_ning where id = 2

--不复制约束条件.只复制表结构和数据.

--复制结构.

create table student_ning5

as

select * from student_ning4

where 1 = 0;

--给新表student_ning5增加约束条件.

insert into student_ning5

(select * from student_ning4

where mid = 2);

建立约束条件的几种语法:

1.建表时, 列级

--约束条件由系统命名

create table student(

id number primary key,

name char(10) not null

);

--约束条件自定义名字

create table student(

id number constraint stu_id_pk primary key,

name char(10) constriant stu_name_nn not null

);

2.建表时, 表级

create table student(

id number,

name char(10) not null,

constraint stu_id_pk primary key (id)

);

3.建表后,表级

create table student(

id number,

name char(10) not null

);

alter table student add

  constraint stu_id_pk primary key(id);

--脚本文件:

-------begin--------

--1.删除所有的外键约束

alter table student_ning drop

  constraint stu_mid_fk;

--2.删除所有的表

drop table student_ning;

drop table major_ning;

--3.建表及建立约束.

create table major_ning.....

insert into major_ning values(....);

create table student_ning(....);

alter table student_ning add constraint...

insert into student_ning values(....);

commit;

-------end-------------

【本文纠错】【告诉好友】【打印此文】【返回顶部】