How to delete foreign key relationship in mysql

In mysql, you can use the ALTER TABLE statement with the DROP keyword to delete foreign key relationships (constraints). The syntax is "ALTER TABLE data table name DROP FOREIGN KEY foreign key constraint name;"; "ALTER TABLE" statement It is used to modify the table (change the structure of the original table). After adding the "DROP FOREIGN KEY" statement, the modification function is limited to deleting foreign key relationships (constraints).

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Foreign key relationship (foreign key constraint) is a special field of the table, often used together with primary key constraints. For two tables with an associated relationship, the table where the primary key in the associated field is located is the primary table (parent table), and the table where the foreign key is located is the secondary table (child table).

Foreign keys are used to establish the association between the master table and the slave table, establish a connection for the data in the two tables, and constrain the consistency and integrity of the data in the two tables. For example, a fruit stall only has four kinds of fruits: apples, peaches, plums, and watermelons. Then, when you come to the fruit stall to buy fruits, you can only choose apples, peaches, plums, and watermelons. Other fruits are not available for purchase.

When a record is deleted from the main table, the corresponding record from the table must also be changed accordingly. A table can have one or more foreign keys, and the foreign key can be null. If it is not null, the value of each foreign key must be equal to a certain value of the primary key in the main table.

When a foreign key constraint is not required in a table, it needs to be deleted from the table. Once the foreign key is deleted, the association between the master table and the slave table will be released.

So how does mysql delete foreign key relationships (constraints)?

In mysql, you can use the ALTER TABLE statement with the DROP keyword to delete foreign key relationships (constraints).

The syntax format for deleting foreign key constraints is as follows:

ALTER TABLE 数据表名 DROP FOREIGN KEY 外键约束名;

• ALTER TABLE statement is used to change the structure of the original table, such as adding or deleting columns, changing Original column type, renamed column or table, etc.

• DROP FOREIGN KEY statement is used to delete foreign key relationships

Example:

Use the following Statement to view the foreign key constraints of the data table tb_emp2:

SHOW CREATE TABLE tb_emp2\G

mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.12 sec)

Delete the foreign key constraints fk_tb_dept1

ALTER TABLE tb_emp2 DROP FOREIGN KEY fk_tb_dept1;

mysql> ALTER TABLE tb_emp2
    -> DROP FOREIGN KEY fk_tb_dept1;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to delete foreign key relationship in mysql. For more information, please follow other related articles on the PHP Chinese website!