华南俳烁实业有限公司

約束條件

數(shù)據(jù)必須遵循的規(guī)則或限制.

primary key 主鍵 PK

foreign key 外鍵 FK

not null 非空 NN

unique 唯一 UK

check  檢查 CK

--建表時(shí),建立主鍵約束條件

--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.錯(cuò) ORA-00001: 唯一約束被違反,主鍵重復(fù)

--constraint: 約束

insert into student_ning

values(2, 'jerry');

--4.錯(cuò), 主鍵列不允許有空值

insert into student_ning(name)

values('zhangsan');

某些數(shù)據(jù)庫: 主鍵自增長

mysql  / sql server

oracle : 主鍵發(fā)生器: 序列

drop table student_ning;

--建表時(shí),設(shè)置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.錯(cuò),name不能插入null值

insert into student_ning(id) values(2);

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

drop table student_ning;

--建表時(shí),給email列創(chuàng)建唯一約束

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.錯(cuò),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';

--命名規(guī)則:表名_列名_約束條件類型

--列級(jí)約束

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'))

);

--表級(jí)約束

--非空,只能定義在列級(jí).其他約束可以定義在表級(jí)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';

--專業(yè)表

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);

--錯(cuò):parent key not found, 父表中沒有9這個(gè)id

insert into student_ning4

values(3, 'jerry', 9);

--錯(cuò):parent key not found

update student_ning4

set mid = 8 where id = 1;

--錯(cuò):child record found

--major_ning表的子表,有參照id=3的記錄

delete major_ning where id = 3;

主鍵 PK = Not Null + Unique

外鍵 FK : 表間的一對多關(guān)系

非空 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)所有專業(yè)2的學(xué)生,mid列設(shè)置為null

delete major_ning where id = 2;

--把約束條件重建,加on delete cascade短語

--cascade: 級(jí)聯(lián),株連

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;

--當(dāng)刪除主表(major_ning)的記錄時(shí),子表中所有參照該條記錄的記錄全部被級(jí)聯(lián)刪除.

delete major_ning where id = 2

--不復(fù)制約束條件.只復(fù)制表結(jié)構(gòu)和數(shù)據(jù).

--復(fù)制結(jié)構(gòu).

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.建表時(shí), 列級(jí)

--約束條件由系統(tǒng)命名

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.建表時(shí), 表級(jí)

create table student(

id number,

name char(10) not null,

constraint stu_id_pk primary key (id)

);

3.建表后,表級(jí)

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-------------