oracle约束

最新推荐文章于 2022-07-25 17:17:30 发布
最新推荐文章于 2022-07-25 17:17:30 发布
阅读量1k 收藏 3
点赞数
文章标签： oracle约束 添加约束 修改约束 删除约束
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/czh500/article/details/82834208
版权 
/*

约束

5种约束

1.NOT NULL (注意:非空约束只能定义在列上)

2.UNIQUE 

3.PRIMARY KEY

4.FOREIGN KEY

5.CHECK

*/
--
/*

创建和修改/维护约束

*/
--建表的同时创建约束
CREATE TABLE emp20180924(
/*
创建非空约束(显示的给这个非空约束取个约束名，为了规范和方便阅读和维护，约
束名最好是这种格式，表名_列名_约束类型,如emp20180924_id_nn)
*/
ID NUMBER(10) CONSTRAINT  emp20180924_id_notnull NOT NULL, 
--创建非空约束(没有给这个非空约束取约束名，系统会自动的给个默认的约束名，但是我还是建议大家自己取一个规范的约束名)
NAME VARCHAR2(30) NOT NULL, 
salary NUMBER(15, 2)
)
--查询emp20180924表的数据
SELECT * 
FROM emp20180924
--插入数据
INSERT INTO emp20180924
VALUES(10001, '令狐冲', NULL)
--插入数据
INSERT INTO emp20180924
VALUES(10001, '令狐冲', 7200.56)

/*

注意：UNIQUE唯一约束是可以插入null空值的，并且每条记录都可以插入null空值，这并不违反UNIQUE唯一约束

*/
--创建表
CREATE TABLE emp8(
--唯一约束
ID NUMBER(12) CONSTRAINT emp8_id_uk UNIQUE, --列级约束
--非空约束
NAME varchar2(30) CONSTRAINT emp8_name_nn NOT NULL, --列级约束
email VARCHAR2(50),
salary NUMBER(15, 2),
--表级约束 
CONSTRAINT emp8_email_uk UNIQUE(email) --指明该约束作用于email这一列上
)
--查询emp8表数据
SELECT * 
FROM emp8
--往emp8表中插入数据
INSERT INTO emp8
VALUES(1002, '韦小宝', 'weixiaobao@qq.com', NULL)

--
/*
注意：
email这一列定义了UNIQUE唯一约束
如下：插入了2条数据，插入这2条数据时，email都是插入的null值,数据都能正常插入，说明
了，多条记录都插入null值，并不会违反UNIQUE唯一约束
*/
--往emp8表中插入数据(UNIQUE唯一约束是可以插入null空值的，并且每条记录都可以插入null空值，这并不违反UNIQUE唯一约束)
INSERT INTO emp8
VALUES(1003, '令狐冲', NULL, 2500)
--往emp8表中插入数据(UNIQUE唯一约束是可以插入null空值的，并且每条记录都可以插入null空值，这并不违反UNIQUE唯一约束)
INSERT INTO emp8
VALUES(1004, '张无忌', NULL, 2900)

--
--创建表(主键约束)
CREATE TABLE emp9(
--主键约束
ID NUMBER(12) CONSTRAINT emp9_id_pk PRIMARY KEY, --列级约束
--非空约束
NAME varchar2(30) CONSTRAINT emp9_name_nn NOT NULL, --列级约束
email VARCHAR2(50),
salary NUMBER(15, 2),
--表级约束 
CONSTRAINT emp9_email_uk UNIQUE(email) --指明该约束作用于email这一列上
)
--
SELECT * 
FROM emp9
/*

PRIMARY KEY 主键约束 (主键约束：不能为null,并且唯一)

*/
--往emp9表中插入数据(插入数据失败)
INSERT INTO emp9
VALUES(NULL, '张无忌', 'zhangwuji@qq.com', NULL)
--往emp9表中插入数据
INSERT INTO emp9
VALUES(100, '郭靖', 'test@qq.com', NULL)
--往emp9表中插入数据(插入数据失败)
INSERT INTO emp9
VALUES(100, '杨过', 'yangguo@qq.com', 8050.67)
--往emp9表中插入数据(插入数据成功)
INSERT INTO emp9
VALUES(108, '双儿', 'shuanger@qq.com', 9865.32)


--创建表(主键约束)
CREATE TABLE emp10(
--主键约束
ID NUMBER(12),
--非空约束
NAME varchar2(30) CONSTRAINT emp10_name_nn NOT NULL, --列级约束
email VARCHAR2(50),
salary NUMBER(15, 2),
--表级约束 
CONSTRAINT emp10_email_uk UNIQUE(email), --指明该约束作用于email这一列上
CONSTRAINT emp10_id_pk PRIMARY KEY(ID)
)
--NAME列已经定义了非空约束，我这里又定义了name列的默认值为null
ALTER TABLE emp10 
MODIFY (NAME VARCHAR2(30) DEFAULT NULL);
--
SELECT * 
FROM emp10
--插入数据失败，插入数据报错
INSERT INTO emp10 (id, email, salary)
VALUES(10, 'test@qq.com', 8500.66)
--插入数据成功
INSERT INTO emp10 (id, name, email, salary)
VALUES(12, '张三', 'test@qq.com', 8500.66)


--
/*

外键约束

*/

