Why have multiple watches?
– create an employee table, including the following (id, name, age, dep_name, dep_location),id primary key, automatic growth, and add 5 pieces of data
Code example:

CREATE TABLE emp (
id INT PRIMARY KEY auto_increment,
name VARCHAR(40),
age int,
dep_name VARCHAR(40),
dep_location VARCHAR(30)
);


INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('Zhang San', 20, 'R & D department','Guangzhou');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('Li Si', 21, 'R & D department','Guangzhou');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('Wang Wu', 20, 'R & D department','Guangzhou');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('Lao Wang', 20, 'Sales Department','Shenzhen');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('king', 22, 'Sales Department','Shenzhen');
INSERT INTO emp (name, age, dep_name, dep_location) VALUES ('Xiao Wang', 18, 'Sales Department','Shenzhen');

– disadvantages of a single table: there are many duplicate data (data redundancy) in the table. If you want to modify the address of the R & D department, you need to modify three places.

Solution: divide one table into two tables (employee table and department table)
Code example:

-- Create department table
CREATE TABLE department(
id INT PRIMARY KEY auto_increment,
dep_name VARCHAR(40),
dep_location VARCHAR(30)
);

-- Create employee table
CREATE TABLE employee(
id INT PRIMARY KEY auto_increment,
name VARCHAR(40),
age INT,
dep_id INT
);

-- Add two departments
INSERT INTO department(dep_name , dep_location) VALUES('R & D department' , 'Guangzhou');
INSERT INTO department(dep_name , dep_location) VALUES('Sales Department' , 'Shenzhen');

-- Add employee
INSERT INTO employee (name, age, dep_id) VALUES
('Zhang San', 20, 1),
('Li Si', 21, 1),
('Wang Wu', 20, 1),
('Lao Wang', 20, 2),
('king', 22, 2),
('Xiao Wang', 18, 2);

Question: when we are in employee dep_ If you enter a nonexistent department in the ID, the data can still be added, but there is no corresponding department
Door, this can't happen. employee dep_ The content in id can only be the id existing in the department table

**Objective: * * dep needs to be constrained_ The value of the id field can only be an id that already exists in the department table. Solution: use a foreign key constraint

Foreign key constraint
Used to maintain the relationship between multiple tables
Foreign key: the value of a field in the slave table refers to the value of the primary key in the primary table. Primary table: table constrained by others, secondary table / slave table: table constrained by others
As shown below:

**Foreign key syntax
Add foreign key
1. Add a foreign key when creating a new table:
[CONSTRAINT] [foreign key CONSTRAINT name] foreign key (foreign key field name) REFERENCES main table name (primary key field name)
Keyword explanation:
CONSTRAINT – CONSTRAINT keyword
Foreign key – a field is used as a foreign key
REFERENCES – primary table name (primary key field name) refers to a field in the reference primary table
2. Add foreign keys to existing tables:
ALTER TABLE adds [constraint] [FOREIGN KEY constraint name] FOREIGN KEY from the table name
REFERENCES main table (primary key field name);

Add foreign key to existing table:
Code example:

-- Add foreign key to existing table
ALTER TABLE employee ADD  CONSTRAINT dep_exm_fk1 FOREIGN KEY(dep_id) REFERENCES department(id);

-- Delete foreign key
ALTER TABLE employee DROP FOREIGN KEY dep_exm_fk1;

Add foreign keys when creating a new table:
Code example:

-- Syntax:	
-- 		[CONSTRAINT] [Foreign key constraint name] FOREIGN KEY (Foreign key field name) REFERENCES Main table(Primary key field name);

-- Create employee table
CREATE TABLE employee(
id INT PRIMARY KEY auto_increment,
name VARCHAR(40),
age INT,
dep_id INT,
CONSTRAINT dep_exm_fk1 FOREIGN KEY(dep_id) REFERENCES department(id)
);

-- Delete foreign key
alter table employee drop foreign key dep_exm_fk1;

– cascade operation
– when modifying and deleting the primary key, update or delete the foreign key value of the secondary table at the same time, which is called cascading operation
– syntax:
– on update cascade updates. When the primary key of the primary table is updated, the foreign key will also be updated
– on delete cascade deletion. When the primary key of the primary table is deleted, the foreign key will also be deleted

-- Add foreign keys and set foreign key cascade update and cascade deletion
alter table employee add constraint dep_emp_fk1 foreign key(dep_id) references department(id) on update cascade on delete cascade; 
 
--  Modify Sales Department id For 5
update department set id = 5 where id = 2;

delete from department where id = 5 ;