在表上建立不同類型的約束

掌握oracle提供的五種約束

l? Not null

l? 唯一約束 UNIQUE

l? Check 檢測約束

l? Primary key 主健約束

l? foreign key 外健

?

1、 Not null

?

定義在表的列上，表明該列必須要有值，不能為null

可以在建立表的時候說明

也可以在表建立后修改為not null

可以給約束指定名稱。

如果不指定名稱，數據庫會給一個系統自動指定名稱，SYS_C#######

User_constraints,user_cons_columns可以查看到約束的信息

建立表的時候指定not null約束

connect scott/tiger

drop table t1 purge;

create table t1 (name char(9) not null,

telenum char(8) constraint t1_tele_nl not null);

建立表的時候指明非空約束，一個是系統命名，一個是我們命名。

select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name

from user_constraints natural join user_cons_columns;

CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME

------------------------------ - ------------------------------ -----------

PK_DEPT P DEPT DEPTNO

FK_DEPTNO R EMP DEPTNO

PK_EMP P EMP EMPNO

T1_TELE_NL C T1 TELENUM

SYS_C005978 C T1 NAME

我們通過數據字典來驗證我們的實驗結果。

建立表后指定not null約束。要使用modify語法。你不指定名稱，數據庫自己命名。

connect scott/tiger

drop table t1 purge;

create table t1 as select * from dept;

alter table t1 modify(dname not null);

?

2、 唯一約束 UNIQUE

?

列的值不能重復

可以為NULL

是用索引來維護唯一的

索引的名稱和約束的名稱相同

建立表的時候指定UNIQUE約束

connect scott/tiger

drop table t1 PURGE;

create table t1 (name char(9) UNIQUE,

mail char(8) constraint t1_mail_u UNIQUE);

建立表的時候指明唯一約束，一個是系統命名，一個是我們命名。

select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name

from user_constraints natural join user_cons_columns;

CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME

------------------------------ - ------------------------------ -----------

PK_DEPT P DEPT DEPTNO

FK_DEPTNO R EMP DEPTNO

PK_EMP P EMP EMPNO

SYS_C005980 U T1 NAME

T1_MAIL_U U T1 MAIL

建立表之后指定UNIQUE約束

connect scott/tiger

drop table t1 purge;

create table t1 as select * from dept;

alter table t1 add constraint u_dname unique (dname);

select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name

from user_constraints natural join user_cons_columns;

CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME

------------------------------ - ------------------------------ -----------

PK_DEPT P DEPT DEPTNO

FK_DEPTNO R EMP DEPTNO

PK_EMP P EMP EMPNO

U_DNAME U T1 DNAME

Select table_name,index_name,column_name from user_ind_columns;

TABLE_NAME INDEX_NAME COLUMN_NAME

------------------------------ ------------------------------ -----------

DEPT PK_DEPT DEPTNO

EMP PK_EMP EMPNO

T1 U_DNAME DNAME

?

3、 Check 檢測約束

?

Drop table t1 purge;

Create table t1(name varchar2(8)

check (length(name)>4),

Mail varchar2(10) );

alter table t1 add constraint check_mail check (length(mail)>4);

insert into t1 (name,mail)values('abc','sdsfs');

ERROR at line 1:

ORA-02290: check constraint (SCOTT.SYS_C005983) violated

insert into t1 (name,mail)values('abcsdfs','sds');

ERROR at line 1:

ORA-02290: check constraint (SCOTT.CHECK_MAIL) violated

?

4、 Primary key 主健約束

?

一個表只能有一個主健

主健要求唯一并且非空

可以是聯合主健，聯合主健每列都要求非空

主健能唯一定位一行,所以主鍵也叫邏輯rowid

主健不是必需的，可以沒有

主健是通過索引實現的

索引的名稱和主健名稱相同

建立表的時候指定主健，系統命名

drop table t1 purge;

create table t1(mail char(8) primary key,name char(8));

select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name

from user_constraints natural join user_cons_columns;

CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME

------------------------------ - ------------------------------ -----------

PK_DEPT P DEPT DEPTNO

FK_DEPTNO R EMP DEPTNO

PK_EMP P EMP EMPNO

SYS_C005985 P T1 MAIL

Select table_name,index_name,column_name from user_ind_columns;

TABLE_NAME INDEX_NAME COLUMN_NAME

------------------------------ ------------------------------ -----------

DEPT PK_DEPT DEPTNO

EMP PK_EMP EMPNO

T1 SYS_C005985 MAIL

表建立后指定自命名的主健

drop table t1 purge;

create table t1(mail char(8) ,name char(8));

alter table t1 add constraint pk_t1_mail primary key (mail) ;

select CONSTRAINT_NAME,CONSTRAINT_type,TABLE_NAME,column_name

from user_constraints natural join user_cons_columns;

CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME

------------------------------ - ------------------------------ -----------

PK_DEPT P DEPT DEPTNO

FK_DEPTNO R EMP DEPTNO

PK_EMP P EMP EMPNO

PK_T1_MAIL P T1 MAIL

Select table_name,index_name,column_name from user_ind_columns;

TABLE_NAME INDEX_NAME COLUMN_NAME

------------------------------ ------------------------------ -------------

DEPT PK_DEPT DEPTNO

EMP PK_EMP EMPNO

T1 PK_T1_MAIL MAIL

?

5、 foreign key 外健

?

指定在表的列上

引用本表其它列，或其它表的其它列

被引用的列得有唯一約束或者主健約束，因為引用的是索引的鍵值，而不是真正的表。

目的是維護數據的完整性

核心是一列是另外一列的子集，null除外

建立主健，建立一個外健來引用主健

conn scott/tiger

drop table e purge;

drop table d purge;

create table d as select * from dept;

create table e as select * from emp;

alter table d add constraint pk_d primary key (deptNO) ;

alter table e add constraint fk_e foreign key (deptno) references d(deptno) ;

違反約束

delete d where deptno=10

ORA-02292: 違反完整約束條件 (SCOTT.FK_E) - 已找到子記錄

update e set deptno=50

ORA-02291: 違反完整約束條件 (SCOTT.FK_E) - 未找到父項關鍵字

建立被級連的外健

alter table e drop constraint FK_E;

alter table e add constraint fk_e foreign key (deptno)

references d(deptno) on delete set null;

父表的值被刪除，子表的相關列自動被賦予NULL值。

alter table e drop constraint FK_E;

alter table e add constraint fk_e foreign key (deptno)

references d(deptno) on delete cascade;

父表的值被刪除，子表的相關行自動被刪除。

?

6、 ? 刪除約束

?

任何約束都可以用約束名稱來刪除

Alter table ### drop constraint ****;

因為主健只能有一個，所以刪除主健約束的時候也可以

Alter table ### drop primary key;

如果主健和唯一性約束被刪除，自動建立的索引也會同時被清除。

Not Null約束也可以用alter table modify來刪除。

刪除有外健引用的主健或唯一約束的時候，外健也要被級連刪除。

Alter table ### drop primary key cascade;

如果不加cascade，你刪不了，報有外鍵在使用，不能刪除。

按約束的名稱來刪除約束（可以刪除各種約束）

Alter table t1 drop constraint sys_c03033;

非空約束的第二種刪除方式

alter table t1 modify (name not null);

alter table t1 modify (name null);

?

學習Oracle數據庫（4）在表上建立不同類型的約束