What are the ways to add foreign keys in Mysql?

Several ways to add foreign keys in Mysql

Note: Adding foreign keys is to add from the table (that is, the child table)
The parent table is Main table

Method 1:

Before creating the table:

FOREIGN KEY (child table id) REFERENCES associated table name (foreign master Table id)

For example

  create table emp( 			
  	e_id int auto_increment primary key, 			
  	ename varchar(50) not null, 			
  	age int, 			job varchar(20),
  	salary int, 			
  	entrydate date, 			
  	managerid int, 			
  	dept_id int,
  	FOREIGN KEY (dept_id) REFERENCES dept(id) 	
  );

Method 2:

After creating the table, add:

ALTER TABLE Table 1 ADD FOREIGN KEY (Foreign key of the secondary table) REFERENCES dept (primary key of the primary table);

 例如：
 		ALTER TABLE emp ADD FOREIGN KEY (dep_id) REFERENCES dept(id);

Note:

The primary table (dept) exists in the secondary table (emp), and the secondary table (emp) exists The table references a foreign key and cannot be deleted directly (main table)

DROP TABLE dept; --Delete failure

When deleting a table, if there is a foreign key reference, you must first delete the slave table table (that is, delete all tables with relevant foreign key constraints) before the main table can be deleted.
The above foreign keys are physical foreign keys, which are database-level references and are not recommended. Using this will greatly increase the degree of data coupling.

Primary key:

uniquely identifies a record, cannot be duplicated, and is not allowed to be empty. It is used to ensure data integrity

Foreign key:

is the primary key of another table. The foreign key can have duplicates or null values ​​and is used to establish with other tables. For contact. Therefore, if we talk about foreign keys, it must involve at least two tables.

Note:

1. The foreign key field should be completely consistent with the type of the main table (length, etc.) when it is created. Otherwise, an error will be reported when creating a foreign key;

2. The foreign key table cannot insert data that does not exist in the main table (the data refers to the data with the primary key of the main table or the foreign key of the secondary table). –For example, if the department number is only 1, 2, 3, 4, and the foreign key value is 5 when inserting data from the table, an error will occur
——>That is, the foreign key constraints of the foreign key table cannot be arbitrarily set.

3. When the data of the main table is modified, the foreign key constraints of the associated foreign key table have been automatically modified.

The difference between adding constraints and not adding them

If constraints are not added, the system will automatically generate names for them, which can be difficult to remember. By adding constraint, you can name the constraints according to your own intentions.

Supplement: MySQL delete foreign key operation

1. Display the foreign key to be deleted. show create table table name;

2. See the constraints;

3. Delete them. One way to rewrite it is: Use the alter table command to delete foreign key constraints in the table. The syntax is as follows: ALTER TABLE table name DROP FOREIGN KEY constraint name;

The above is the detailed content of What are the ways to add foreign keys in Mysql?. For more information, please follow other related articles on the PHP Chinese website!