Examples of common operations on MySQL foreign key constraints [view, add, modify, delete]

This article uses examples to describe common operations on MySQL foreign key constraints. Share with you for your reference, the details are as follows:

1. View the SQL statement for creating the database table

show create table vip

2. View the constraint name of the foreign key

CREATE TABLE `vip` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `address` varchar(255) DEFAULT NULL,
 `code` varchar(255) DEFAULT NULL,
 `mobile` varchar(255) DEFAULT NULL,
 `name` varchar(255) DEFAULT NULL,
 `score` int(11) DEFAULT NULL,
 `id_code` varchar(255) DEFAULT NULL,
 `user_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `FK1C81D1738DA76` (`user_id`),
 CONSTRAINT `FK1C81D1738DA76` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

3. Remove foreign key constraints

alter table vip drop foreign key FK1C81D1738DA76

4. Delete foreign keys

alter table vip drop user_id

5. Add foreign key constraints

ALTER TABLE `tfeedbackmessage`
ADD CONSTRAINT `FK_i1q2cf5pxfr8r69cfci3yyari` FOREIGN KEY (`HANDLERID`) REFERENCES `toperationuser` (`FID`) 
ON DELETE CASCADE ON UPDATE RESTRICT;

Summary of common operations:

View the field information of the table: desc table name;

View all information of the table: show create table table name;

Add a primary key constraint: alter table table name add constraint primary key (such as: PK_table name) primary key table name (primary key field);

Add foreign key constraint: alter table add constraint foreign key from table (such as: FK_from table_primary table) foreign key from table (foreign key field) references primary table (primary key field);

Delete the primary key constraint: alter table table name drop primary key;

Delete foreign key constraints: alter table table name drop foreign key foreign key (case sensitive);

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL log operation skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.