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

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';