ORACLE数据库的SQL语句之约束——基础篇

/*修改用户密码:
1.输入cmd打开dos窗口
2.sqlplus/nolog
3.conn/as sysdba
4.alter user system identified by orcl;
*/

/**
数据完整性指存储在数据库中的所有数据值均正确的状态。
如果数据库中存储有不正确的数据值，则该数据库称为已丧失数据完整性
*/

CREATE TABLE tb_student(
NAME VARCHAR2(18),
sex CHAR(3),
age INT,
address VARCHAR2(100),
phone VARCHAR2(30)
)

SELECT * FROM tb_student;

--不正确状态数据
INSERT INTO tb_clazz(sex,age,address,phone)VALUES('女','19','广州','1390000115');
INSERT INTO tb_clazz(NAME,sex,age,address,phone)VALUES('alice','妖','19','广州','1390000115');
INSERT INTO tb_clazz(NAME,sex,age,address,phone)VALUES('alice','女','19','广州','1390000115');

/**
约束是在表上强制执行的数据校验规则
约束主要用于保证数据库的完整性
大部分数据库支持下面五类完整性约束
UNIQUE Key唯一键
CHECK检查(NOT NULL非空)
*/

/**重点：
PRIMARY KEY主键
作用：主键是表中唯一确定一行数据的字段，主键从功能上看相当于非空且唯一
1.一个表中只允许一个主键
联合主键，主键字段可以是单字段或者是多字段的组合
2.现代数据库建模，建议一张表一定要有主键，并且主键应该和业务数据无关，建议使用自动增长的自然数。

重点：
FOREIGN KEY外键
格式FOREIGN KEY (外键列名) REFERENCES 主表(参照列)
作用：外键是构建于一个表的两个字段或者两个字段之间的关系，解决数据冗余问题。
课堂示例：通过clazz_id字段将tb_clazz表和tb_student表关联起来。
*/

/**外键要注意的问题：
1.子（从）表[tb_student]外键列[clazz_id]的值必须在父（主）表[tb_clazz]参照列[id]的范围内。
或者为空（也可以加非空约束，强制不允许为空）
2.外键[clazz_id]参照的只能是主表[tb_clazz]主键或者唯一键，保证子表记录可以准确定位到被参照的记录。
3.当主表[tb_clazz]的记录被子表[tb_student]参照时，主表记录不允许被删除。
解决方案1：先删除关联的子表数据，再删除主表数据
*/
SELECT * FROM tb_student;
ALTER

--先删除主表的ID
DELETE FROM tb_student WHERE clazz_id = 1;
--再删除从表的ID
DELETE FROM tb_clazz WHERE ID = 1;

--解决方案2：先将关联的子表数据修改，再删除主表数据
UPDATE tb_student SET clazz_id = 1 WHERE CLAZZ_ID = 3;
DELETE FROM tb_clazz WHERE ID = 3;

SELECT * FROM tb_student;
SELECT * FROM tb_clazz;

--解决方案3:先将关联的子表数据设置为null，再删除主表数据
UPDATE tb_student SET clazz_id = NULL WHERE clazz_id = 2;
DELETE FROM tb_clazz WHERE ID = 2;

4.建表时可以增加以下设置：
--层叠效果删除CASCADE
(1)ON DELETE CASCADE:当父表中的行被删除的时候，同时删除再子表中依靠的行
--把B表中依靠外键A表的值转换为空值
(2)ON DELETE SET NULL:将依靠的外键值转换为空值，该外键设置为空

/*在建表时直接在列后面创建约束称为列级约束，数据库会默认给约束增加一个唯一
的名称SYS_C007217用于管理**/
DROP TABLE tb_student;

SELECT * FROM tb_student;

CREATE TABLE tb_student(
ID INT PRIMARY KEY,--主键约束
NAME VARCHAR2(30) NOT NULL,--非空约束
sex CHAR(3) DEFAULT '男' CHECK(sex='男' OR sex='女'),--检查约束
age INT CHECK(age >6 AND age<149),--检查约束
phone VARCHAR2(11) UNIQUE --唯一约束UNIQUE
);

--测试非空约束
INSERT INTO tb_student(sex,age,phone)VALUES('男',23,'15913115996');

--测试检查约束
--插入性别为妖，手机号为重复的数据，年龄200
INSERT INTO tb_student(ID,name,sex,age,phone)VALUES(1,'admin','妖',23,'15913115996');
INSERT INTO tb_student(ID,name,sex,age,phone)VALUES(2,'admin','男',2000,'15913115996');

--测试唯一约束
INSERT INTO tb_student(ID,name,sex,age,phone)VALUES(1,'admin','男','20','15913115996');
INSERT INTO tb_student(ID,name,sex,age,phone)VALUES(2,'admin','男','20','15913115996');

