在表上建立不同類型的約束
掌握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);
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