--创建表(外键约束)
CREATE TABLE emp11(
--主键约束
ID NUMBER(12),
--非空约束
NAME varchar2(30) CONSTRAINT emp11_name_nn NOT NULL, --列级约束
email VARCHAR2(50),
salary NUMBER(15, 2),
department_id NUMBER(10),
--表级约束 
CONSTRAINT emp11_email_uk UNIQUE(email), --指明该约束作用于email这一列上
CONSTRAINT emp11_id_pk PRIMARY KEY(ID),
--外键约束
CONSTRAINT emp11_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)
--
SELECT * 
FROM emp11
--
SELECT * 
FROM departments
--插入数据成功(因为departments表中的department_id列中有10这个值，也就是说departments表中有10号部门)
INSERT INTO emp11
VALUES(100, 'AA', 'aa@q63.com', 9650.55, 10)
--违反了主外键约束，插入数据失败(因为departments表中的department_id列中没有680这个值，也就是说departments表中没有680号部门)
INSERT INTO emp11
VALUES(101, 'CC', 'cc@q63.com', 8265.36, 680)

/*

约束命令规范
约束名称建议自己定义一套命名规则，否则使用系统生成的约束名，很难能把它和对应的表、字段联系起来

        非空约束 　　　　NN_表名_列名

　　　　唯一约束 　　　　UK_表名_列名

　　　　主键约束 　　　　PK_表名

　　　　外键约束 　　　　FK_表名_列名

　　　　条件约束 　　　　CK_表名_列名

　　　　默认约束 　　　　DF_表名_列名

*/

/*

级联删除 ON DELETE CASCADE

级联置空 ON DELETE SET NULL

*/

--级联置空
CREATE TABLE emp12(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp12_name_nn NOT NULL,
email VARCHAR2(50),
salary NUMBER(15, 2),
department_id NUMBER(10),
 
CONSTRAINT emp12_email_uk UNIQUE(email), 
CONSTRAINT emp12_id_pk PRIMARY KEY(ID),
--级联置空
CONSTRAINT emp12_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ON DELETE SET NULL
)
--
SELECT * 
FROM emp12
--
SELECT * 
FROM departments
--
INSERT INTO emp12
VALUES(103, 'AA', 'AA@qq.com', 6790.26, 260)

--
INSERT INTO emp12
VALUES(104, 'BB', 'BB@126.com', 5186.68, 260)
--
INSERT INTO emp12
VALUES(105, 'CC', 'CC@163.com', 12680.91, 270)
--从departments表中删除260号部门 (测试级联置空)
DELETE FROM departments dep
WHERE dep.department_id = 260

--

--级联删除
CREATE TABLE emp13(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp13_name_nn NOT NULL,
email VARCHAR2(50),
salary NUMBER(15, 2),
department_id NUMBER(10),
 
CONSTRAINT emp13_email_uk UNIQUE(email), 
CONSTRAINT emp13_id_pk PRIMARY KEY(ID),
--级联删除
CONSTRAINT emp13_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ON DELETE CASCADE 
)
--
SELECT * 
FROM emp13
--
SELECT * 
FROM departments
--
INSERT INTO emp13
VALUES(108, 'AA', 'AA@qq.com', 6790.26, 270)

--
INSERT INTO emp13
VALUES(109, 'BB', 'BB@126.com', 5186.68, 270)
--
INSERT INTO emp13
VALUES(110, 'CC', 'CC@163.com', 12680.91, 240)
--从departments表中删除270号部门 (测试级联删除)
DELETE FROM departments dep
WHERE dep.department_id = 270
--

/*

check约束

*/

CREATE TABLE emp14(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp14_name_nn NOT NULL,
email VARCHAR2(50),
--check约束，系统会给一个默认的约束名
salary NUMBER(15, 2) CHECK(salary > 1980 AND salary < 300000),
department_id NUMBER(10),
 
CONSTRAINT emp14_email_uk UNIQUE(email), 
CONSTRAINT emp14_id_pk PRIMARY KEY(ID),

CONSTRAINT emp14_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)
--
SELECT * 
FROM emp14
--插入一条符合check约束的数据
INSERT INTO emp14
VALUES(101, '令狐冲', 'linghuchong@163.com', 6790.55, 10)
--插入一条不符合check约束的数据(插入失败，违反check约束)
INSERT INTO emp14
VALUES(101, '令狐冲', 'linghuchong@163.com', 860.89, 10)

--check约束，显式的给一个约束名
CREATE TABLE emp15(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp15_name_nn NOT NULL,
email VARCHAR2(50),
--check约束，显式的给一个约束名
salary NUMBER(15, 2) CONSTRAINT emp15_salary_ck CHECK(salary > 1980 AND salary < 300000),
department_id NUMBER(10),
 
CONSTRAINT emp15_email_uk UNIQUE(email), 
CONSTRAINT emp15_id_pk PRIMARY KEY(ID),

CONSTRAINT emp15_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)
--
SELECT * 
FROM emp15

--列级外键约束
CREATE TABLE emp16(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp16_name_nn NOT NULL,
email VARCHAR2(50),
--check约束，显式的给一个约束名
salary NUMBER(15, 2) CONSTRAINT emp16_salary_ck CHECK(salary > 1980 AND salary < 300000),
--直接在列后面写外键约束，如下:
department_id NUMBER(10) REFERENCES departments(department_id) , --系统会给一个默认的约束名
 
CONSTRAINT emp16_email_uk UNIQUE(email), 
CONSTRAINT emp16_id_pk PRIMARY KEY(ID)

--CONSTRAINT emp16_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)

