Skip to content

Latest commit

 

History

History
169 lines (128 loc) · 4.09 KB

7_0FOREIGN_KEY.md

File metadata and controls

169 lines (128 loc) · 4.09 KB

Foreign Key

Foreign Key 是一個Constraint(限制)

  • 有Foreign Key的table稱為reference table
  • 被參考的稱為parent table

Foreign的主要目地

  • 驗証被參考的parent table,一定有這個值
  • 保持reference table和 parent table的一致性
  • 還有加上ON DELETE ACTION ,和 ON UPDATE ACTION的功能
    • 代表的是parent table的資料如果被刪除或更新時,refernece table該如何處理foreign key的資料

也可以不設foreign key,但就無法保證資料的一致性

ACTION 支援的語法:

  • SET NULL
  • SET DEFAULT
  • RESTRICT
  • NO ACTION
  • CASCADE

語法:

FOREIGN KEY(fk_columns) 
REFERENCES parent_table(parent_key_columns)
[ON DELETE delete_action]
[ON UPDATE update_action]

範例NO_ACTION設定:

  • GENERATED ALWAYS AS IDENTITY
    • 欄位必需是SMALLINT, INT, BIGINT
    • 一定自動產生
  • GENERATED BY DEFAULT AS IDENTITY
    • 欄位必需是SMALLINT, INT, BIGINT
    • 自動產生或手動加入
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS contacts;

CREATE TABLE customers(
	customer_id INT GENERATED ALWAYS AS IDENTITY,
	customer_name VARCHAR(255) NOT NULL,
	PRIMARY KEY(customer_id)
);



CREATE TABLE contacts(
	contact_id INT GENERATED ALWAYS AS IDENTITY,
	customer_id INT,
	contact_name VARCHAR(255) NOT NULL,
	phone VARCHAR(15),
	email VARCHAR(100),
	PRIMARY KEY(contact_id),
	CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');	   
	   
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','[email protected]'),
      (1,'Jane Doe','(408)-111-1235','[email protected]'),
      (2,'David Wright','(408)-222-1234','[email protected]');
	  
/*沒有設定ON DELETE和ON UPDATE*/
/*DEFAULT NO ACTION*/
/*下面所以違反限定,出現錯誤*/
DELETE FROM customers
WHERE customer_id = 1

範例SET NULL設定:

  • reference table 的 foreign key 值被設為NULL
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers; /*parent table*/
 

CREATE TABLE customers(
	customer_id INT GENERATED ALWAYS AS IDENTITY,
	customer_name VARCHAR(255) NOT NULL,
	PRIMARY KEY(customer_id)
);



CREATE TABLE contacts(
	contact_id INT GENERATED ALWAYS AS IDENTITY,
	customer_id INT,
	contact_name VARCHAR(255) NOT NULL,
	phone VARCHAR(15),
	email VARCHAR(100),
	PRIMARY KEY(contact_id),
	FOREIGN KEY(customer_id) 
	REFERENCES customers(customer_id)
	ON DELETE SET NULL
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');	   
	   
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','[email protected]'),
      (1,'Jane Doe','(408)-111-1235','[email protected]'),
      (2,'David Wright','(408)-222-1234','[email protected]');
	  

/*ON DELETE SET NULL*/
/*parent table被刪除,所有refernece 資料被設為NULL*/
DELETE FROM customers
WHERE customer_id = 1;

SELECT * FROM contacts;

範例SET CASCADE設定:

  • parent table的資料被刪除,reference table 也被刪除
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers; /*parent table*/
 

CREATE TABLE customers(
	customer_id INT GENERATED ALWAYS AS IDENTITY,
	customer_name VARCHAR(255) NOT NULL,
	PRIMARY KEY(customer_id)
);



CREATE TABLE contacts(
	contact_id INT GENERATED ALWAYS AS IDENTITY,
	customer_id INT,
	contact_name VARCHAR(255) NOT NULL,
	phone VARCHAR(15),
	email VARCHAR(100),
	PRIMARY KEY(contact_id),
	FOREIGN KEY(customer_id) 
	REFERENCES customers(customer_id)
	ON DELETE CASCADE
);

INSERT INTO customers(customer_name)
VALUES('BlueBird Inc'),
      ('Dolphin LLC');	   
	   
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1,'John Doe','(408)-111-1234','[email protected]'),
      (1,'Jane Doe','(408)-111-1235','[email protected]'),
      (2,'David Wright','(408)-222-1234','[email protected]');
	  

/*ON DELETE CASCADE*/
/*parent table被刪除,所有refernece 資料也被刪除*/
DELETE FROM customers
WHERE customer_id = 1;

SELECT * FROM contacts;