1. An overview of Database Constraints
1.1 Role of constraints:
Restrict the data in the table to ensure the correctness, validity and integrity of the data.If a table has constraints added, incorrect data cannot be inserted into the table.Constraints are appropriate to add when creating tables.
1.2 Constraint types:
Constraint Name Constraint keywords Characteristic Primary key primary key Ensure that the column field is used to query for unique data Only unique Ensure the uniqueness of the column data Non-empty not null Ensure that the column data cannot be null foreign key foreign key Designing multiple tables, data from subtables, dependent on the main table; default default If the column data does not contain data, then there is a default value; timestamp, when adding data, is null, then defaults to the current system time Check Constraints check Note: mysql is not supported and will be explained later in the oracle summary When inserting or modifying data, sql checks for compliance2. Primary Key Constraints
2.1 Role of Primary Key
Used to uniquely identify each record in the database
2.2 Which field should be the primary key for the table?
Normally, instead of using business fields as primary keys, an id field is designed for each table separately, with id as the primary key.
Primary keys are for databases and programs, not for the final customer.So it doesn't matter whether the primary key has meaning or not, as long as it doesn't repeat, it's not empty.
For example: ID card, school number is not recommended as primary key
2.3 Create Primary Key
-
Keyword: primary key
-
Characteristic:
- Non-empty not null
- unique
-
Creation Method
-- Create a table of students st5, Include field(id, name, age)take id Be the primary key create table st5 ( id int primary key, -- id Is Primary Key name varchar(20), age int ); desc st5;
-- Insert duplicate primary key values insert into st5 values (1, 'Guan Yu', 30); -- Error code: 1062 Duplicate entry '1' for key 'PRIMARY' insert into st5 values (1, 'Guan Yunchang', 20); select * from st5; -- insert NULL Primary key value, Column 'id' cannot be null insert into st5 values (null, 'Guan Yunchang', 20);
2.4 Delete primary key
-- delete st5 Primary Key alter table st5 drop primary key; -- Add Primary Key alter table st5 add primary key(id);
2.5 Primary key self-increasing
Primary keys If we let ourselves add duplicates, we usually want the database to automatically generate the values of the primary key field each time a new record is inserted
auto_increment means automatic growth (field type must be integer type)
-- insert data insert into st6 (name,age) values ('Little Joe',18); insert into st6 (name,age) values ('Jo',20); -- Another way of writing insert into st6 values(null,'Zhou Yu',35); select * from st6;
2.6 Modify the starting value of the self-increasing default value
Default AUTO_The starting value of INCREMENT is 1. If you want to modify the starting value, use the following SQL syntax
- Specify start value when creating table
-- Specify a starting value of 1000 create table st4 ( id int primary key auto_increment, name varchar(20) ) auto_increment = 1000; insert into st4 values (null, 'Kong Ming'); select * from st4;
- Modify the starting value after creating the table
alter table st4 auto_increment = 2000; insert into st4 values (null, 'Liu Bei');
2.7 Effects of delete and truncate on self-growth
- delete: Deleting all records has no effect on self-growth.
- truncate: After deletion, growth starts again.
3. Unique Constraints
What is a unique constraint: A column in a table cannot have duplicate values
3.1 Basic format for unique constraints
Field name field type unique3.2 Implement Unique Constraints
-- Create Student Table st7, Include field(id, name),name This column sets unique constraints,No student with the same name can appear create table st7( id int, name varchar(20) unique ); -- Add a student insert into st7 values(1,'Zhang San'); select * from st7; -- Duplicate entry 'Zhang San' for key 'name' insert into st7 values (2, 'Zhang San');
- What about inserting multiple null s?
insert into st7 values (2, null); insert into st7 values (3, null); select * from st7;
4. Non-null constraints
What is a non-null constraint: A column cannot be null.
4.1 Basic grammar formats for non-empty constraints
Field name field type not null-- Create a table of students st8, Include field(id,name,gender)among name Cannot be NULL create table st8 ( id int, name varchar(20) not null, gender char(1) ); -- Add a record with no name assignment insert into st8 values (1,'Zhang Sanlun','male'); select * from st8; -- Column 'name' cannot be null insert into st8 values (2,null,'male');
5. Defaults
What is the default value: When a column does not add data, use the default data
Field name field type default default-- Create a student table st9，Include field(id,name,address)， Address default is Guangzhou create table st9 ( id int, name varchar(20), address varchar(20) default 'Guangzhou' ); -- Add a record,Use default address insert into st9 values (1, 'Li Si', default); select * from st9; insert into st9 (id,name) values (2, 'Li Bai'); -- Add a record,Do not use default address insert into st9 values (3, 'minister of geology', 'Shenzhen');
- If a field has a non-null and unique constraint set, what is the difference between the field and the primary key?
- 1) The number of primary keys in a table can only have one.Multiple primary keys cannot appear.Primary keys can be single or multiple columns.
- 2) Self-growth can only be used on primary keys.
6. Foreign Key Constraints
6.1 Disadvantages of Single Table
- Create an employee table with the following columns (id, name, age, dep_name, dep_location),id primary key and autogrowth, add 5 pieces of data
CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_name VARCHAR(30), dep_location VARCHAR(30) ); -- Add data 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 ('King Five', 20, 'R&D Department', 'Guangzhou'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('King', 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 ('Xiaowang', 18, 'Sales Department', 'Shenzhen');
- Disadvantages of the above data tables:
-
- data redundancy
-
- Additions, deletions and changes will occur later
-
6.2 Solution
-- Solution: Divide into 2 tables -- Create Department Table(id,dep_name,dep_location) -- One side, main table create table department( id int primary key auto_increment, dep_name varchar(20), dep_location varchar(20) ); -- Create employee table(id,name,age,dep_id) -- Multi-party, from table create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int -- Foreign keys correspond to the primary key of the primary table ); -- Add 2 Departments insert into department values(null, 'R&D Department','Guangzhou'),(null, 'Sales Department', 'Shenzhen'); select * from department; -- Add Employee,dep_id Indicates the Department where the employee is located INSERT INTO employee (NAME, age, dep_id) VALUES ('Zhang San', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Li Si', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('King Five', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('King', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('king', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('Xiaowang', 18, 2); select * from employee;
- Question:
When we are at dep_in employeeIf you enter a department that does not exist in the id, the data can still be added. However, there is no corresponding department, and this can not happen in practice.Dep_of employeeData in ID can only be IDs that exist in the Department table 
-
Target: Dep_needs to be constrainedID can only be an id that already exists in the department table
-
Solution: Use foreign key constraints
6.3 What are foreign key constraints
- What is a foreign key: The column in the slave table that corresponds to the main table.For example: dep_in the employee tableID
- Main table: One side, table used to constrain others
- From table: A table that is multi-party and constrained by others.
6.4 Syntax for creating constraints
- Add foreign keys when creating new tables:
- Foreign keys have been added to existing tables:
- Operation:
-- 1) Delete Subtable/From Table employee drop table employee; -- 2) Create Slave Table employee And add foreign key constraints emp_depid_fk -- Multi-party, from table create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- Foreign keys correspond to the primary key of the primary table -- Create Foreign Key Constraints constraint emp_depid_fk foreign key (dep_id) references department(id) ); -- 3) Add data normally INSERT INTO employee (NAME, age, dep_id) VALUES ('Zhang San', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Li Si', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('King Five', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('King', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('king', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('Xiaowang', 18, 2); select * from employee; -- 4) Failed to add Department error data -- Insert non-existent Department -- Cannot add or update a child row: a foreign key constraint fails INSERT INTO employee (NAME, age, dep_id) VALUES ('Lao Zhang', 18, 6);
6.5 Delete foreign keys
alter table drops foreign key foreign key name from table;-- delete employee Table emp_depid_fk foreign key alter table employee drop foreign key emp_depid_fk; -- stay employee Add a foreign key if the table exists -- From Table employee Constraint Name emp_depid_fk From table field dep_id Main table department Main Table Fields id alter table employee add constraint emp_depid_fk foreign key (dep_id) references department(id);
Cascade of 6.6 Foreign Keys
- A new problem has occurred:
select * from employee; select * from department; -- To add to the Department table id Value 2, change to 5, can you update directly? -- Cannot delete or update a parent row: a foreign key constraint fails update department set id=5 where id=2; -- To delete Department id Department equal to 1, Can you delete it directly? -- Cannot delete or update a parent row: a foreign key constraint fails delete from department where id=1;
- What is a cascade operation:
When modifying and deleting the primary key of a primary table, updating or deleting the foreign key value of a secondary table at the same time is called a cascade operation
Cascading operation syntax describe ON UPDATE CASCADE Cascade updates can only be created when a table is created.Update the primary key in the main table and automatically synchronize updates from foreign key columns in the table ON DELETE CASCADE cascading deletion-- delete employee Table, recreate employee Table, add cascading updates and deletes drop table employee; create table employee( id int primary key auto_increment, name varchar(20), age int, dep_id int, -- Foreign keys correspond to the primary key of the primary table -- Create Foreign Key Constraints constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade ) -- Add data to the employee and department tables again INSERT INTO employee (NAME, age, dep_id) VALUES ('Zhang San', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('Li Si', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('King Five', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('King', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('king', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('Xiaowang', 18, 2); -- Delete Department table?Can you delete it directly? drop table department; -- Put department table in id Change Department equal to 1 to id Equals 10 update department set id=10 where id=1; select * from employee; select * from department; -- Delete department number is 2 Department delete from department where id=2;