--
SELECT * 
FROM emp16
--向emp16表中插入部门为800的数据，插入失败，违反外键约束，departments表中没有800号部门
INSERT INTO emp16
VALUES(1002, '杨过', 'yangguo@163.com', 5726.39, 800)


--列级外键约束
CREATE TABLE emp17(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp17_name_nn NOT NULL,
email VARCHAR2(50),
--check约束，显式的给一个约束名
salary NUMBER(15, 2) CONSTRAINT emp17_salary_ck CHECK(salary > 1980 AND salary < 300000),
--直接在列后面写外键约束，如下:
department_id NUMBER(10) CONSTRAINT emp17_department_id_fk REFERENCES departments(department_id), --显式的取一个约束名
 
CONSTRAINT emp17_email_uk UNIQUE(email), 
CONSTRAINT emp17_id_pk PRIMARY KEY(ID)

--CONSTRAINT emp17_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)
--
SELECT * 
FROM emp17

--查询前10条数据
SELECT *
FROM employees
WHERE ROWNUM <= 10
--查询第10到第20条纪录
SELECT *
FROM (
SELECT emp.*, ROWNUM rn
FROM employees emp
)
WHERE rn >= 10 AND rn <= 20

/*

添加约束
使用 ALTER TABLE 语句
添加或删除约束,但是不能修改约束
添加 NOT NULL 约束要使用 MODIFY 语句

*/

--添加约束
ALTER TABLE emp17
MODIFY(salary NUMBER(15, 2) NOT NULL)
--删除约束
ALTER TABLE emp17
DROP CONSTRAINT emp17_name_nn
--删除约束
ALTER TABLE emp17
DROP CONSTRAINT sys_c0012511
--添加约束
ALTER TABLE emp17
ADD CONSTRAINT emp17_name_uk unique(NAME)

--
SELECT * 
FROM emp12

--将约束无效化
ALTER TABLE emp12
DISABLE CONSTRAINT emp12_email_uk
--向emp12表中插入数据
INSERT INTO emp12
VALUES(106, 'DD', 'CC@163.com', 26000.58, 10)

--将约束有效化(激活约束)
ALTER TABLE emp12
ENABLE CONSTRAINT emp12_email_uk

--
UPDATE emp12
SET email = 'dd@qq.com'
WHERE ID = 106

--查询某表中的约束
SELECT const.CONSTRAINT_NAME, const.CONSTRAINT_TYPE, const.SEARCH_CONDITION 
FROM user_constraints const
WHERE const.TABLE_NAME = 'EMPLOYEES'

--查询定义约束的列
SELECT	constraint_name, column_name
FROM	user_cons_columns
WHERE	table_name = 'EMPLOYEES'

/*
      总结
      
      约束类型
      1.not null
      2.unique
      3.primary key
      4.foreign key
      5.check
      
      1.NOT NULL (注意:非空约束只能定义在列上)

      2.UNIQUE 

      3.PRIMARY KEY

      4.FOREIGN KEY

      5.CHECK
      
      57. 定义非空约束

	1). 非空约束只能定义在列级.
	
	2). 不指定约束名
	create table emp2 (
	name varchar2(30) not null, 
	age number(3)
	);
	
	3). 指定约束名	
	create table emp3(
	name varchar2(30) constraint name_not_null not null, 
	age number(3));
	
58. 唯一约束
	1). 列级定义
		
		①. 不指定约束名
		create table emp2 (
		name varchar2(30) unique, 
		age number(3)
		);
		
		②. 指定约束名
		create table emp3 (
		name varchar2(30) constraint name_uq unique, 
		age number(3)
		);
		
	2). 表级定义: 必须指定约束名
		①. 指定约束名
		create table emp3 (
		name varchar2(30), 
		age number(3), 
		constraint name_uq unique(name)
		);

58.1 主键约束：唯一确定一行记录。表明此属性：非空，唯一 
		
59. 外键约束
	1). 列级定义
		
		①. 不指定约束名
		create table emp2(
		       emp_id number(6), 
		       name varchar2(25), 
		       dept_id number(4) references dept2(dept_id))
		
		②. 指定约束名
		create table emp3(
		       emp_id number(6), 
		       name varchar2(25), 
		       dept_id number(4) constraint dept_fk3 references dept2(dept_id))
		
	2). 表级定义: 必须指定约束名

		①. 指定约束名
		create table emp4(
		       emp_id number(6), 
		       name varchar2(25), 
		       dept_id number(4),
		       constraint dept_fk2 foreign key(dept_id) references dept2(dept_id))
	
60. 约束需要注意的地方

	1). ** 非空约束(not null)只能定义在列级

	2). ** 唯一约束(unique)的列值可以为空

	3). ** 外键(foreign key)引用的列起码要有一个唯一约束		
	
61. 建立外键约束时的级联删除问题:
	1). 级联删除:
	
	create table emp2(
	       id number(3) primary key, 
	       name varchar2(25) unique, 
	       dept_id number(3) references dept2(dept_id) on delete cascade)
	
	2). 级联置空
	
	create table emp3(
	       id number(3) primary key, 
	       name varchar2(25) unique, 
	       dept_id number(3) references dept2(dept_id) on delete set null)

*/


/*

练习题

*/

