SQL中的CONSTRAINT(限制)用法

主要就是增加資料限制的。

ORACLE支援五種類型的完整性限制

NOT NULL (非空)–防止NULL值進入指定的列,在單列基礎上定義,預設情況下,ORACLE允許在任何列中有NULL值.

CHECK (檢查)–檢查在限制中指定的條件是否得到了滿足.

UNIQUE (唯一)–保證在指定的列中沒有重複值.在該表中每一個值或者每一組值都将是唯一的.

PRIMARY KEY (主鍵)–用來唯一的辨別出表的每一行,并且防止出現NULL值,一個表隻能有一個主鍵限制.

POREIGN KEY (外部鍵)–通過使用公共列在表之間建立一種父子(parent-child)關系,在表上定義的外部鍵可以指向主鍵或者其他表的唯一鍵.

以下幾種限制 、并 一一列舉：

1.主鍵限制：

要對一個列加主鍵限制的話，這列就必須要滿足的條件就是分空

因為主鍵限制：就是對一個列進行了限制，限制為（非空、不重複）

以下是代碼 要對一個列加主鍵，列名為id,表名為emp

格式為：

alter table 表格名稱 add constraint 限制名稱 增加的限制類型 （列名）

例子：

alter table emp add constraint ppp primary key (id)

————————————————————————————————

2.check限制：

就是給一列的資料進行了限制

比方說，年齡列的資料都要大于20的

表名（emp） 列名（age）

格式：

alter table 表名稱 add constraint 限制名稱 增加的限制類型 （列名）

例子：

alter table emp add constraint xxx check(age>20)

3.unique限制：

這樣的限制就是給列的資料追加的不重複的限制類型

格式：

alter table 表名 add constraint 限制名稱 限制類型（列名）

比方說可以給ename列加個unique，讓ename列的資料不重複

例子：

alter table emp add constraint qwe unique(ename)

————————————————————————————————

4.預設限制：

意思很簡單就是讓此列的資料預設為一定的資料

格式：

alter table 表名稱 add constraint 限制名稱 限制類型 預設值） for 列名

比方說：emp表中的gongzi列預設為10000

alter table emp add constraint jfsd default 10000 for gongzi

————————————————————————————————

5.外鍵限制：

這個有點難了解了，外鍵其實就是引用

因為主鍵實作了實體的完整性，

外鍵實作了引用的完整性，

應用完整性規定，所引用的資料必須存在！

其實就是個引用，

比方說一個表名稱叫dept 裡面有2列資料 一列是ID一列是ENAME

id：表示産品的編号

ename：表示産品的名稱

另外一個表格名稱是emp 裡面有2列資料，一列是ID 一列是DID

id:表示使用者号

did：表示購買的産品号

要讓emp表中的did列去引用dept表中的id

可以用下面的方法

格式：

alter table 表名 add constraint 限制名稱 限制類型 (列名) references 被引用的表名稱 （列名）

例子：

alter table emp add constraint jfkdsj foreign key (did) references dept (id)

限制定義存儲在資料字典中,查詢USER_CONSTRAINTS可以獲得相關資訊.

定義限制

CREATE TABLE [schema.]table

(column datatype [DEFAULT expr]

[column_constraint],

…

[table_constraint][,…]);

e.g.

CREATE TABLE employees

(employee_id NUMBER(6),

first_name VARCHAR2(20),

…

job_id VARCHAR2(10) NOT NULL,

CONSTRAINTS emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));

列級的限制定義

column [CONSTRAINT constraint_name] constraint_type,

表級限制的定義

column,…

[CONSTRAINT constraint_name] constraint_type (column,…)

NOT NULL限制

隻能定義在列級,不能定義在表級.

e.g.

CREATE TABLE employees

(employee_id NUMBER(6),

last_name VARCHAR2(25) NOT NULL, //沒有指定名字,将用系統SYS_命名

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL); //使用者定義了名字

UNIQUE限制

用來保護一個表中的一個或者多個列沒有任何兩行在收到保護的列中具有重複的資料.ORACLE在唯一鍵列上自動生成一個唯一索引以實作唯一性

