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

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