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 ;