e.g.

CREATE TABLE employees

(employee_id NUMBER(6),

last_name VARCHAR2(25) NOT NULL,

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

hire_date DATE NOT NULL,

CONSTRAINT emp_email_uk UNIQUE(email));

PRIMARY KEY限制

唯一鍵的所有特征都适用于主鍵限制,隻是在主鍵列中不允許有NULL值.一個表隻能有一個主鍵.

e.g.

CREATE TABLE 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 PRIMARY KEY(department_id));

FOREIGN KEY 限制

用來保護一個表中的一個或者多個列,它會通過一個主鍵主鍵或者唯一鍵保證對于每個非NULL值在資料庫的其他地方都有一個資料可用.這個外部鍵就是在生成此限制的表(子表)中的一個或多個列,在父級表和子表中,相關列的資料類型必須比對.外部鍵列和引用鍵(reference key)列可以位于相同的表中(自引用完整性限制).

e.g.

CREATE TABLE employees

(employee_id NUMBER(6),

last_name VARCHAR2(25) NOT NULL,

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

hire_date DATE NOT NULL,

deparment_id NUMBER(4),

CONSTRAINT emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id),

CONSTRAINT emp_email_uk UNIQUE(email));

上例中是在表級定義外部鍵限制,如果在列級定義,不同的是:

CREATE TABLE employees

(…,

department_id NUMBER(4) CONSTRAINT emp_deptid_fk REFERENCES departments(department_id),

…);

//沒有關鍵字FOREIGN KEY

FOREIGN KEY限制還有兩個關鍵字是

ON DELETE CASCADE --當删除所引用的父表記錄時,删除子表中相關的記錄

ON DELETE SET NULL–與上面不同的是删除時,轉換子表中相關記錄為NULL值

預設情況下,如果沒有指定以上兩個中任一,則父表中被引用的記錄将不能被删除.

CHECK 限制

[CONSTRAINT ] CHECK (

這裡CHECK子句中的CONDITION應該求值為一個布爾值結果,并且可以引用相同行中其他列的值;不能包含子查詢,序列,環境函數(SYSDATE,UID,USER,USERENV)和僞列(ROWNUM,LEVEL,CURRVAL,NEXTVAL),一個列上可以定義多個CHECK限制,如果所定義的條件為FALSE,則語句将復原.

CREATE TABLE employees

(…,

salary NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary>0),

…);

添加限制

ALTER TABLE employees

ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);

删除限制

ALTER TABLE employees

DROP CONSTRAINT emp_manager_fk;

ALTER TABLE departments

DROP PRIMARY KEY CASCADE; //由于departments被employees.department_id引用了

對于NOT NULL限制,用ALTER TABLE MODIFY子句來删除

ALTER TABLE employees MODIFY last_name NULL;

關閉限制

ALTER TABLE employees

DISABLE CONSTRAINT emp_emp_id_pk CASCADE; //如果沒有被引用則不需CASCADE關鍵字

當你生成一個限制時,限制自動打開(除非你指定了DISABLE子句0,當用DISABLE關閉UNIQUE或者PRIMARY KEY限制時,ORACLE會自動删除相關的唯一索引,再次打開時,ORACLE又會自動建立的.

打開限制

ALTER TABLE employees

ENABLE CONSTRAINT emp_emp_id_pk; //注意,打開一個先前關閉的被引用的主鍵限制,并不能自動打開相關的外部鍵限制

可以從USER_CONSTRAINTS表和USER_CONS_COLUMNS視圖中查詢限制的資訊

SELECT constraint_name,constraint_type,search_condition

FROM user_constraints

WHERE table_name=‘EMPLOYEES’;

限制類型

C–CHECK和NOT NULL都算為C TYPE

P–PRIMARY KEY

R–REFERENTIAL INTEGRITY就是外部鍵限制

U–UNIQUE

SELECT constraint_name,column_name

FROM user_cons_columns

WHERE table_name=‘EMPLOYEES’;