Mysql methods to add foreign keys are: add directly after the attribute value; add at the end of the sql statement; use fk and add foreign keys after creating the table
Related learning recommendations: mysql tutorial
4 ways to add foreign keys to mysql
Create the main table: Class
CREATE TABLE class(cid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(15) NOT NULL) INSERT INTO class VALUES(NULL,'六年级一班') INSERT INTO class VALUES(NULL,'六年级二班')
First method: Add
CREATE TABLE student(sid INT AUTO_INCREMENT, sname VARCHAR(10) NOT NULL, s_cid INT REFERENCES class(cid), PRIMARY KEY(sid) );
directly after the attribute value//Add data for testing
INSERT INTO student VALUES(NULL,'王大锤',2) INSERT INTO student VALUES(NULL,'胡汉三',3) //约束不生效,原因待查 INSERT INTO student VALUES(NULL,'卧槽',4) //不敢相信,在测试一次
This way , I don’t know why, but it was added in the test WTF? No results on Baidu online, no reply in the self-study group. I will leave it here for now
Second type: 2. Add at the end of the sql statement
CREATE TABLE student( sid INT AUTO_INCREMENT, sname VARCHAR(10), s_cid INT, PRIMARY KEY(sid), FOREIGN KEY (s_cid) REFERENCES class(cid) );
//Insert data and test it
INSERT INTO student VALUES(NULL,'王大锤',2) INSERT INTO student VALUES(NULL,'胡汉三',3) //约束生效 插入失败
Foreign key constraints take effect
Third method: use of fk
CREATE TABLE student(sid INT AUTO_INCREMENT, sname VARCHAR(10) NOT NULL, s_cid INT , PRIMARY KEY(sid), CONSTRAINT fk_student_class FOREIGN KEY(s_cid) REFERENCES class(cid) );
INSERT INTO student VALUES(NULL,'王大锤',2) INSERT INTO student VALUES(NULL,'胡汉三',3) //约束生效 插入失败
Fourth method: Add foreign keys after creating the table
CREATE TABLE student(sid INT AUTO_INCREMENT, sname VARCHAR(10) NOT NULL, s_cid INT , PRIMARY KEY(sid) );
//Add foreign key constraints:
ALTER TABLE student ADD FOREIGN KEY (s_cid) REFERENCES class(cid);
//Test
INSERT INTO student VALUES(NULL,'王大锤',2) INSERT INTO student VALUES(NULL,'胡汉三',3) //约束生效 插入失败
The above is the detailed content of What are the methods to add foreign keys in mysql. For more information, please follow other related articles on the PHP Chinese website!