《Oracle编程自学与面试指南》08：约束

于 2020-08-17 10:51:38 发布
阅读量397 收藏
点赞数
原文链接：https://www.szcbjs.com/
版权

订阅博主
课程封面-Oracle Database-AT阿宝哥

内容导航：

前言
1、定义
2、非空约束
3、默认约束
4、检查约束
5、唯一约束
6、主键约束
7、外键约束
8、启用/禁用约束
1、定义
表约束是数据库系统能够实施业务规则以保证数据遵循实体-关系模型的一种手段，目的是为了防止"无效数据"被插入表中。其中，实体-关系模型由定义应用程序数据结构的系统分析所确定。

在针对定义了约束的表执行任何DML时，如果DML违反了约束，则将自动回滚整个语句。注意，如果一个DML语句影响到了多个行，那么，在特定行遇到约束问题前，此语句可能已经局部成功。如果此语句是多语句事务的一部分，那么，事务中已经成功的语句将保持完好，但不提交。如果违反约束，将自动回滚出现问题的整个语句，而不是语句中的单个操作，也不是整个事务。

约束具有名称，最好使用标准命名约定指定名称，如果未显式指定名称，Oracle将为其生成名称。

Oracle数据库支持的约束类型如下:

类型 作用 命名
NOT NULL
DEFAULT
UNIQUE 唯一性 UK_Name
PRIMARY KEY 确保行唯一 PK_TableName
FOREIGN KEY 降低冗余 FK_FatherTableName_ChildTableName
CHECK 格式和精度 CK_Name
场景案例:

唯一：比如微信的多张号，包括QQ，MT，Mail，第三方帐号等
非空(必填)：比如注册时的用户名，密码
主键：比如微信注册ID
外键：比如朋友关系中的所有者编号和朋友编号。
检查：比如年龄必须在0~150之间，手机号码的长度及格式
默认：比如签名中的"这家伙很懒…"，或者给数值类型列赋0值，字符串列赋空字符
目标:
在本课中,您将学到如何使用"完整性"约束实现业务规则.

2、非空约束

