Oracle 11g系列：約束

libingql發表於2014-03-11
Oracle

　　約束是每個資料庫必不可少的一部分，約束的目的在於儲存資料的完整性。資料完整性是指資料的精確性和可靠性。資料庫約束主要包括：主鍵約束、外來鍵約束、唯一性約束、檢查約束和預設值約束。

　　1、主鍵約束

　　主鍵約束可以保障資料完整性，防止資料表中的兩條記錄完全相同。通過主鍵輸入查詢條件，可以查詢結果最多返回一條記錄。

　　主鍵被建立在一個或多個列上，通過這些列的值或者值的組合，唯一地標識一條記錄。

　　1>、建立主鍵約束

　　主鍵約束作為表結構設計的一部分，一般在建立表的時候建立。建立主鍵約束使用關鍵字primary key。

　　2>、檢視主鍵約束

select table_name,constraint_name,constraint_type,status from user_constraints
where table_name='emp'
select constraint_name,table_name,column_name from user_cons_columns
where constraint_name='SYS_C005121'

　　3>、顯示命名主鍵約束

CREATE TABLE EMP
(
       EMP_ID NUMBER CONSTRAINT PK_EMP PRIMARY KEY,
       EMP_NAME VARCHAR(20)
)
CREATE TABLE EMP
(
       EMP_ID NUMBER,
       EMP_NAME VARCHAR(20),
       CONSTRAINT PK_EMP PRIMARY KEY(EMP_ID)
)

　　4>、建立多列主鍵

CREATE TABLE EMP
(
       EMP_ID NUMBER,
       EMP_NAME VARCHAR(20),
       CONSTRAINT PK_EMP PRIMARY KEY(EMP_ID,EMP_NAME)
)

　　5>、為表新增主鍵

ALTER TABLE EMP MODIFY (EMP_ID NUMBER PRIMARY KEY)

　　6>、為表新增多列主鍵

ALTER TABLE EMP ADD CONSTRAINT PK_EMP PRIMARY KEY(EMP_ID,EMP_NAME)

　　7>、刪除主鍵

ALTER TABLE EMP DROP PRIMARY KEY

　　DROP PRIMARY KEY用於刪除表的主鍵，由於一個表的主鍵是唯一的，所以無須指定主鍵名。

ALTER TABLE EMP DROP CONSTRAINT PK_EMP

　　DROP CONSTRAINT用於刪除表的約束，一個表的約束可能有多個，所以必須指定約束的名稱。

　　8>、啟用/禁用主鍵

ALTER TABLE EMP DISABLE PRIMARY KEY
ALTER TABLE EMP ENABLE PRIMARY KEY

　　9>、重新命名主鍵約束名稱

ALTER TABLE EMP RENAME CONSTRAINT SYS_C005245 TO PK_EMP

　　2、外來鍵約束

　　外來鍵與之間用於用於保證資料完整性，主鍵是針對單個表的約束，外來鍵則描述了表之間的關係。

　　1>、建立外來鍵約束

CREATE TABLE CUSTOMERS
(
       CUSTOMER_ID NUMBER,
       CUSTOMER_NAME VARCHAR(20),
       CONSTRAINT PK_CUSTOMERS PRIMARY KEY(CUSTOMER_ID)
)
CREATE TABLE ORDERS
(
       ORDER_ID NUMBER,
       CUSTOMER_ID NUMBER,
       PRODUCT_NAME VARCHAR(20),
       QUANTITY NUMBER,
       CONSTRAINT PK_ORDERS PRIMARY KEY(ORDER_ID)
)
ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_CUSTOMERS FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID)

　　2>、檢視外來鍵

SELECT TALBE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'ORDERS'

　　3>、級聯刪除

　　ON DELETE CASCADE選項指定建立的外來鍵實現級聯刪除，即刪除主表的記錄時，字表中的子記錄同時被自動刪除。刪除子表記錄的動作由Oracle資料自動實現的。

ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_CUSTOMERS FOREIGN KEY(CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOM) ON DELETE CASCADE

　　4>、重新命名外來鍵約束

ALTER TABLE ORDERS RENAME FK_ORDERS_CUSTOMERS TO FK_ORDERS

　　5>、啟用/禁用外來鍵

ALTER TABLE ORDERS MODIFY CONSTRAINT FK_ORDERS_CUSTOMERS DISABLE
ALTER TABLE ORDERS MODIFY CONSTRAINT FK_ORDERS_CUSTOMERS ENABLE

　　在預設情況下，啟用外來鍵時，將會對已有的資料進行校驗。可以使用NOVALIDATE選項，使Oracle不對已有資料進行校驗，從而直接啟用外來鍵。

ALTER TABLE ORDERS MODIFY CONSTRAINT FK_ORDERS_CUSTOMERS ENABLE NOVALIDATE

　　6>、刪除外來鍵

ALTER TABLE ORDERS DROP CONSTRAINT FK_ORDERS_CUSTOMERS

　　3、惟一性約束

　　1>、建立惟一性約束

CREATE TABLE USERS
(
       USER_ID NUMBER PRIMARY KEY,
       USER_NAME VARCHAR2(20),
       EMAIL VARCHAR2(100) UNIQUE
)

　　2>、新增惟一性約束

ALTER TABLE USERS ADD CONSTRAINT UQ_EMAIL UNIQUE (EMAIL)

　　3>、刪除惟一性約束

ALTER TABLE USERS DROP CONSTRAINT UQ_EMAIL

　　4>、重新命名惟一性約束

ALTER TABLE USERS RENAME CONSTRAINT SYS_C005188 TO UQ_EMAIL

　　5>、禁用/啟用惟一性約束

ALTER TABLE USERS DISABLE CONSTRAINT UQ_EMAIL

　　或：

ALTER TABLE USERS MODIFY CONSTRAINT UQ_EMAIL DISABLE

　　4、檢查約束

　　檢查約束對列值進行限制，將表中的一列或多列限制在某個範圍內。

　　1>、建立檢查約束

CREATE TABLE STUDENTS
(
       STUDENT_ID NUMBER PRIMARY KEY,
       STUDENT_NAME VARCHAR2(20),
       SCORE NUMBER,
　　　　CONSTRAINT CK_SCORE CHECK(SCORE BETWEEN 0 AND 100)
)

　　2>、新增檢查約束

ALTER TABLE STUDENTS ADD CONSTRAINT CK_STUDENT_NAME CHECK (LENGTH(STUDENT_NAME) <= 4)
ALTER TABLE STUDENTS ADD CONSTRAINT CK_SEX CHECK (SEX IN ('男','女'))

　　5、預設值約束

　　1>、新增預設值約束

ALTER TABLE ORDERS MODIFY QUANTITY DEFAULT 1

　　2>、刪除預設值

ALTER TABLE ORDERS MODIFY QUANTITY DEFAULT NULL

相關文章