MySQL Summary Database Table Constraints - Detailed

Constraints on database tables

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 compliance

2. 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

/* 
Add primary keys to fields when creating tables
 --Format: Field name field type primary key
*/
/*
Add Primary Key to Existing Table
 -- alter table table table name add primary key (field name);
*/
-- 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
--Format:
create table table table name (
  Column name int primary key auto_increment
 ) auto_increment = start value;
-- 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
--Format:
alter table table name auto_increment = start value;
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 unique

3.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:
      1. data redundancy
      1. 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:
[constraint] [foreign key constraint name] foreign key (foreign key field name) references primary table name (primary key field name)
  • Foreign keys have been added to existing tables:
alter table adds [constraint] [foreign key constraint name] foreign key (foreign key field name) references primary table (primary key field name)
  • 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;

7. Summary of data constraints

Constraint Name Constraint keywords describe
Primary key primary key Unique, not empty
Only unique This column cannot have duplicate values
Non-empty not null This column must have a value
foreign key foreign key Primary key column in primary table, foreign key column in secondary table
default default If a column has no value, use the default value
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 compliance

OK, that's it

Tags: Database SQL MySQL Oracle

Posted on Tue, 26 May 2020 20:25:55 -0400 by Alk3m1st