Oracle数据库的SQL语句之完整性约束——基础篇

‘123’,’123′

/**
格式 FOREIGN KEY (外键列名) REFERENCES 主表(参照列)
意义:外键是营造于三个表的多个字段恐怕多个表的八个字段之间的关系,消除数量冗余(多余)难点。
例如:通过clazz_id字段将tb_claz表和tb_student表关联起来。

SELECT * FROM tb_clazz;

clazz_id INT REFERENCES tb_clazz(ID)

INSERT INTO tb_student(ID,name,sex,age,address)
VALUES(1,’林志玲’,’女’,41,’台湾’);

INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’);
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(2,’林志玲’,’女’,41,’123′,’lzl@qq.com’,’台湾’);
— 以下数据丧失数据完整性
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(3,null,’妖’,200,’123′,’lzl@qq.com’,’台湾’);

— 删除个中一条数据,发现不或者唯壹明显某一条记下
DELETE FROM tb_student WHERE

— 查询两条相同数量
INSERT INTO tb_student(ID,name,sex,age,phone,email,address)
VALUES(1,’林志玲’,’女’,41,’111′,’lzl@qq.com’,’台湾’);

INSERT INTO tb_clazz(code,NAME,bzr)
VALUES(‘1401′,’14计算机1班’,’廖老师’);
INSERT INTO tb_clazz(code,NAME,bzr)
VALUES(‘1402′,’14计算机2班’,’谢老师’);

clazz_id INT REFERENCES tb_clazz(ID) ON DELETE CASCADE

INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’);

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;


2.外键[clazz_id]参考的只好是主表[tb_clazz]主键或然唯壹键,保障子表记录能够确切定位到被参照的记录。
— 反证法注解,tb_clazz参照的不是主键也不是唯壹键,会现身的难点
CREATE TABLE tb_clazz(
ID INT ,
CODE VARCHAR(18),
NAME VARCHAR(18),
bzr VARCHAR(18)
);
INSERT INTO tb_clazz(id,code,NAME,bzr)
VALUES(1,’1401′,’14计算机1班’,’廖老师’);
INSERT INTO tb_clazz(id,code,NAME,bzr)
VALUES(1,’1402′,’14计算机2班’,’谢老师’);

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;

(二)ON DELETE SET NULL:将凭借的外键值转换为空值

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100),
clazz_id INT REFERENCES tb_clazz(ID)
);
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address,clazz_id)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’,1);

INSERT INTO
tb_student(ID,NAME,sex,age,phone,email,address,clazzcode,clazzname,clazzbzr)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’,’1401′,’14计算机1班’,’廖老师’);

— 删除当中一条数据,发现不能唯1显著某一条记下
DELETE FROM tb_student WHERE

化解数据冗余问题选拔外键。
关周到据库就是指多个表经过外键产生关联关系。
例如:通过tb_student表的clazz_id列让tb_clazz表和tb_student表产生了关联的关联。
*/
CREATE TABLE tb_clazz(
ID INT PRIMARY KEY,
CODE VARCHAR(18),
NAME VARCHAR(18),
bzr VARCHAR(18)
);
INSERT INTO tb_clazz(id,code,NAME,bzr)
VALUES(1,’1401′,’14计算机1班’,’廖老师’);
INSERT INTO tb_clazz(id,code,NAME,bzr)
VALUES(2,’1402′,’14计算机2班,’谢老师’);

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100),
clazz_id INT
);
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address,clazz_id)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’,1);

clazz_id INT REFERENCES tb_clazz(ID) ON SET NULL
*/

