MySQL FOREIGN key implementation

1. Create a departmental table

CREATE TABLE DEPT1 (
ID Int (one) not NULL auto_increment,
Name varchar (NOT NULL),
Description varchar (100),
Primary KEY (ID)
);

2. Create an Employee table

CREATE TABLE EMP1 (
ID Int (one) not NULL auto_increment,
Name varchar (20),
Gendar char (2),
Salary float (10,2),
Age int (2),
GMR Int (11),
dept_id Int (11),
Primary KEY (ID)
);

3, establish the primary key, the connection between the foreign key

ALTER TABLE EMP1 add foreign key (dept_id) references dept1 (ID);

4. View Desc EMP1

One more mul means the primary key is good.

Insert data, insert multiple data at the same time

INSERT into DEPT1 values (' 1 ', ' small white ', ' technical Department '), (' 2 ', ' Little Black ', ' tour guide '), (' 3 ', ' brother ', ' Happy Department ');

------------------------------

Navicate

1. How to create a table with a statement

Select database name → click query → new query

The input statement can be saved

2. How to query foreign keys with statements

Select a table → double-click → file → design table → foreign key to view.

To view the name of a foreign key by using a statement:

Show CREATE table emp1;

or select a table in Navicat → Right-click Object Properties →ddl

Delete foreign key

Format

ALTER TABLE name drop FOREIGN key foreign key name

ALTER TABLE EMP1 drop foreign key emp1_ibfk_1;

MySQL FOREIGN key implementation