Oracle11g第十章知识点总结约束

Oracle 11g 第十章知识点总结——约束 知识点预览 复习 约束 复习 --主键primarykey--(非空唯一) --唯一能够标识表里的一条记录--非空 not null--唯一性约束unique--检查约束 check(gender in('男','女')) --外键 --当前表里的这个外键是另外一张表里的主键

Oracle 11g 第十章知识点总结——约束

知识点预览

复习

约束

复习

--主键
primarykey
--(非空+唯一)
 --唯一能够标识表里的一条记录
--非空
 not null
--唯一性约束
unique
--检查约束
 check(gender in('男','女'))
 
--外键
 --当前表里的这个外键是另外一张表里的主键
 --必须和哪个主键的数据类型和长度保持一致
 
 
--删除表
 drop table table_name
 --产生乱码
 purge table "BIN$2RcRpY2KSLy+FONALkCBUw==$0";
 purge table t_tes;--错误
 
 
--DBA
 
 
--创建表
--分类表
--基表
 
 create tablecategory (
 idnumber(3) primary key,
 namevarchar2(20) not null
 );
--产品表
--从表
 
 create table product(
 idnumber(10) primary key,
 namevarchar2(20) not null,
 cidnumber(3),
 constraintscott_product_cid_FK foreign key(cid) references category(id)
 );
 
 insert into categoryvalues(1,'电脑');
 insert into productvalues(1,'IBM',1);
 
 insert into categoryvalues(2,'食品');
 insert into productvalues(2,'牛肉干',2);
 
--删除
--先删除从表数据
--后删主表数据
 delete category where id=1;
 

约束

1. 什么是约束

a) 约束是表级的强制规定

b) 约束放置在表中删除有关联关系的数据

c) 有以下五种约束:

i. NOT NULL

ii. UNIQUE

iii. PRIMARY KEY

iv. FOREIGN KEY

v. CHECK

2. 注意事项

a) 如果不指定约束名 Oracle server 自动按照 SYS_Cn 的格式指定约束名

b) 在什么时候创建约束:

i. 建表的同时

ii. 建表之后

c) 可以在表级或列级定义约束

d) 可以通过数据字典视图查看约束

3. 定义约束

a) 语法

CREATE TABLE[schema.]table

(columndatatype [DEFAULT expr]

[column_constraint],

...

[table_constraint][,...]);

CREATE TABLEemployees(
 employee_id NUMBER(6),
 first_name VARCHAR2(20),
 ...
 job_id VARCHAR2(10) NOT NULL,
 CONSTRAINT emp_emp_id_pk
 PRIMARYKEY (EMPLOYEE_ID));

b) 列级

column [CONSTRAINT constraint_name]constraint_type,

c) 列级

column,...

[CONSTRAINT constraint_name]constraint_type

(column, ...),

4. NOT NULL 约束

a) 保证列值不能为空:

b) 只能定义在列级:

5. UNIQUE 约束

可以定义在表级或列级:

CREATE TABLE employees(
 employee_id NUMBER(6),
 last_name VARCHAR2(25) NOT NULL,
 email VARCHAR2(25) ,
 salary NUMBER(8,2),
 commission_pct NUMBER(2,2),
 hire_date DATE NOT NULL,
... 
 CONSTRAINT emp_email_uk UNIQUE(email));

6. PRIMARY KEY 约束

可以定义在表级或列级:

CREA TETABLE departments(
 department_id NUMBER(4),
 department_name VARCHAR2(30)
 CONSTRAINT dept_name_nn NOT NULL,
 manager_id NUMBER(6),
 location_id NUMBER(4),
 CONSTRAINT dept_id_pk PRIMARYKEY(department_id));

7. FOREIGN KEY 约束

可以定义在表级或列级:

CREATE TABLEemployees(
 employee_id NUMBER(6),
 last_name VARCHAR2(25) NOT NULL,
 email VARCHAR2(25),
 salary NUMBER(8,2),
 commission_pct NUMBER(2,2),
 hire_date DATE NOT NULL,
...
 department_id NUMBER(4),
 CONSTRAINT emp_dept_fk FOREIGN KEY(department_id)
 REFERENCES departments(department_id),
 CONSTRAINT emp_email_uk UNIQUE(email));

8. FOREIGN KEY 约束的关键字

a) FOREIGN KEY: 在表级指定子表中的列

b) REFERENCES: 标示在父表中的列

c) ON DELETE CASCADE: 当父表中的列被删除是，子表中相对应的列也被删除

d) ON DELETE SET NULL: 子表中相应的列置空

9. CHECK 约束

a) 定义每一行必须满足的条件

b) 以下的表达式是不允许的:

i. 出现CURRVAL, NEXTVAL, LEVEL, 和ROWNUM 伪列

ii. 使用 SYSDATE, UID, USER, 和 USERENV 函数

iii. 在查询中涉及到其它列的值

..., salary NUMBER(2)
 CONSTRAINT emp_salary_min 
 CHECK (salary > 0),...

10. 添加约束的语法

使用 ALTER TABLE 语句:

添加或删除约束, 但是不能修改约束

有效化或无效化约束

添加 NOT NULL 约束要使用 MODIFY 语句

ALTER TABLE table

ADD [CONSTRAINT constraint]type (column);

11. 添加约束举例

ALTER TABLE employees
ADDCONSTRAINT emp_manager_fk
 FOREIGN KEY(manager_id)
 REFERENCES employees(employee_id);

Table altered.

12. 删除约束

a) 表 EMPLOYEES 中删除约束

ALTER TABLE employees
DROPCONSTRAINT emp_manager_fk;

Table altered.

b) 使用CASCADE选项删除约束

ALTER TABLE departments
DROP PRIMARY KEYCASCADE;

Table altered.

13. 无效化约束

a) 在ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。

b) 使用 CASCADE 选项将相关的约束也无效化

 
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;

Table altered.

14. 激活约束

a) ENABLE 子句可将当前无效的约束激活

ALTER TABLE employees
ENABLECONSTRAINT emp_emp_id_pk;

Table altered.

b) 当索引定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY

15. 及连约束

a) CASCADE CONSTRAINTS 子句在 DROP COLUMN 子句中使用

b) 在删除表的列时 CASCADE CONSTRAINTS 子句指定将相关的约束一起删除

c) 在删除表的列时 CASCADE CONSTRAINTS 子句同时也删除多列约束

d) 及连约束举例:

ALTER TABLEtest1
DROP (pk)CASCADE CONSTRAINTS;

Table altered.

ALTER TABLEtest1
DROP (pk, fk,col1) CASCADE CONSTRAINTS;

Table altered.

16. 查询约束

查询数据字典视图 USER_CONSTRAINTS

SELECT constraint_name, constraint_type,
search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

17. 查询定义约束的列

查询数据字典视图 USER_CONS_COLUMNS

SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';