INSERT INTO
tb_student(ID,name,sex,age,phone,email,address,clazzcode,clazzname,clazzbzr)
VALUES(2,’林志玲’,’女’,41,’111′,’lzl@qq.com’,’台湾’,’1402′,’14计算机2班’,’廖老师’);
/*
以上数据库插入数据难题:若是有一百位在1401班,则九二十一人的clazzcode,clazzname,clazzbzr音信
完全1致,3个上学的儿童的clazzcode,clazzname,clazzbzr消息存款和储蓄需求1七个字节,九十几个学生供给一七*100个字节,
而那么些音信大旨属于重复的(数据冗余),浪费存款和储蓄空间。

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;

INSERT INTO tb_student(ID,name,sex,age,address)
VALUES(1,’林志玲’,’女’,41,’台湾’);

— 因为主键非空并且唯一,所以插入时务必插入,并且不能壹如既往
INSERT INTO tb_student(ID,name,sex,age,address)
VALUES(1,’林志玲’,’女’,41,’台湾’);
INSERT INTO tb_student(ID,name,sex,age,address)
VALUES(2,’林志玲’,’女’,41,’台湾’);
— 删除当中一条数据,遵执照主人键id删除
DELETE FROM tb_student WHERE ID = 2;

SELECT * FROM tb_clazz;

SELECT * FROM tb_student;

3.当主表[tb_clazz]的笔录被子表[tb_student]参考时,主表记录不允许被删除。
DELETE FROM tb_clazz WHERE ID = 1;
— 化解方案一:先删除子表数据,再删除父表数据
DELETE FROM tb_student WHERE clazz_id = 1;
— 化解方案二:先将子表关联数据设置为null,再删除父表数据
UPDATE tb_student SET clazz_id = NULL WHERE clazz_id = 1;
— 消除方案三:先将子表数据修改,再删除父表数据
UPDATE tb_student SET clazz_id = 2 WHERE clazz_id = 1;

— 四. ON DELETE
CASCADE指当删除父表数据时,级联删除子表数据,对应下面的方案一
— ON SET NULL
指当删除父表数据时,将波及的多少设置为null,对应上面包车型大巴方案二

 

/*******************主键**************************/
/**
重点
格式 PRIMARY KEY
成效:用来唯一分明1行记录
(壹)主键是表中唯壹分明1行数据的字段,主键从作用上看约等于非空且唯一
(二)3个表中只允许一个主键
(三)主键字段能够是单字段可能是多字段的叁结合
(4)当建立主键约束时,Oracle为主键创造对应的目录
(伍)现代数据库建立模型,提议一张表一定要有主键,并且主键应该和事情数据非亲非故,提出利用机动增进的自然数。
*/
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100)
);

INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’);
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(2,’林志玲’,’女’,41,’123′,’lzl@qq.com’,’台湾’);
— 以下数据丧失数据完整性
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(3,null,’妖’,200,’123′,’lzl@qq.com’,’台湾’);

INSERT INTO tb_clazz(code,NAME,bzr)
VALUES(‘1401′,’14计算机1班’,’廖老师’);
INSERT INTO tb_clazz(code,NAME,bzr)
VALUES(‘1402′,’14计算机2班’,’谢老师’);

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100),
clazz_id INT REFERENCES tb_clazz(ID)
);
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address,clazz_id)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’,1);


建表时,直接在列后边创造约束,称为列级约束,数据库会暗中同意分配唯1的羁绊名SYS_624
CREATE TABLE tb_student(
ID INT,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100)
);

/*******************主键**************************/
/**
重点
格式 PRIMARY KEY
效用:用来唯1显著一行记录
(一)主键是表中绝无仅有显明一行数据的字段,主键从成效上看约等于非空且唯壹
(二)2个表中只同意3个主键
(3)主键字段能够是单字段大概是多字段的三结合
(四)当建立主键约束时,Oracle为主键创造对应的目录
(5)现代数据库建立模型,提议一张表一定要有主键,并且主键应该和作业数据非亲非故,指出使用自动拉长的自然数。
*/
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100)
);

INSERT INTO
tb_student(ID,NAME,sex,age,phone,email,address,clazzcode,clazzname,clazzbzr)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’,’1401′,’14计算机1班’,’廖老师’);

clazz_id INT REFERENCES tb_clazz(ID) ON DELETE CASCADE

— 因为主键非空并且唯一,所以插入时必须插入,并且不可能一如既往
INSERT INTO tb_student(ID,name,sex,age,address)
VALUES(1,’林志玲’,’女’,41,’台湾’);
INSERT INTO tb_student(ID,name,sex,age,address)
VALUES(2,’林志玲’,’女’,41,’台湾’);
— 删除其中一条数据,依照主键id删除
DELETE FROM tb_student WHERE ID = 2;