/*
主题：
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

–Sample4:非空约束

–Step1:创建表.
DROP TABLE person ;

CREATE TABLE person
(
pid VARCHAR2(18) ,
NAME VARCHAR2(200) NOT NULL , --非空约束只能定义在列级.
age NUMBER(3) NOT NULL , --非空约束只能定义在列级.
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’ ,
CONSTRAINT person_pid_pk PRIMARY KEY(pid)
) ;

–Step2:进行非空约束测试.
INSERT INTO person(pid,NAME,age,birthday,sex) VALUES
(‘11111111111111111’,NULL,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;–失败。提示“无法将 NULL 插入”

INSERT INTO person(pid,NAME,birthday,sex) VALUES
(‘22222222222222222’,‘张三’,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;–失败。提示“无法将 NULL 插入”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
3、默认约束

/*
主题：
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

–Sample20:默认值和备注

–Step1:创建表
DROP TABLE person ;

CREATE TABLE person
(
personid VARCHAR2(32) PRIMARY KEY,
NAME VARCHAR2(24) NOT NULL,
mobile VARCHAR2(12) DEFAULT ‘-’ UNIQUE,
sex VARCHAR2(4) DEFAULT ‘男’ NOT NULL CHECK( sex = ‘男’ OR sex = ‘女’),
age NUMBER(3) DEFAULT 0 CHECK( age BETWEEN 0 and 150) not null,
sign VARCHAR2(64) DEFAULT ‘这家伙很懒…’
);

–Step2:设置备注
COMMENT ON COLUMN person.personid IS ‘个人编号’;
COMMENT ON COLUMN person.NAME IS ‘昵称’;
COMMENT ON COLUMN person.age IS ‘年龄’;
COMMENT ON COLUMN person.sign IS ‘签名2’;
COMMENT ON COLUMN person.mobile IS ‘移动电话’;
COMMENT ON COLUMN person.sex IS ‘性别’;

COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
4、检查约束

/*
主题：
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

–Sample7:检查约束。
–Step1:删除测试表
DROP TABLE person ;

–Step2：创建表及约束
CREATE TABLE person
(
pid VARCHAR2(18) ,
NAME VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL CHECK(age BETWEEN 0 AND 150), --添加列级别检查约束
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’ CHECK(sex IN (‘男’,‘女’,‘中’)),–添加列级别检查约束
CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
CONSTRAINT person_name_uk UNIQUE(NAME)
) ;

–Step3：测试
INSERT INTO person(pid,NAME,age,birthday,sex) VALUES
(‘11111111111111111’,‘张三’,300,TO_DATE(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;–失败。提示“违反检查约束条件”

INSERT INTO person(pid,NAME,age,birthday,sex) VALUES
(‘11111111111111111’,‘张三’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘无’) ;–失败。提示“违反检查约束条件”

–Sample8:检查约束。
–Step1：删除测试表
DROP TABLE person ;

–Step2：创建表及约束
CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’,

CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
CONSTRAINT person_name_uk UNIQUE(NAME) ,
CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) ,--表级
CONSTRAINT person_sex_ck CHECK(sex IN ('男','女','中'))--表级

) ;

INSERT INTO person(pid,name,age,birthday,sex) VALUES
(‘11111111111111111’,‘张三’,300,TO_DATE(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;

INSERT INTO person(pid,name,age,birthday,sex) VALUES
(‘11111111111111111’,‘张三’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘无’) ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
5、唯一约束

/*
主题：Unique
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

–Sample5:唯一约束

–Step1:创建表.
DROP TABLE person ;
CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL UNIQUE,
Tel char(11) unique,
age NUMBER(3) NOT NULL ,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’ ,
CONSTRAINT person_pid_pk PRIMARY KEY(pid)
) ;

–Step2:进行唯一约束测试
INSERT INTO person(pid,name,tel,age,birthday,sex) VALUES
(‘11111111111111111’,‘张三’,‘18688888888’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;
INSERT INTO person(pid,NAME,tel,age,birthday,sex) VALUES
(‘33333333333333333’,‘李四’,‘18999999999’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;

INSERT INTO person(pid,NAME,age,birthday,sex) VALUES
(‘22222222222222222’,‘张三’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;–失败。提示“违反唯一约束条件”

UPDATE person
SET NAME = ‘张三’
where pid = ‘33333333333333333’;

SELECT * FROM person;

–Sample6:唯一约束。通过指定方式
–Step1:创建表.
DROP TABLE person ;
CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL ,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’ ,
CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
CONSTRAINT person_name_uk UNIQUE(NAME)–表级约束
) ;

–Step2:进行唯一约束测试.
INSERT INTO person(pid,name,age,birthday,sex) VALUES
(‘11111111111111111’,‘张三’,30,TO_DATE(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;
INSERT INTO person(pid,NAME,age,birthday,sex) VALUES
(‘22222222222222222’,‘张三’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;–失败。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
6、主键约束
/*
主题：主键约束
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：
PK:施加了PK约束的列,不能插入重复值和空值;主键约束必然会创建同名聚集索引.
注意：

*/

–Sample1:测试主键约束

–Step1:创建表。使用主键约束
DROP TABLE person ;

CREATE TABLE person
(
pid VARCHAR2(18) PRIMARY KEY ,–列级约束
name VARCHAR2(20) ,
age NUMBER(3) ,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘N’
) ;

–Step2：进行主键约束测试
INSERT INTO person(pid,name,age,birthday,sex) VALUES(‘11111111111111111’,‘张三’,30,TO_DATE(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;

INSERT INTO person(pid,NAME,age,birthday,sex) VALUES(‘11111111111111111’,‘李四’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘男’) ;–失败。提示“违反唯一约束条件”

INSERT INTO person(pid,name,age,birthday,sex) VALUES(NULL,‘王五’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘男’) ;–失败。提示“无法将 NULL 插入”

INSERT INTO person(pid,NAME,age,birthday,sex) VALUES(‘222222222222222222’,‘李四’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘男’) ;

INSERT INTO person(pid,NAME,age,birthday,sex) VALUES(‘333333333333333333’,‘李四’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘男’) ;
–Step3：删除表
DROP TABLE person ;

–Sample2:测试主键约束，指定一列作为主键
–Step1：
CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) ,
age NUMBER(3) ,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’ ,
CONSTRAINT pk_person_pid PRIMARY KEY(pid)
) ;

