What are the methods to add foreign keys in mysql

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!