--准备工作
create table emp18 
as select employee_id id, last_name name, salary 
from employees
--
create table dept18 
as select department_id id, department_name dept_name 
from departments
--

--向表emp18的id列中添加PRIMARY KEY约束(emp18_id_pk)
ALTER TABLE emp18
ADD CONSTRAINT emp18_id_pk PRIMARY KEY(id)


--2.向表dept18的id列中添加PRIMARY KEY约束(dept18_id_pk)
ALTER TABLE dept18
ADD CONSTRAINT dept18_id_pk PRIMARY KEY(id)


--3.向表emp18中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept18表中的id列
ALTER TABLE emp18
ADD (dept_id NUMBER(10) CONSTRAINT emp18_dept_id_fk REFERENCES dept18(id))

--删除约束
ALTER TABLE emp18
DROP CONSTRAINT emp18_dept_id_fk

--删除列
ALTER TABLE emp18
DROP COLUMN dept_id

--知识点拓展一下(增加级联删除)
ALTER TABLE emp18
ADD (
dept_id NUMBER(10) CONSTRAINT emp18_dept_id_fk REFERENCES dept18(id) ON DELETE CASCADE
)

--删除列
ALTER TABLE emp18
DROP COLUMN dept_id

--知识点拓展一下(增加级联置空)
ALTER TABLE emp18
ADD (
dept_id NUMBER(10) CONSTRAINT emp18_dept_id_fk REFERENCES dept18(id) ON DELETE SET NULL
)

/*

回顾一下以前的知识点/注意点

增加一列:
alter table 表名 add 列名 数据类型(长度)  例句 alter table emp4 add test varchar2(10)


修改一列：
alter table 表名 modify 列名 数据类型(长度)  例句 alter table emp4 modify test varchar2(20)


删除一列：
alter table 表名 drop column 列名    例句 alter table emp4 drop column test

这里要注意几个地方，首先，增加和修改列是不需要加关键字COLUMN，否则会报错
其次，对删除单列的话，一定要加COLUMN，然后记住，删除是不需要加列类型的


增加多列：
alter table 表名 add (列名 数据类型(长度),列名 数据类型) 例句 alter table emp4 add (test varchar2(10),test2 number)

修改多列：  
alter table 表名 modify (列名 数据类型(长度),列名 数据类型(长度)) 例句 alter table emp4 modify (test varchar2(20),test2 varchar2(20))


删除多列：
alter table 表名 drop (列名1, 列名2) 例句 alter table emp4 drop (test, test2)

删除单列时要加关键字COLUMN，然而在删除多列的时候，不能加COLUMN关键字

*/

/*

约束

5种约束

1.NOT NULL (注意:非空约束只能定义在列上)

2.UNIQUE

3.PRIMARY KEY

4.FOREIGN KEY

5.CHECK

*/
--
/*

创建和修改/维护约束

*/
--建表的同时创建约束
CREATE TABLE emp20180924(
/*
创建非空约束(显示的给这个非空约束取个约束名，为了规范和方便阅读和维护，约
束名最好是这种格式，表名_列名_约束类型,如emp20180924_id_nn)
*/
ID NUMBER(10) CONSTRAINT  emp20180924_id_notnull NOT NULL,
--创建非空约束(没有给这个非空约束取约束名，系统会自动的给个默认的约束名，但是我还是建议大家自己取一个规范的约束名)
NAME VARCHAR2(30) NOT NULL,
salary NUMBER(15, 2)
)
--查询emp20180924表的数据
SELECT *
FROM emp20180924
--插入数据
INSERT INTO emp20180924
VALUES(10001, '令狐冲', NULL)
--插入数据
INSERT INTO emp20180924
VALUES(10001, '令狐冲', 7200.56)

/*

注意：UNIQUE唯一约束是可以插入null空值的，并且每条记录都可以插入null空值，这并不违反UNIQUE唯一约束

*/
--创建表
CREATE TABLE emp8(
--唯一约束
ID NUMBER(12) CONSTRAINT emp8_id_uk UNIQUE, --列级约束
--非空约束
NAME varchar2(30) CONSTRAINT emp8_name_nn NOT NULL, --列级约束
email VARCHAR2(50),
salary NUMBER(15, 2),
--表级约束
CONSTRAINT emp8_email_uk UNIQUE(email) --指明该约束作用于email这一列上
)
--查询emp8表数据
SELECT *
FROM emp8
--往emp8表中插入数据
INSERT INTO emp8
VALUES(1002, '韦小宝', 'weixiaobao@qq.com', NULL)

--
/*
注意：
email这一列定义了UNIQUE唯一约束
如下：插入了2条数据，插入这2条数据时，email都是插入的null值,数据都能正常插入，说明
了，多条记录都插入null值，并不会违反UNIQUE唯一约束
*/
--往emp8表中插入数据(UNIQUE唯一约束是可以插入null空值的，并且每条记录都可以插入null空值，这并不违反UNIQUE唯一约束)
INSERT INTO emp8
VALUES(1003, '令狐冲', NULL, 2500)
--往emp8表中插入数据(UNIQUE唯一约束是可以插入null空值的，并且每条记录都可以插入null空值，这并不违反UNIQUE唯一约束)
INSERT INTO emp8
VALUES(1004, '张无忌', NULL, 2900)

