PHP資料連線主鍵與外鍵！

標籤：int   c   set   資料   cti   使用   

設定MySQL資料表主鍵： 使用“primary key”關鍵字建立主鍵資料列。被設定為主鍵列不允許出現重複的值，很多情況下與“auto_increment”遞增數字相結合。如下SQL語句所示：

Mysql>create table books(bookid int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,bookname varchar(50)); Mysql>insert into books(bookname) values(“book1”),(“book2”),(“book3”); Mysql>select * from books; 

若要修改列主鍵或類型，請參考 ALTER TABLE 語句。 設定MySQL資料表外鍵 外鍵是設定當前表中的某一列與別一資料表中的主鍵列關聯。主要目的是控制與外鍵表中的資料，保持資料一致性，完整性，也就是說：當前表中這一列的資料必須是關聯外鍵列中的某一資料，而且相關聯的兩個資料列的類型必須相同；當關聯外鍵列某一資料修改或刪除時，將觸當前表的某一項相應操作。可解發以下事件以及參數： 觸發事件：on delete和on update 可設參數：cascade(跟隨外鍵改動)； restrict(限制外表中的外鍵改動)； set Null(設空值）； set Default（設預設值）； no action [預設] 設定關聯的語句由[指定主鍵關鍵字：foreign key(列名)]和[引用外鍵關鍵字： references <外鍵表名>(外鍵列名)]組成。例如建立一個關於books的購物車資料表“gbooks”，其中“gbookid”與books表中的“bookid”建立外部索引鍵關聯。

Mysql>create table gbooks(gid int(11) not null auto_increment primary key,gbookid int(11),goodsum int(4),foreign key(gbookid) references books(bookid) on delete cascade on update cascade); 

刪除外鍵： 首先，使用SHOW CREATE TABLE語句查看建立表描述。其中“CONSTRAINT”關鍵字後面有一個引號括起來的名稱，它就是這個表外鍵的代表，是在建立外鍵時自動產生的名稱，當然在建立的過程中可以直接用“CONSTRAINT”關鍵字自訂名稱。其查看的完整語句如下：

Mysql>show create table gbooks; 

這裡代表外鍵的名稱是“gbooks_ibfk_1”，目標找到了，再使用ALTER語句進行刪除。

Mysql>ALTER TABLE gbooks DROP FOREIGN KEY gbooks_ibfk_1; 

主鍵和外鍵的設定意義： 主鍵和外鍵是把多個表組織為一個有效關聯式資料庫的粘合劑。主鍵和外鍵的設計對物理資料庫的效能和可用性都有著決定性的影響。 必須將資料庫模式從理論上的邏輯設計轉換為實際的實體設計。而主鍵和外鍵的結構是這個設計過程的癥結所在。一旦將所設計的資料庫用於了生產環境，就很難對這些鍵進行修改，所以在開發階段就設計好主鍵和外鍵就是非常必要和值得的。 主鍵： 關聯式資料庫依賴於主鍵—它是資料庫物理模式的基石。主鍵在物理層面上只有兩個用途： 1. 惟一地標識一行。 2. 作為一個可以被外鍵有效引用的對象。 基於以上這兩個用途，下面給出了我在設計物理層面的主鍵時所遵循的一些原則： 1. 主鍵應當是對使用者沒有意義的。如果使用者看到了一個表示多對多關係的串連表中的資料，並抱怨它沒有什麼用處，那就證明它的主鍵設計地很好。 2. 主鍵應該是單列的，以便提高串連和篩選操作的效率。 3. 永遠也不要更新主鍵。實際上，因為主鍵除了惟一地標識一行之外，再沒有其他的用途了，所以也就沒有理由去對它更新。 註：這項原則對於那些經常需要在資料轉換或多資料庫合并時進行資料整理的資料並不適用。 4. 主鍵不應包含動態變化的資料，如時間戳記、建立時間列、修改時間列等。 5. 主鍵應當有電腦自動產生。如果由人來對主鍵的建立進行幹預，就會使它帶有除了惟一標識一行以外的意義。一旦越過這個界限，就可能產生認為修改主鍵的動機，這樣，這種系統用來連結記錄行、管理記錄行的關鍵手段就會落入不瞭解資料庫設計的人的手中。 外鍵是用來和其他表建立聯絡用的：這個表中的一列和另外一個表中的一列相同，為了讓這兩個表聯絡起來，就把其中一個表中的列設成外鍵，把另外一個表列設成主鍵，就實現了這兩個表的關聯。一個表可以有多個外鍵。但主鍵只能有一個。 像MySQL這樣的關係型資料庫管理系統，它們的基礎是在資料庫的表之間建立關係的能力。通過方便地在不同表中建立記錄到記錄的聯絡，RDBMS可以利用不同的方法分析資料，同時保持資料庫以系統的方式、最小的冗餘進行組織。 像MySQL這樣的關係型資料庫管理系統，它們的基礎是在資料庫的表之間建立關係的能力。通過方便地在不同表中建立記錄到記錄的聯絡，RDBMS可以利用不同的方法分析資料，同時保持資料庫以系統的方式、最小的冗餘進行組織。 簡單描述： 這些關係基本上依靠外鍵進行管理，在關係中所有表中具有相同含義的欄位作為公用部分來串連不同表中的記錄。外鍵可以是一對一的，一個表的記錄只能與另一個表的一條記錄串連，或者是一對多的，一個表的記錄與另一個表的多條記錄串連。 MySQL中“鍵”和“索引”的定義相同， 所以外鍵和主鍵一樣也是索引的一種。不同的是MySQL會自動為所有表的主鍵進行索引，但是外鍵欄位必須由使用者進行明確的索引。這和一些封建思想比較沉重的家庭是一樣的，外來的孩子(兒媳婦，倒插門女婿)一般都是不受重視的。 低俗樣本： 表間一對一關聯性樣本： 有兩張表，第一張表是記錄公司有多少人，都有誰，也就是員工編號及員工姓名這些基本表。另一張表記錄每個月發給使用者多少工資，所謂工資表是也。 但是工資表裡面不能以員工姓名為主鍵，同樣要通過員工id，因為員工的姓名是可能重複的啊。部門經理叫張三，小弟也叫張三，那這倆張三的工資能一樣嗎？並且員工表裡面的每個人都有工資，否則誰也不給你幹活，且一個人只能有一份工資，否則老闆也不同意了。所以員工表和工資表是通過員工id進行關聯的一對一關聯性。 不過我們要有一個好的價值觀，我們上班不能為了錢，我們是為了學知識，學文化，為早日實現四個現代化(別問我是啥，也別問我到底實現沒有)而努力奮鬥。所以在工資表裡如果沒有你也不要亂喊。嗯。 /* 建立員工表 */ create table employees ( id int(5) not null auto_increment , name varchar(8) not null, primary key (id) ) type＝innodb; /* 建立工資表 */ create table payroll( id int(5) not null, emp_id int(5) not null, name varchar(8) not null, payroll float(4,2) not null, primary key(id), index emp_id (emp_id), foreign key (emp_id) references employees (id) ) type = innodb; 表間一對多關聯性樣本： 有兩個表，一個是貪官表，有貪官的id和名字。另有一張貪官情婦表，注意一個貪官不一定只有一個情婦，其有個二三四五奶是很正常的，所以在貪官表裡面的一條資料，對應情婦表裡可能就有多條記錄，這是通過貪官id進行關聯的一對多關聯性。 參照完整性： 當外鍵與另一個表的欄位有關係，而且這種關係是惟一時，這個系統就稱為處於參照完整性的狀態。也就是說，如果一個欄位在所有的表中只出現一次，而且每個表的這個欄位的變化都會影響其他表，這就是存在參照完整性。 術語理解上可能不太方便，其實就是說要在有外鍵的表中保持所有資料的一致性。比如說“張三”離職了，在員工表裡面肯定沒有這個人了，可是如果在工資表裡面還存在這個孩子，那麼老大就會很生氣的。 另外，比如說一個縣官，因為一些小政績，由縣官變成了知府，那麼他的那些情婦的地位也要調整一下，最起碼得從縣官二奶改為知府二奶，否則這位二奶也是不會同意的。 MySQL的外鍵只能在InnoDB表中使用： 當今主流資料庫都會自動考慮參照完整性的問題。當你更新或刪除資料時，其會把相關聯的表中資料也都給你變過來。比如縣官張三改名為王二麻子，其情婦的稱號就會自動改為王二麻子的情婦。嗯。 MySQL對此一直持觀望態度，它允許使用外鍵，但是為了完整性檢驗的目的，在除了InnoDB表類型之外的所有表類型中都忽略了這個功能。這可能有些怪異，實際上卻非常正常：對於資料庫的所有外鍵的每次插入、更新和刪除後，進行完整性檢查是一個耗費時間和資源的過程，它可能影響效能，特別是當處理複雜的或者是纏繞的串連樹時。因而，使用者可以在表的基礎上，選擇適合於特定需求的最好結合。。 所以，如果需要更好的效能，並且不需要完整性檢查，可以選擇使用MyISAM表類型，如果想要在MySQL中根據參照完整性來建立表並且希望在此基礎上保持良好的效能，最好選擇表結構為innoDB類型。 MySQL建立外鍵文法： 建立外鍵的文法是這樣的：FOREIGN KEY (當前表的欄位名)… REFERENCES 參照表 (參照表的欄位名) foreign key (emp_id) references employees (id); 的意思就是說當前表的emp_id欄位是以employees的id欄位為外鍵的。 注意事項： 一旦建立外鍵，MySQL只允許向當前表中加入外鍵表中已有的資料列。比如說貪官表裡有“王二麻子”，那麼在情婦表只才能有“王二麻子的情婦”。也就是說只有確認一個人是貪官了，才能把其情婦資訊列入此表中，否則是不行滴。 關係中的所有表必須是innoDB表，在非InnoDB表中，MySQL將會忽略FOREIGN KEY…REFERENCES修飾符。 用於外鍵關係的欄位必須在所有的參照表中進行明確地索引，InnoDB不能自動地建立索引。 在外鍵關係中，欄位的資料類型必須相似，這對於大小和符號都必須匹配的整數類型尤其重要。 即使表存在外鍵約束，MySQL還允許我們刪除表，並且不會產生錯誤(即使這樣做可能會破壞更早建立的外鍵) 刪除外鍵方法： long long ago，人們只能通過刪除表來刪除外鍵。不過現在MySQL(在4.0.13及更高版本中)提供了一種從表中刪除外鍵比較緩和的方法，緩和與否不太清楚，但是至少不再那麼無恥。 ALTER TABLE table-name DROP FOREIGN KEY key-id; 這裡有一個概念，這個外鍵的id是啥玩意？我們可以通過SHOW　CREATE TABLＥ 命令來獲得key-id的值。日後我們詳細討論這些內容，大家可以自行示範。 /* 顯示建表結構語句，key-id為payroll_ibfk_1 */ show create table payroll /G /* *************************** 1. row *************************** Table: payroll Create Table: CREATE TABLE `payroll` ( `id` int(5) NOT NULL, `emp_id` int(5) NOT NULL, `name` varchar(8) NOT NULL, `payroll` float(4,2) NOT NULL, PRIMARY KEY (`id`), KEY `emp_id` (`emp_id`), CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employees` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) */ 自動鍵更新和刪除： 外鍵可以保證新插入的記錄的完整性。但是，如果在REFERENCES從句中從已命名的表刪除記錄會怎樣？在使用同樣的值作為外鍵的輔助表中會發生什嗎？ 很明顯，那些記錄也應該被刪除，否則在資料庫中就會有很多無意義的孤立記錄。MySQL可能通過向FOREIGN KEY…REFERENCES 修飾符添加一個ON DELETE或ON UPDATE子句簡化任務，它告訴了資料庫在這種情況如何處理孤立任務。 請注意，通過 ON UPDATE　和ON DELETE規則，設定ＭｙＳＱＬ能夠實現自動操作時，如果鍵的關係沒有設定好，可能會導致嚴重的資料破壞。例如，如果一系列的表通過外鍵關係和ON DELETE CASCADE 規則串連時，任意一個主表的變化都會導致甚至只和原始刪除有一些將要聯絡的記錄在沒有警告的情況下被刪除。所以，我們在操作之前還是要檢查這些規則的，操作之後還要再次檢查。