--为什么要有主键约束?观察插入重复数据
--主键用来保证数据的完整性和唯一性
INSERT INTO tb_student(name,sex,age,phone)VALUES('admin','男','20','15913115996');
INSERT INTO tb_student(name,sex,age,phone)VALUES('admin','男','20','15913115996');

--为什么要有外键？
DROP TABLE tb_student;
DROP TABLE tb_clazz;

SELECT * FROM tb_clazz;

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR2(18) NOT NULL,
sex CHAR(3) DEFAULT '男' CHECK(sex='男' OR sex='女'),
age INT CHECK(age > 15 AND age <60),
address VARCHAR2(30) UNIQUE,
phone VARCHAR2(30) UNIQUE,
--班级名字，班级编码，班主任
clazz_name VARCHAR2(30),
CODE VARCHAR2(30),
bzr VARCHAR2(30)
);

SELECT * FROM tb_student;
SELECT * FROM tb_clazz;
--这样插入数据会出现数据冗余
--插入数据
INSERT INTO tb_student(ID,NAME,SEX,AGE,ADDRESS,PHONE,CLAZZ_NAME,CODE,bzr)
VALUES(1,'admin','男',23,'广州','15913115996','j1707','java就业班','谢老师');

--建立外键关联两个表

--主表 班级表
CREATE TABLE tb_clazz(
ID INT PRIMARY KEY,--如果要设置外键，那么主表必须有一个主键
CODE VARCHAR2(18),
NAME VARCHAR2(18),
bzr VARCHAR2(18)
);

INSERT INTO tb_clazz(id,CODE,NAME,bzr)VALUES(1,'j1709','java就业班','谢老师');
INSERT INTO tb_clazz(id,CODE,NAME,bzr)VALUES(2,'j1710','java就业班','李老师');
INSERT INTO tb_clazz(id,CODE,NAME,bzr)VALUES(3,'j1711','java基础班','黄老师');

--列级约束
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR2(18) NOT NULL,
sex CHAR(3) DEFAULT '男' CHECK(sex='男' OR sex='女'),
age INT CHECK(age >15 AND age <60),
address VARCHAR2(100),
phone VARCHAR2(30) UNIQUE,
--班级外键
clazz_id INT,
--外键 （外键名） 引用 主表（主键）
FOREIGN KEY (clazz_id) REFERENCES tb_clazz(ID)
)

INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(1,'admin','男',23,'广州','15913115996',1);
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(2,'admin2','男',23,'广州','15913115997',2);
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(3,'admin3','男',23,'广州','15913115998',3);

--插入数据
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(1,'admin','男',23,'广州','15913115996',1);
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(2,'rose','女',21,'深圳','15913115997',2);

--测试：1.子（从）表[tb_student]外键列[clazz_id]的值必须在父(主)表[tb_clazz]参照列[id]值的范围内
--错误信息：未找到父项关键字，因为现在tb_clazz当中没有id为9的班级
INSERT INTO tb_student(ID,NAME,sex,age,address,phone,clazz_id)
VALUES(3,'rose','女',21,'深圳','15913115998',9);

--测试：2.外键[clazz_id]参照的只能是主表[tb_clazz]主键或者唯一键，
--保证子表记录可以准确定位到被参照的记录。
CREATE TABLE tb_clazz1(
ID INT,
CODE VARCHAR2(18),
NAME VARCHAR2(18),
bzr VARCHAR2(18)
);

INSERT INTO tb_clazz1(id,CODE,NAME,bzr)VALUES(1,'j1509','java就业班','谢老师');
INSERT INTO tb_clazz1(id,CODE,NAME,bzr)VALUES(1,'j1508','java就业班','李老师');

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;

--测试：3.当主表[tb_clazz]的记录被子表[tb_student]参照时,主表记录不允许被删除。
--错误信息：已找到子记录
DELETE FROM tb_clazz WHERE ID = 1;

-- 解决方案2：先将关联的子表数据修改，再删除主表数据
--子表修改的CLAZZ_ID值必须要在主表中的ID值范围内
UPDATE tb_student SET clazz_id = 3 WHERE ID =1;
DELETE FROM tb_clazz WHERE ID = 2;

--(1)ON DELETE CASCADE:当父表中的行被删除的时候，同时删除在子表中依靠的行
--(2)ON DELETE SET NULL:将依靠的外键值转换为空值
--建表时增加

DROP TABLE tb_student;

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR2(18) NOT NULL,
sex CHAR(3) DEFAULT '男' CHECK(sex='男' OR sex='女'),
age INT CHECK(age >15 AND age <60),
address VARCHAR2(100),
phone VARCHAR2(30) UNIQUE,
--外键 （外键名） 引用 主表（主键）
FOREIGN KEY (clazz_id) REFERENCES tb_clazz(ID),
--当删除主表数据时要把子表关联的数据设置为空
clazz_id INT REFERENCES tb_clazz(ID) ON DELETE SET NULL
);

DELETE FROM tb_clazz WHERE ID = 1;

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;