--
--创建表(主键约束)
CREATE TABLE emp9(
--主键约束
ID NUMBER(12) CONSTRAINT emp9_id_pk PRIMARY KEY, --列级约束
--非空约束
NAME varchar2(30) CONSTRAINT emp9_name_nn NOT NULL, --列级约束
email VARCHAR2(50),
salary NUMBER(15, 2),
--表级约束
CONSTRAINT emp9_email_uk UNIQUE(email) --指明该约束作用于email这一列上
)
--
SELECT *
FROM emp9
/*

PRIMARY KEY 主键约束 (主键约束：不能为null,并且唯一)

*/
--往emp9表中插入数据(插入数据失败)
INSERT INTO emp9
VALUES(NULL, '张无忌', 'zhangwuji@qq.com', NULL)
--往emp9表中插入数据
INSERT INTO emp9
VALUES(100, '郭靖', 'test@qq.com', NULL)
--往emp9表中插入数据(插入数据失败)
INSERT INTO emp9
VALUES(100, '杨过', 'yangguo@qq.com', 8050.67)
--往emp9表中插入数据(插入数据成功)
INSERT INTO emp9
VALUES(108, '双儿', 'shuanger@qq.com', 9865.32)


--创建表(主键约束)
CREATE TABLE emp10(
--主键约束
ID NUMBER(12),
--非空约束
NAME varchar2(30) CONSTRAINT emp10_name_nn NOT NULL, --列级约束
email VARCHAR2(50),
salary NUMBER(15, 2),
--表级约束
CONSTRAINT emp10_email_uk UNIQUE(email), --指明该约束作用于email这一列上
CONSTRAINT emp10_id_pk PRIMARY KEY(ID)
)
--NAME列已经定义了非空约束，我这里又定义了name列的默认值为null
ALTER TABLE emp10
MODIFY (NAME VARCHAR2(30) DEFAULT NULL);
--
SELECT *
FROM emp10
--插入数据失败，插入数据报错
INSERT INTO emp10 (id, email, salary)
VALUES(10, 'test@qq.com', 8500.66)
--插入数据成功
INSERT INTO emp10 (id, name, email, salary)
VALUES(12, '张三', 'test@qq.com', 8500.66)


--
/*

外键约束

*/

--创建表(外键约束)
CREATE TABLE emp11(
--主键约束
ID NUMBER(12),
--非空约束
NAME varchar2(30) CONSTRAINT emp11_name_nn NOT NULL, --列级约束
email VARCHAR2(50),
salary NUMBER(15, 2),
department_id NUMBER(10),
--表级约束
CONSTRAINT emp11_email_uk UNIQUE(email), --指明该约束作用于email这一列上
CONSTRAINT emp11_id_pk PRIMARY KEY(ID),
--外键约束
CONSTRAINT emp11_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)
--
SELECT *
FROM emp11
--
SELECT *
FROM departments
--插入数据成功(因为departments表中的department_id列中有10这个值，也就是说departments表中有10号部门)
INSERT INTO emp11
VALUES(100, 'AA', 'aa@q63.com', 9650.55, 10)
--违反了主外键约束，插入数据失败(因为departments表中的department_id列中没有680这个值，也就是说departments表中没有680号部门)
INSERT INTO emp11
VALUES(101, 'CC', 'cc@q63.com', 8265.36, 680)

/*

约束命令规范
约束名称建议自己定义一套命名规则，否则使用系统生成的约束名，很难能把它和对应的表、字段联系起来

        非空约束 NN_表名_列名

唯一约束 UK_表名_列名

主键约束 PK_表名

外键约束 FK_表名_列名

条件约束 CK_表名_列名

默认约束 DF_表名_列名

*/

/*

级联删除 ON DELETE CASCADE

级联置空 ON DELETE SET NULL

*/

--级联置空
CREATE TABLE emp12(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp12_name_nn NOT NULL,
email VARCHAR2(50),
salary NUMBER(15, 2),
department_id NUMBER(10),
 
CONSTRAINT emp12_email_uk UNIQUE(email),
CONSTRAINT emp12_id_pk PRIMARY KEY(ID),
--级联置空
CONSTRAINT emp12_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ON DELETE SET NULL
)
--
SELECT *
FROM emp12
--
SELECT *
FROM departments
--
INSERT INTO emp12
VALUES(103, 'AA', 'AA@qq.com', 6790.26, 260)

--
INSERT INTO emp12
VALUES(104, 'BB', 'BB@126.com', 5186.68, 260)
--
INSERT INTO emp12
VALUES(105, 'CC', 'CC@163.com', 12680.91, 270)
--从departments表中删除260号部门 (测试级联置空)
DELETE FROM departments dep
WHERE dep.department_id = 260

--

--级联删除
CREATE TABLE emp13(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp13_name_nn NOT NULL,
email VARCHAR2(50),
salary NUMBER(15, 2),
department_id NUMBER(10),
 
CONSTRAINT emp13_email_uk UNIQUE(email),
CONSTRAINT emp13_id_pk PRIMARY KEY(ID),
--级联删除
CONSTRAINT emp13_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ON DELETE CASCADE
)
--
SELECT *
FROM emp13
--
SELECT *
FROM departments
--
INSERT INTO emp13
VALUES(108, 'AA', 'AA@qq.com', 6790.26, 270)