–Step2：进行主键约束测试
INSERT INTO person(pid,name,age,birthday,sex) VALUES(‘11111111111111111’,‘张三’,30,TO_DATE(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;

INSERT INTO person(pid,NAME,age,birthday,sex) VALUES(‘11111111111111111’,‘李四’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘男’) ;–失败。提示“违反唯一约束条件”

INSERT INTO person(pid,name,age,birthday,sex) VALUES(NULL,‘王五’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘男’) ;–失败。提示“无法将 NULL 插入”

–Step3：删除表
DROP TABLE person ;

–Sample3:测试联合主键

drop table friend;
–Step1:创建表.
CREATE TABLE friend(
ownerid VARCHAR2(32),–不可用列级约束定义联合主键
friendid VARCHAR2(32),–不可用列级约束定义联合主键
CONSTRAINT PK_friend_ownerid_friendid PRIMARY KEY(ownerid,friendid)
);

–Step2:进行主键约束测试.
INSERT INTO friend VALUES (‘wid001’,‘wid002’);
INSERT INTO friend VALUES (‘wid002’,‘wid001’);

INSERT INTO friend VALUES (‘wid001’,‘wid002’);–失败。提示“违反唯一约束条件”
INSERT into friend values (‘wid002’,‘wid001’);–失败。提示“违反唯一约束条件”

–Step3:删除表
DROP TABLE friend;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
7、外键约束
7.1、案例1

/*
主题：
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

–Sample9:没有外键约束

–Step1:创建表
DROP TABLE person ;
DROP TABLE book ;

CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’,

  CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
  CONSTRAINT person_name_uk UNIQUE(name) ,
  CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) ,
  CONSTRAINT person_sex_ck CHECK(sex IN ('男','女','中'))

) ;

CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
bname VARCHAR(30) ,
bprice NUMBER(5,2) ,
pid VARCHAR2(18)
) ;

–Step2:插入测试数据
INSERT INTO person(pid,name,age,birthday,sex) VALUES
(‘11111111111111111’,‘张三’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;

INSERT INTO book(bid,bname,bprice,pid) VALUES
(1,‘JAVA SE’,89.9,‘11111111111111111’) ;

INSERT INTO book(bid,bname,bprice,pid) VALUES
(2,‘JAVA SE’,89.9,‘00000000000000000’) ;

–Step3:查询
SELECT * FROM person;
SELECT * FROM book;

–Step4:
DROP TABLE person ;
DROP TABLE book ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
7.2、案例2

/*
主题：
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

–Sample10:外键约束
–Step1:创建表并实施外键约束
DROP TABLE person ;
DROP TABLE book ;
CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’,

CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
CONSTRAINT person_name_uk UNIQUE(name) ,
CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) ,
CONSTRAINT person_sex_ck CHECK(sex IN ('男','女','中'))

) ;

CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
bname VARCHAR(30) ,
bprice NUMBER(5,2) ,
pid VARCHAR2(18) ,

CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)

) ;

–Step2:插入测试数据
INSERT INTO person(pid,name,age,birthday,sex) VALUES(‘11111111111111111’,‘张三’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;

INSERT INTO book(bid,bname,bprice,pid) VALUES (1,‘JAVA SE’,89.9,‘11111111111111111’) ;

INSERT INTO book(bid,bname,bprice,pid) VALUES (2,‘JAVA SE’,89.9,‘00000000000000000’) ;–失败，提示“违反完整约束条件”；

–Step3:查询
SELECT * FROM person;
SELECT * FROM book;

–Step4:
DROP TABLE person ;
DROP TABLE book ;

TRUNCATE TABLE book;
TRUNCATE table person;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
7.3、案例3

/*
主题：
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

–Sample11:父表子表删除测试

–Step1:主表中的唯一/主键被外键引用，则不能删除
SELECT * FROM person;
SELECT * FROM book;

DROP TABLE person ;
DROP TABLE book ;

–Step2:可以先删除子表，然后删除父表
DROP TABLE book ;
DROP TABLE person ;

–Step3：强制删除
–此处考虑父表和子表都存在数据的情况下,对父表进行强制删除,对子表及其数据有和影响.
DROP TABLE person CASCADE CONSTRAINT;–删除层叠约束.
select * from book;–子表及原外键列的数据还存在.

–Sample12:删除记录测试

–Step1:创建表并实施外键约束
DROP TABLE person ;
DROP TABLE book ;

CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’,
CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
CONSTRAINT person_name_uk UNIQUE(name) ,
CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) ,
CONSTRAINT person_sex_ck CHECK(sex IN (‘男’,‘女’,‘中’))
) ;

CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
bname VARCHAR(30) ,
bprice NUMBER(5,2) ,
pid VARCHAR2(18) ,
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
) ;

–Step2:插入测试数据
INSERT INTO person(pid,name,age,birthday,sex) VALUES(‘11111111111111111’,‘张三’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;

INSERT INTO book(bid,bname,bprice,pid) VALUES (1,‘JAVA SE’,89.9,‘11111111111111111’) ;

–Step3:删除父表记录测试
SELECT * FROM person WHERE pid = ‘11111111111111111’;
DELETE from person WHERE PID = ‘11111111111111111’; --失败。提示“违反完整约束条件”

–Step4:删除子表记录测试
SELECT * FROM book where PID = ‘11111111111111111’;

DELETE FROM book WHERE pid = ‘11111111111111111’;
DELETE from person WHERE PID = ‘11111111111111111’;

–Sample13:更新记录测试

–Step1:创建表并实施外键约束
DROP TABLE book ;
DROP TABLE person ;

CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’,
CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
CONSTRAINT person_name_uk UNIQUE(name) ,
CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) ,
CONSTRAINT person_sex_ck CHECK(sex IN (‘男’,‘女’,‘中’))
) ;

CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
bname VARCHAR(30) ,
bprice NUMBER(5,2) ,
pid VARCHAR2(18) ,
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
) ;

–Step2:插入测试数据
INSERT INTO person(pid,name,age,birthday,sex) VALUES(‘11111111111111111’,‘张三’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;

INSERT INTO book(bid,bname,bprice,pid) VALUES (1,‘JAVA SE’,89.9,‘11111111111111111’) ;

–Step3:更新父表记录测试
SELECT * FROM person WHERE pid = ‘11111111111111111’;
UPDATE person set Pid = ‘999999999999999999’ Where pid = ‘11111111111111111’;–失败，提示“违反完整约束条件”
update book set pid =‘999999999999999999’ where pid = ‘11111111111111111’;–失败，提示“违反完整约束条件”

–Step4:更新子表记录测试
SELECT * FROM book WHERE pid = ‘11111111111111111’;
UPDATE book SET pid = ‘999999999999999999’ WHERE pid = ‘11111111111111111’;–业务错误。直接更新子表时，可能父表中不存在这样的值
UPDATE person SET pid = ‘999999999999999999’ WHERE pid = ‘11111111111111111’;–业务错误。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
7.4、案例4

/*
主题：
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

–Sample14:级联删除ON DELETE CASCADE

–Step1:创建表
DROP TABLE book ;
DROP TABLE person ;

CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’,
CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
CONSTRAINT person_name_uk UNIQUE(name) ,
CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) ,
CONSTRAINT person_sex_ck CHECK(sex IN (‘男’,‘女’,‘中’))
) ;

CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
bname VARCHAR(30) ,
bprice NUMBER(5,2) ,
pid VARCHAR2(18) ,
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
) ;

–Step2:插入数据
INSERT INTO person(pid,name,age,birthday,sex) VALUES(‘11111111111111111’,‘张三’,30,TO_DATE(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;
INSERT INTO book(bid,bname,bprice,pid) VALUES(1,‘JAVA SE’,89.9,‘11111111111111111’) ;

–Step3:查询数据
SELECT * FROM person NATURAL join book;
–Step4:删除数据
DELETE FROM person WHERE pid = ‘11111111111111111’;
SELECT * FROM book;

–Sample15:级联更新https://www.szcbjs.com/

–Step1:创建表
DROP TABLE book ;
DROP TABLE person ;

CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’,
CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
CONSTRAINT person_name_uk UNIQUE(name) ,
CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150) ,
CONSTRAINT person_sex_ck CHECK(sex IN (‘男’,‘女’,‘中’))
) ;

CREATE TABLE book
(
bid NUMBER PRIMARY KEY NOT NULL ,
bname VARCHAR(30) ,
bprice NUMBER(5,2) ,
pid VARCHAR2(18) ,
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
) ;

–Step2:插入数据
INSERT INTO person(pid,name,age,birthday,sex) VALUES(‘11111111111111111’,‘张三’,30,TO_DATE(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;
INSERT INTO book(bid,bname,bprice,pid) VALUES(1,‘JAVA SE’,89.9,‘11111111111111111’) ;

–Step3:查询数据
SELECT * FROM person NATURAL join book;

–Step4:创建更新触发器
CREATE OR REPLACE TRIGGER trigger_person_update_pid
AFTER UPDATE OF PID ON PERSON For EACH ROW
BEGIN
UPDATE book SET pid = :NEW.pid WHERE pid = :OLD.pid;
END;

–Step4:更新数据
UPDATE person SET pid = ‘8888’ WHERE pid = ‘11111111111111111’;
UPDATE person SET pid = ‘9999’ WHERE pid = ‘8888’;

SELECT * FROM person;
SELECT * FROM book;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
7.5、案例5

/*
主题：
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

/*
主题：联合主键的外键约束
作者：刘志宝
日期：2016年9月18日
说明：
1\子表中联合外键的创建.
2\父表中的联合主键的创建.
注意：
联合外键的书写顺序要一一对应,不然会发生"列类型与引用的列类型不兼容"错误.
*/

–Sample1:联合外键案例
CREATE TABLE testF(
clu1 NUMBER(10 ) ,
clu2 VARCHAR2(100 CHAR) ,

CONSTRAINT PK_TestF PRIMARY KEY(clu1,clu2)

);

CREATE TABLE testC(
clu3 NUMBER(10 ) ,
clu4 VARCHAR2(100 CHAR) ,

CONSTRAINT fk_testf_testc FOREIGN KEY(clu3,clu4) REFERENCES testf(clu1,clu2),
CONSTRAINT PK_TestC PRIMARY KEY(clu3,clu4)
);

insert into testF values(1,‘A’);
insert into testF values(2,‘B’);

insert into testC values(1,‘A’);
insert into testC values(2,‘B’);

insert into testC values(1,‘B’);
insert into testC values(2,‘A’);

DROP TABLE testc;
DROP TABLE testF;

–Sample2：活动和参与者

–创建活动表
CREATE TABLE Party(
partyID VARCHAR2(32) ,
NAME VARCHAR2(32) NOT NULL ,

CONSTRAINT  PK_Party_PartyID  PRIMARY key(partyID)

);

–创建参与者表
CREATE TABLE Actor(
actorID VARCHAR2(32),
name varchar2(32) not null,

CONSTRAINT PK_Actor_ActorID  PRIMARY key(actorID)

);
DROP TABLE Actor;

–创建报名表
create table Apply(
partyID varchar2(32),
actorID varchar2(32),

--创建联合主键
CONSTRAINT pk_Apply PRIMARY key(partyID  , actorID),
CONSTRAINT fk_Party_Apply foreign key(PartyID) references Party(PartyID),
CONSTRAINT fk_Actor_Apply foreign key(ActorID) references Actor(ActorID)

);

drop table apply;

–创建活动发言表
create table ApplySpeak(
ApplySpeakID varchar2(32),
partyID varchar2(32),
actorID varchar2(32),
content varchar2(100),

CONSTRAINT PK_ApplySpeak  PRIMARY key(ApplySpeakID),
CONSTRAINT FK_Apply_ApplySpeak  FOREIGN key(partyID , actorID) REFERENCES Apply(partyID , actorID)

);

drop table ApplySpeak;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
8、启用/禁用约束

/*
主题：
作者：刘志宝 Mailto: goldenunion@qq.com WechatID:goldenunion
日期：2016年9月18日
说明：

注意：

*/

–Sample16:修改约束
–Step1：创建表
DROP TABLE book;
DROP TABLE person ;

CREATE TABLE person
(
pid VARCHAR2(18) ,
name VARCHAR2(200) NOT NULL ,
age NUMBER(3) NOT NULL,
birthday DATE ,
sex VARCHAR2(4) DEFAULT ‘男’
) ;

–Step2:插入数据
INSERT INTO person(pid,NAME,age,birthday,sex) VALUES(‘6666’,‘张三’,30,to_date(‘1976-02-13’,‘yyyy-mm-dd’),‘女’) ;

SELECT * FROM person;

–Ster3:修改约束。添加主键
ALTER TABLE person
MODIFY (pid VARCHAR2(18) PRIMARY KEY);–列级修改

–Step4:修改约束。添加Check约束
ALTER TABLE person
ADD CONSTRAINT CK_Person_Sex CHECK( sex = ‘男’ OR sex = ‘女’ );–表级别.

–Sample17:删除约束

–Step1:删除约束
ALTER TABLE person
DROP CONSTRAINT ck_person_sex ;

–Sample18:删除级联约束

–Sample19:启用和禁用约束
alter table
disable constraint constraint_name [cascade];

drop table student;

CREATE TABLE student(
SID NUMBER(10) ,
NAME VARCHAR2(20),
CONSTRAINT student_sid_pk PRIMARY KEY(SID)
);

ALTER TABLE student
DISABLE CONSTRAINT student_sid_pk ;

ALTER TABLE student
ENABLE CONSTRAINT student_sid_pk ;

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值