建表时,直接在列前边创制约束,称为列级约束,数据库会私下认可分配唯一的牢笼名SYS_624
CREATE TABLE tb_student(
ID INT,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100)
);

/**
多数数据库协助下边几类完整性约束:
CHECK 检查约束
— NOT NULL 非空约束(特殊的反省约束)
UNIQUE KEY 唯壹约束
P锐界IMA昂CoraY KEY 主键约束
FOREIGN KEY 外键约束
*/


1.子(从)表[tb_student]外键列[clazz_id]的值必须在父(主)表[tb_clazz]参照列[id]值的范围内,
— 只怕为空(也足以加非空约束,强制不允许为空)。
INSERT INTO tb_student(ID,name,sex,age,phone,email,address,clazz_id)
VALUES(3,’柳岩’,’女’,36,’222′,’ly@qq.com’,’台湾’,9);


2.外键[clazz_id]参考的只可以是主表[tb_clazz]主键也许唯一键,保险子表记录能够确切定位到被参照的记录。
— 反证法注明,tb_clazz参照的不是主键也不是唯一键,会出现的标题
CREATE TABLE tb_clazz(
ID INT ,
CODE VARCHAR(18),
NAME VARCHAR(18),
bzr VARCHAR(18)
);
INSERT INTO tb_clazz(id,code,NAME,bzr)
VALUES(1,’1401′,’14计算机1班’,’廖老师’);
INSERT INTO tb_clazz(id,code,NAME,bzr)
VALUES(1,’1402′,’14计算机2班’,’谢老师’);

INSERT INTO
tb_student(ID,name,sex,age,phone,email,address,clazzcode,clazzname,clazzbzr)
VALUES(2,’林志玲’,’女’,41,’111′,’lzl@qq.com’,’台湾’,’1402′,’14计算机2班’,’廖老师’);
/*
如上数据库插入数据难点:如果有九捌人在140一班,则九十九人的clazzcode,clazzname,clazzbzr消息
完全1致,三个上学的儿童的clazzcode,clazzname,clazzbzr新闻存款和储蓄须求一七个字节,九十三个学生须要壹柒*100个字节,
而这一个新闻为主属于重复的(数据冗余),浪费存款和储蓄空间。

clazz_id INT REFERENCES tb_clazz(ID) ON SET NULL
*/

CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100),
clazz_id INT
);
INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address,clazz_id)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’,1);

INSERT INTO tb_student(ID,name,sex,age,phone,email,address,clazz_id)
VALUES(2,’林志玲’,’女’,41,’111′,’lzl@qq.com’,’台湾’,1);

— 查询两条相同数量
INSERT INTO tb_student(ID,name,sex,age,phone,email,address)
VALUES(1,’林志玲’,’女’,41,’111′,’lzl@qq.com’,’台湾’);

(二)ON DELETE SET NULL:将依靠的外键值转换为空值

2.外键[clazz_id]参照的只好是主表[tb_clazz]主键或许唯1键,保障子表记录能够精鲜明位到被参照的记录。
3.当主表[tb_clazz]的笔录被子表[tb_student]参考时,主表记录不容许被去除。
肆.建表时可以扩大以下设置:
(1)ON DELETE
CASCADE:当父表中的行被删除的时候,同时删除在子表中凭借的行

SELECT * FROM tb_student;

3.当主表[tb_clazz]的记录被子表[tb_student]参考时,主表记录分裂意被删除。
DELETE FROM tb_clazz WHERE ID = 1;
— 消除方案一:先删除子表数据,再删除父表数据
DELETE FROM tb_student WHERE clazz_id = 1;
— 消除方案二:先将子表关联数据设置为null,再删除父表数据
UPDATE tb_student SET clazz_id = NULL WHERE clazz_id = 1;
— 化解方案三:先将子表数据修改,再删除父表数据
UPDATE tb_student SET clazz_id = 2 WHERE clazz_id = 1;