--
INSERT INTO emp13
VALUES(109, 'BB', 'BB@126.com', 5186.68, 270)
--
INSERT INTO emp13
VALUES(110, 'CC', 'CC@163.com', 12680.91, 240)
--从departments表中删除270号部门 (测试级联删除)
DELETE FROM departments dep
WHERE dep.department_id = 270
--

/*

check约束

*/

CREATE TABLE emp14(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp14_name_nn NOT NULL,
email VARCHAR2(50),
--check约束，系统会给一个默认的约束名
salary NUMBER(15, 2) CHECK(salary > 1980 AND salary < 300000),
department_id NUMBER(10),
 
CONSTRAINT emp14_email_uk UNIQUE(email),
CONSTRAINT emp14_id_pk PRIMARY KEY(ID),

CONSTRAINT emp14_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)
--
SELECT *
FROM emp14
--插入一条符合check约束的数据
INSERT INTO emp14
VALUES(101, '令狐冲', 'linghuchong@163.com', 6790.55, 10)
--插入一条不符合check约束的数据(插入失败，违反check约束)
INSERT INTO emp14
VALUES(101, '令狐冲', 'linghuchong@163.com', 860.89, 10)

--check约束，显式的给一个约束名
CREATE TABLE emp15(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp15_name_nn NOT NULL,
email VARCHAR2(50),
--check约束，显式的给一个约束名
salary NUMBER(15, 2) CONSTRAINT emp15_salary_ck CHECK(salary > 1980 AND salary < 300000),
department_id NUMBER(10),
 
CONSTRAINT emp15_email_uk UNIQUE(email),
CONSTRAINT emp15_id_pk PRIMARY KEY(ID),

CONSTRAINT emp15_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)
--
SELECT *
FROM emp15

--列级外键约束
CREATE TABLE emp16(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp16_name_nn NOT NULL,
email VARCHAR2(50),
--check约束，显式的给一个约束名
salary NUMBER(15, 2) CONSTRAINT emp16_salary_ck CHECK(salary > 1980 AND salary < 300000),
--直接在列后面写外键约束，如下:
department_id NUMBER(10) REFERENCES departments(department_id) , --系统会给一个默认的约束名
 
CONSTRAINT emp16_email_uk UNIQUE(email),
CONSTRAINT emp16_id_pk PRIMARY KEY(ID)

--CONSTRAINT emp16_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)

--
SELECT *
FROM emp16
--向emp16表中插入部门为800的数据，插入失败，违反外键约束，departments表中没有800号部门
INSERT INTO emp16
VALUES(1002, '杨过', 'yangguo@163.com', 5726.39, 800)


--列级外键约束
CREATE TABLE emp17(

ID NUMBER(12),

NAME varchar2(30) CONSTRAINT emp17_name_nn NOT NULL,
email VARCHAR2(50),
--check约束，显式的给一个约束名
salary NUMBER(15, 2) CONSTRAINT emp17_salary_ck CHECK(salary > 1980 AND salary < 300000),
--直接在列后面写外键约束，如下:
department_id NUMBER(10) CONSTRAINT emp17_department_id_fk REFERENCES departments(department_id), --显式的取一个约束名
 
CONSTRAINT emp17_email_uk UNIQUE(email),
CONSTRAINT emp17_id_pk PRIMARY KEY(ID)

--CONSTRAINT emp17_department_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)
--
SELECT *
FROM emp17

--查询前10条数据
SELECT *
FROM employees
WHERE ROWNUM <= 10
--查询第10到第20条纪录
SELECT *
FROM (
SELECT emp.*, ROWNUM rn
FROM employees emp
)
WHERE rn >= 10 AND rn <= 20

/*

添加约束
使用 ALTER TABLE 语句
添加或删除约束,但是不能修改约束
添加 NOT NULL 约束要使用 MODIFY 语句

*/

--添加约束
ALTER TABLE emp17
MODIFY(salary NUMBER(15, 2) NOT NULL)
--删除约束
ALTER TABLE emp17
DROP CONSTRAINT emp17_name_nn
--删除约束
ALTER TABLE emp17
DROP CONSTRAINT sys_c0012511
--添加约束
ALTER TABLE emp17
ADD CONSTRAINT emp17_name_uk unique(NAME)

--
SELECT *
FROM emp12

--将约束无效化
ALTER TABLE emp12
DISABLE CONSTRAINT emp12_email_uk
--向emp12表中插入数据
INSERT INTO emp12
VALUES(106, 'DD', 'CC@163.com', 26000.58, 10)

--将约束有效化(激活约束)
ALTER TABLE emp12
ENABLE CONSTRAINT emp12_email_uk

--
UPDATE emp12
SET email = 'dd@qq.com'
WHERE ID = 106

--查询某表中的约束
SELECT const.CONSTRAINT_NAME, const.CONSTRAINT_TYPE, const.SEARCH_CONDITION
FROM user_constraints const
WHERE const.TABLE_NAME = 'EMPLOYEES'

--查询定义约束的列
SELECT    constraint_name, column_name
FROM    user_cons_columns
WHERE    table_name = 'EMPLOYEES'

