What are the methods to add foreign keys in mysql-Mysql Tutorial-php.cn

What are the methods to add foreign keys in mysql

清浅

Release： 2020-09-15 11:49:44

Original

28271 people have browsed it

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

What are the methods to add foreign keys in mysql

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,'六年级二班')

Copy after login

First method: Add

CREATE TABLE student(sid INT AUTO_INCREMENT,
sname VARCHAR(10) NOT NULL,
s_cid INT REFERENCES class(cid),
PRIMARY KEY(sid)
);

Copy after login

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)　　　　　//不敢相信，在测试一次

Copy after login

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)
);

Copy after login

//Insert data and test it

INSERT INTO student VALUES(NULL,'王大锤',2)
INSERT INTO student VALUES(NULL,'胡汉三',3)　　　//约束生效　　插入失败

Copy after login

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)
);

Copy after login

INSERT INTO student VALUES(NULL,'王大锤',2)
INSERT INTO student VALUES(NULL,'胡汉三',3)　　//约束生效　　插入失败

Copy after login

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)
);

Copy after login

//Add foreign key constraints:

ALTER TABLE student ADD FOREIGN KEY (s_cid) REFERENCES class(cid);

Copy after login

//Test

INSERT INTO student VALUES(NULL,'王大锤',2)
INSERT INTO student VALUES(NULL,'胡汉三',3)　　//约束生效 　　插入失败

Copy after login

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!