MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. --引用官方
簡單說罚攀，外鍵約束就是把兩張表的數(shù)據(jù)通過某種條件關(guān)聯(lián)起來, 作用是保持?jǐn)?shù)據(jù)完整性和一致性
語法如下
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
Adding foreign keys
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
Dropping Foreign Keys
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
例子，
有一個parent表,含一個字段id(int)
有一個child表猫态，含兩個字段分別是 id(int) 和 parent_id(int)
現(xiàn)在為child表增加刪除聯(lián)動(delete cascade)外鍵该园，當(dāng) parent 表 的 id=2 的記錄被刪除時，如果對應(yīng) child 表有相對應(yīng)的parent_id=2 ,那么 child 表的該行記錄也會被刪除拉馋。
//創(chuàng)建表parent，并插入數(shù)據(jù)
CREATE TABLE `parent` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into parent values(1),(2),(3);
// 創(chuàng)建表child,并插入數(shù)據(jù)
CREATE TABLE `child` (
`id` int(11) NOT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into child values(10,1),(20,2),(30,3);
// 為child表添加一個外鍵，與parent表的關(guān)系是刪除聯(lián)動
// 當(dāng)parent表的記錄被刪除時埠巨，如果指定的外鍵parent_id=id時双妨，child表的相對應(yīng)的記錄也同時被刪除
ALTER TABLE child ADD CONSTRAINT child_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE;
//刪除parent表id=2的記錄
delete from parent where id=2;
// 發(fā)現(xiàn)child表parent_id=2的記錄也被刪除掉了
select * from child;
詳情請看Using FOREIGN KEY Constraints
順帶說說主鍵(Primary key)
A primary key is a column or a set of columns that uniquely identifies each row in the table. You must follow the rules below when you define a primary key for a table:
- A primary key must contain unique values. If the primary key consists of multiple columns, the combination of values in these columns must be unique.
- A primary key column cannot contain NULL values. It means that you have to declare the primary key column with the NOT NULL attribute. If you don’t, MySQL will force the primary key column as NOT NULL implicitly.
- A table has only one primary key.
簡單而言，主鍵就是一個或多個字段蔓腐，用來唯一標(biāo)識表中的每行記錄
主鍵的值必須是唯一的矩乐，非空的，每個表只有一個主鍵
主鍵的數(shù)據(jù)類型，最好是 int 類型散罕，Because MySQL works faster with integers
主鍵通常設(shè)為 auto_increment分歇，自動生成唯一的序列值
添加主鍵
ALTER TABLE table_name ADD PRIMARY KEY(primary_key_column);
Primary key vs. unique key vs. key
-
key的同義詞就是index索引，當(dāng)你想為字段創(chuàng)建索引create an index的時候欧漱，用到key關(guān)鍵詞 -
unique key與Primary key相似职抡，要求值是唯一的，不同的是：允許為NULL值误甚，mysql也允許可以設(shè)定多個uniqueindexs
Introduction to MySQL primary key
MySQL Managing Database Index
額外提一下好用的replace()
簡單而言缚甩，就是替換掉你想更改的東西
應(yīng)用場景，表t1里的url字段里的所有記錄中窑邦，要把http改成https
UPDATE t1 SET url=REPLACE(url,'http','https');
詳情請看replace()用法
