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

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