— 4. ON DELETE
CASCADE指当删除父表数据时,级联删除子表数据,对应下面的方案一
— ON SET NULL
指当删除父表数据时,将涉及的多少设置为null,对应下边包车型大巴方案二

 

/*****************外键****************************/
— 保存学生数量时索要精晓学生的班级意况
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100),
clazzcode VARCHAR(18),
clazzname VARCHAR(18),
clazzbzr VARCHAR(18)
);
SELECT * FROM tb_student;


1.子(从)表[tb_student]外键列[clazz_id]的值必须在父(主)表[tb_clazz]参照列[id]值的限量内,
— 只怕为空(也得以加非空约束,强制不容许为空)。
INSERT INTO tb_student(ID,name,sex,age,phone,email,address,clazz_id)
VALUES(3,’柳岩’,’女’,36,’222′,’ly@qq.com’,’台湾’,9);

/**
格式 FOREIGN KEY (外键列名) REFERENCES 主表(参照列)
成效:外键是营造于三个表的多少个字段可能三个表的八个字段之间的关联,化解数量冗余(多余)难题。
例如:通过clazz_id字段将tb_claz表和tb_student表关联起来。

SELECT * FROM tb_student;

SELECT * FROM tb_clazz;
SELECT * FROM tb_student;

/*****************外键****************************/
— 保存学生数量时须要明白学生的班级景况
CREATE TABLE tb_student(
ID INT PRIMARY KEY,
NAME VARCHAR(18) NOT NULL,
sex CHAR(3) CHECK(sex = ‘男’ OR sex = ‘女’),
age INT CHECK(age > 15 AND age < 50),
phone VARCHAR(18) UNIQUE,
email VARCHAR(50) UNIQUE,
address VARCHAR(100),
clazzcode VARCHAR(18),
clazzname VARCHAR(18),
clazzbzr VARCHAR(18)
);
SELECT * FROM tb_student;

/**
大部数据库援救上边几类完整性约束:
CHECK 检查约束
— NOT NULL 非空约束(特殊的检查约束)
UNIQUE KEY 唯一约束
P大切诺基IMA福睿斯Y KEY 主键约束
FOREIGN KEY 外键约束
*/

2.外键[clazz_id]参照的只好是主表[tb_clazz]主键也许唯壹键,有限协助子表记录能够规范定位到被参照的笔录。
3.当主表[tb_clazz]的笔录被子表[tb_student]参考时,主表记录不容许被删去。
四.建表时能够追加以下设置:
(一)ON DELETE
CASCADE:当父表中的行被删除的时候,同时删除在子表中凭借的行

SELECT * FROM tb_student;

缓解数量冗余难题采用外键。
关周全据库正是指几个表经过外键发生关联关系。
例如:通过tb_student表的clazz_id列让tb_clazz表和tb_student表产生了关系的关联。
*/
CREATE TABLE tb_clazz(
ID INT PRIMARY KEY,
CODE VARCHAR(18),
NAME VARCHAR(18),
bzr VARCHAR(18)
);
INSERT INTO tb_clazz(id,code,NAME,bzr)
VALUES(1,’1401′,’14计算机1班’,’廖老师’);
INSERT INTO tb_clazz(id,code,NAME,bzr)
VALUES(2,’1402′,’14计算机2班,’谢老师’);

clazz_id INT REFERENCES tb_clazz(ID)

INSERT INTO tb_student(ID,NAME,sex,age,phone,email,address)
VALUES(1,’周杰伦’,’男’,31,’123′,’jack@qq.com’,’台湾’);

外键要小心的标题:
1.子(从)表[tb_student]外键列[clazz_id]的值必须在父(主)表[tb_clazz]参照列[id]值的限量内,
恐怕为空(也得以加非空约束,强制不容许为空)。

外键要留意的标题:
1.子(从)表[tb_student]外键列[clazz_id]的值必须在父(主)表[tb_clazz]参照列[id]值的界定内,
恐怕为空(也能够加非空约束,强制不容许为空)。

INSERT INTO tb_student(ID,name,sex,age,phone,email,address,clazz_id)
VALUES(2,’林志玲’,’女’,41,’111′,’lzl@qq.com’,’台湾’,1);