1.NOT NULL：禁止放null
2.UNIQUE：同一欄位只能唯一，但允許放null
3.PRIMARY KEY(PK)：NOT NULL + UNIQUE
4.FOREIGN KEY(FK)：欄位指定FK後，表示此欄位的值必需是另外一張表的欄位值的其中之一
5.CHECK：符合指定的條件
6.REF：參考到另外一個物件，個人覺得這個比較不像約束，比較像自定型態，很多書寫完整性約束時也沒有提到這個，不過官網有就是了
※NOT NULL只能放在宣告欄位的最後面，其他的除了這種方式，還有一種是為約束命名的寫法，這個是官方說的，可是FK我試的結果只能用約束命名的寫法(而且只能使用CONSTRAINT獨立一行)，官網也沒有提供不寫約束名稱的範例
※以下以這個範例來練習，先寫沒有約束名稱的寫法
DROP TABLE xxx PURGE; CREATE TABLE xxx ( id number(5) PRIMARY KEY, name varchar2(20) UNIQUE, color varchar2(10) NOT NULL, price number(5,2) DEFAULT 0 CHECK(price BETWEEN 0 AND 1000), make_date timestamp DEFAULT sysdate UNIQUE ); CREATE TABLE ooo ( oid number(5) PRIMARY KEY, oname varchar2(20) UNIQUE, xid number(5), CONSTRAINT fk_xid FOREIGN KEY(xid) REFERENCES xxx(id) );
※DEFAULT一定要在約束前面，如果有DEFAULT，INSERT時可以不塞值
※NOT NULL
INSERT INTO xxx(id, color) VALUES (1, null); INSERT INTO xxx(id) VALUES (1);
※預設不打就是null，所以這兩條SQL是一樣的
※因為color是null，所以會出「ORA-01400: 無法將 NULL 插入 ("帳號"."XXX"."NAME")」的錯
※UNIQUE
INSERT INTO xxx(id, name, color, price) VALUES (1, 'apple', 'red', 50); INSERT INTO xxx(id, name, color, price) VALUES (2, 'apple', 'blue', 40);
※第一條SQL新增成功，但第二條的name值一樣，所以會出「ORA-00001: 違反必須為唯一的限制條件」的錯
※null可以重覆，官網有說明
※PRIMARY KEY
一筆記錄的唯一值，所以是NOT NULL + UNIQUE※CHECK
INSERT INTO xxx(id, name, color, price) VALUES (1, 'apple', 'red', 1002);
※因為price的範圍是0~1000，1002已經超過了，所以會出「ORA-02290: 違反檢查條件 (C##SCOTT.SYS_C0010287)」的錯
※FOREIGN KEY
一定要有兩張表而且FK對應的欄位必需有UNIQUE(所以PK也可以)，而且型態也要一樣只要欄位有FK，那就表示它的值有多筆，對應到另外一個table的欄位值(除非另外一個table的欄位值只有一種)
因為這樣的原因，所以刪除時，預設也必須先刪除子項(FK)的欄位，才能刪除父項(PK)的欄位
INSERT INTO xxx(id, name, color) VALUES (1, 'aaa', 'red'); INSERT INTO xxx(id, name, color) VALUES (2, 'bbb', 'blue'); INSERT INTO ooo(oid, oname, xid) VALUES (2, 'qoo', 3);
※xxx新增兩條SQL，而ooo的xid是對應到xxx的id，而xxx的id值，全部的值有1和2，所以ooo的xid不塞1或2，就會發生「ORA-02291: 違反完整性限制條件 (C##SCOTT.FK_XID) - 找不到父項索引鍵」的錯
※null是可以的
※REF
-- 1.新增TYPE
DROP TYPE aaa;
CREATE TYPE aaa AS OBJECT (
p1 varchar2(40),
p2 varchar(2)
);
-- 2.新增Table，並使用REF型態
DROP TABLE bbb PURGE;
CREATE TABLE bbb (
id number,
ref_name REF aaa
);
-- 3.新增另一張Table，並引用aaa TYPE，順便塞值
DROP TABLE table_a PURGE;
CREATE TABLE table_a OF aaa;
INSERT INTO table_a VALUES('1', 'a');
INSERT INTO table_a VALUES('2', 'b');
-- 4.新增資料
INSERT INTO bbb(id, ref_name)VALUES(1,
(SELECT REF(t) FROM table_a t WHERE P1 = '1')
);
INSERT INTO BBB(id, ref_name)VALUES(2,
(SELECT REF(t) FROM table_a t WHERE P1 = '2')
);
※主要是2的bbb表，它要使用一個TYPE，所以1才要新增TYPE
4要新增時，不知道怎麼塞值，所以3就引用的aaa TYPE
所以使用REF是不能直接在裡面塞值的，要像3這樣引用才可以
※修改可參考PL/SQL 三十三
※以上的6種約束可以混用，用空格隔開如「NOT NULL UNIQUE」，但不是全部都可以，詳情要看官網的說明，例如PRIMARY KEY UNIQUE就不行
※有約束名稱的寫法
CREATE TABLE xxx ( id number(5), name varchar2(20), color varchar2(10) NOT NULL, price number(5,2) DEFAULT 0, make_date timestamp DEFAULT sysdate UNIQUE, CONSTRAINT pk_id PRIMARY KEY(id), CONSTRAINT uk_name UNIQUE(name), CONSTRAINT ck_price CHECK(price BETWEEN 0 AND 1000) ); CREATE TABLE xxx ( id number(5) CONSTRAINT pk_id PRIMARY KEY, name varchar2(20) CONSTRAINT uk_name UNIQUE, color varchar2(10) NOT NULL, price number(5,2) DEFAULT 0 CONSTRAINT ck_price CHECK(price BETWEEN 0 AND 1000), make_date timestamp DEFAULT sysdate UNIQUE );
※PK、UNIQUE、CHECK都可以獨立成一行或宣告欄位的最後面
※獨立一行還可以設定多個欄位，下面會介紹
※PK、FK、UNIQUE多個欄位
DROP TABLE xxx PURGE;
CREATE TABLE xxx (
id number(5),
name varchar2(20),
color varchar2(10) NOT NULL,
price number(5,2) DEFAULT 0 UNIQUE,
make_date timestamp DEFAULT sysdate UNIQUE,
CONSTRAINT pk_id_name PRIMARY KEY(id, name),
CONSTRAINT uk_name UNIQUE(name),
CONSTRAINT ck_price CHECK(price BETWEEN 0 AND 1000)
);
DROP TABLE ooo PURGE;
CREATE TABLE ooo (
oid number(5),
oname varchar2(20),
xid number(5),
xname varchar2(20),
CONSTRAINT pk_oid_xid PRIMARY KEY(oid, xid),
CONSTRAINT fk_xid_xname FOREIGN KEY(xid, xname) REFERENCES xxx(id, name),
CONSTRAINT uk_oname_xid UNIQUE(oname, xid)
);
※主要是看ooo表，而FK對應的表欄位兩個欄位也要有UNIQUE(所以PK也可以)
※新增／刪除約束
-- 1 ALTER TABLE xxx MODIFY(color varchar2(25)); -- 2 ALTER TABLE ooo DROP CONSTRAINT fk_xid; ALTER TABLE xxx DROP CONSTRAINT pk_id; ALTER TABLE xxx ADD CONSTRAINT pk_id PRIMARY KEY(id); ALTER TABLE ooo ADD CONSTRAINT fk_xid FOREIGN KEY(xid) REFERENCES xxx(id); -- 3 ALTER TABLE xxx DROP CONSTRAINT ck_price; ALTER TABLE xxx ADD CONSTRAINT ck_price CHECK(price BETWEEN 500 AND 2000); -- 4 ALTER TABLE xxx DISABLE /*ENABLE*/ CONSTRAINT ck_price;
※1是NOT NULL用的
※2我在官網沒有看到修改約束和修改約束名稱的功能，所以只好先刪除再新增了
※2和3是PK、FK、CHECK
※4是將約束啟用／停用的功能
※因為有這個功能，所以有些地方是先新增表，創建完了才用這種語法加一些限制條件
沒有留言:
張貼留言