/*
      总结
      
      约束类型
      1.not null
      2.unique
      3.primary key
      4.foreign key
      5.check
      
      1.NOT NULL (注意:非空约束只能定义在列上)

      2.UNIQUE

      3.PRIMARY KEY

      4.FOREIGN KEY

      5.CHECK
      
      57. 定义非空约束

    1). 非空约束只能定义在列级.
    
    2). 不指定约束名
    create table emp2 (
    name varchar2(30) not null,
    age number(3)
    );
    
    3). 指定约束名    
    create table emp3(
    name varchar2(30) constraint name_not_null not null,
    age number(3));
    
58. 唯一约束
    1). 列级定义
        
        ①. 不指定约束名
        create table emp2 (
        name varchar2(30) unique,
        age number(3)
        );
        
        ②. 指定约束名
        create table emp3 (
        name varchar2(30) constraint name_uq unique,
        age number(3)
        );
        
    2). 表级定义: 必须指定约束名
        ①. 指定约束名
        create table emp3 (
        name varchar2(30),
        age number(3),
        constraint name_uq unique(name)
        );

58.1 主键约束：唯一确定一行记录。表明此属性：非空，唯一
        
59. 外键约束
    1). 列级定义
        
        ①. 不指定约束名
        create table emp2(
               emp_id number(6),
               name varchar2(25),
               dept_id number(4) references dept2(dept_id))
        
        ②. 指定约束名
        create table emp3(
               emp_id number(6),
               name varchar2(25),
               dept_id number(4) constraint dept_fk3 references dept2(dept_id))
        
    2). 表级定义: 必须指定约束名

        ①. 指定约束名
        create table emp4(
               emp_id number(6),
               name varchar2(25),
               dept_id number(4),
               constraint dept_fk2 foreign key(dept_id) references dept2(dept_id))
    
60. 约束需要注意的地方

    1). ** 非空约束(not null)只能定义在列级

    2). ** 唯一约束(unique)的列值可以为空

    3). ** 外键(foreign key)引用的列起码要有一个唯一约束        
    
61. 建立外键约束时的级联删除问题:
    1). 级联删除:
    
    create table emp2(
           id number(3) primary key,
           name varchar2(25) unique,
           dept_id number(3) references dept2(dept_id) on delete cascade)
    
    2). 级联置空
    
    create table emp3(
           id number(3) primary key,
           name varchar2(25) unique,
           dept_id number(3) references dept2(dept_id) on delete set null)

*/


/*

练习题

*/

--准备工作
create table emp18
as select employee_id id, last_name name, salary
from employees
--
create table dept18
as select department_id id, department_name dept_name
from departments
--

--向表emp18的id列中添加PRIMARY KEY约束(emp18_id_pk)
ALTER TABLE emp18
ADD CONSTRAINT emp18_id_pk PRIMARY KEY(id)


--2.向表dept18的id列中添加PRIMARY KEY约束(dept18_id_pk)
ALTER TABLE dept18
ADD CONSTRAINT dept18_id_pk PRIMARY KEY(id)


--3.向表emp18中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept18表中的id列
ALTER TABLE emp18
ADD (dept_id NUMBER(10) CONSTRAINT emp18_dept_id_fk REFERENCES dept18(id))

--删除约束
ALTER TABLE emp18
DROP CONSTRAINT emp18_dept_id_fk

--删除列
ALTER TABLE emp18
DROP COLUMN dept_id

--知识点拓展一下(增加级联删除)
ALTER TABLE emp18
ADD (
dept_id NUMBER(10) CONSTRAINT emp18_dept_id_fk REFERENCES dept18(id) ON DELETE CASCADE
)

--删除列
ALTER TABLE emp18
DROP COLUMN dept_id

--知识点拓展一下(增加级联置空)
ALTER TABLE emp18
ADD (
dept_id NUMBER(10) CONSTRAINT emp18_dept_id_fk REFERENCES dept18(id) ON DELETE SET NULL
)

/*

回顾一下以前的知识点/注意点

增加一列:
alter table 表名 add 列名 数据类型(长度)  例句 alter table emp4 add test varchar2(10)


修改一列：
alter table 表名 modify 列名 数据类型(长度)  例句 alter table emp4 modify test varchar2(20)


删除一列：
alter table 表名 drop column 列名    例句 alter table emp4 drop column test

这里要注意几个地方，首先，增加和修改列是不需要加关键字COLUMN，否则会报错
其次，对删除单列的话，一定要加COLUMN，然后记住，删除是不需要加列类型的


增加多列：
alter table 表名 add (列名 数据类型(长度),列名 数据类型) 例句 alter table emp4 add (test varchar2(10),test2 number)

修改多列：  
alter table 表名 modify (列名 数据类型(长度),列名 数据类型(长度)) 例句 alter table emp4 modify (test varchar2(20),test2 varchar2(20))


删除多列：
alter table 表名 drop (列名1, 列名2) 例句 alter table emp4 drop (test, test2)

删除单列时要加关键字COLUMN，然而在删除多列的时候，不能加COLUMN关键字

*/

