Oracle培训（三十）——Oracle 11g 第十章知识点总结——约束

gwi3494pm5

Oracle培训（三十）——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';