程宇寒
关注
Oracle的五种约束
wu的BLOG的博客
11-06 813
Oracle的五种约束 1.非空（NOT NULL）约束:所定义的列不绝对不能为空； 例如：将已经创建好的表BOOK中的bookname字段修改为不为空： 利用 ALTER TABLE…MODIFY …NOT NULL; ALTER TABLE BOOK MODIFY bookname NOT NULL; 2.主键(PRIMARY KEY)约束:唯一的标识表中的每一行； 例如：为已经创建好的表b...
Oracle定义约束 外键约束
cklu75008的博客
01-24 160
转自：http://oracle.**.com 外键约束保证参照完整性。外键约束限定了一个列的取值范围。一个例子就是限定州名缩写在一个有限值集合中，这个值集合是另外一个控制结构——一张父表 下面我们创建一张参照表，它...
Oracle约束
yyjacn的专栏
05-27 1217
约束的种类 1. 非空约束（不允许值为空） 2. 唯一约束（通过自动添加索引或利用现有索引来实现） 3. 主键约束（通过自动添加或利用现有索引和添加非空约束来实现） 4. 外键约束（也叫“引用完整性约束”，或简称“引用约束”） 5. 检查约束（为列给出写入值的条件，满足的才允许） 无约束的建表例子 create table test_t1 ( id number, create_time timestamp, name varchar2(20) ); 在建表时指定约束 1. 创建表在指定列的同时指定
Oracle中的约束
Rushinger
08-04 290
文章目录1.概况2.常见约束类型3. 约束分类3.1 列级约束3.2 表级约束4.外键4.1 级联删除4.2 级联置空5.追加约束5.1 唯一、主键、检查、外键约束5.2 默认，非空6.删除约束7.完整性约束 1.概况 对数据的限制条件： 数据类型 2.常见约束类型 检查约束（check）： name > 4 限制长度 唯一约束（Unique）： id:1 2 3 4 null 某个字段字段只能是一个值 主键约束（Primary key）： 类
Oracle约束管理脚本
12-16
正在看的ORACLE教程是:Oracle约束管理脚本。作为一个Oracle数据库管理员，会碰到这样的数据库管理需求，停止或者打开当前用户（模式）下所有表的约束条件和触发器。这在数据库的合并以及对数据库系统的代码表中某些...
oracle约束键解析.docx
05-03
Oracle约束键解析主要涉及到数据库设计中的关键概念，用于确保数据的完整性和一致性。本文将详细介绍四种主要的约束类型：主键约束（Primary Key）、唯一约束（Unique）、外键约束（Foreign Key）和检查约束（Check...
09oracle约束.pptx
09-20
Oracle约束是数据库管理系统中用于确保数据完整性的重要机制。在Oracle数据库中，约束主要分为三类：域完整性、实体完整性和参照完整性。 1. 域完整性（Domain Integrity）： 域完整性关注的是单个列的数据有效性...
[Oracle]约束（constraint）
weixin_30312563的博客
07-23 161
(一)约束的概念 在Oracle中，可以通过设置约束来防止无效数据进入表中。Oracle一共有5种约束： 主键约束（primary key） 外键约束（foreign key） 唯一性约束（unique） 非空约束（not null） 检查约束（check） （1）主键约束 --主键约束可以定义在一列或多列上，值具有唯一性、非空性； --在一个表上只能定义一个主键约束； --Oracle...
oracle-约束
故园
05-23 1156
1.定义： 约束是强加在表上的规则或条件。 2.约束的功能： 实现一些业务规则，防止无效的垃圾数据进入数据库。 维护数据库的完整性(完整性指正确性与一致性)，从而使数据库的开发和维护都更加容易。 3.约束的分类： ①not mull 非空约束 ②unique 唯一约束 ③primary key 主键 ④foreign key 外键 ⑤check 定义一般性约束 4.语法 增加：alter ta...
Oracle的约束详解
weixin_42402597的博客
07-25 5208
约束：///就是强加在表中的规则或者条件，作用让表中的数据更加符合实际要求 constraint 约束的分类 一、按照约束的效果不同分为六种 1.唯一约束 unique 作用：字段中不能出现重复的非空数据，，，///不能添加两个张三但无法限制空值 2.非空约束 not null 作用：该字段中不能出现空值 3.主键约束 primary key 作用：该字段中不能出现空值，也不能出现重复值 4.外键约束 foreign key 作用：字段的取值范围来自另一张表的某字段（某..
Oracle 约束的定义
tiegenZ的博客
03-13 991
约束用于确保数据库满足特定的商业逻辑或者企业规划
索引常用命令
我爱海
04-08 1158
/*给创建bitmap index分配的内存空间参数，以加速建索引*/show parameter create_bit;/*改变索引的存储参数*/alter index index_name pctfree 30 storage(initial 200k next 200k);/*给索引手工分配一个分区*/alter index index_name allocate ext
oracle之约束
qq_44942996的博客
03-08 390
查看表约束 select * from all_constraints a, all_cons_columns b where a.constraint_type='p' and c.constraint_name=b.constraint_name and a.owner=b.owner and a.table_name='tb1'; 删除表约束 alter table tb1 drop constraint 约束名; 添加约束 alter table tb1 add constraint 约束
Oracle约束操作
u014066037的博客
08-10 1074
约束是在表中定义的用于维护数据库完整性的一些规则。通过为表中的字段定义约 束，可以防止将错误的数据插入到表中。 注意： 1.如果某个约束只作用于单独的字段，既可以在字段级定义约束，也可以在表级定义约束；但如果某个约束将作用于多个字段，必须在表级定义约束。 2.oracle中的约束通过名称来进行识别。在定义约束时可以通过constraint关键字为约束命名。如果用户没有为约束指定名称，o
oracle 约束
最新发布
05-11
Oracle数据库中的约束是用来保证数据完整性和一致性的重要手段。常见的约束有五种：主键约束、唯一约束、非空约束、外键约束和检查约束。 1. 主键约束（primary key）：用于唯一标识一条记录，一个表只能有一个主键...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值