MySQL advanced 1 -- constraints

1. Restraint

1.1 concept

  • Constraints are rules that act on columns in a table and are used to restrict the data added to the table
    For example, we can restrict the id column so that its value cannot be repeated and cannot be null.

  • The existence of constraints ensures the correctness, effectiveness and integrity of data in the database
    Adding constraints can limit incorrect data when adding data. The age is 3000 and the math score is - 5. This kind of invalid data can then ensure the integrity of the data.

1.2 classification

  • Non NULL constraint: the keyword is NOT NULL

    Ensure that all data in the column cannot have null values.
    For example, the id column cannot be added successfully when the data of Ma Huatong is added.

  • UNIQUE constraint: the keyword is UNIQUE

    Ensure that all data in the column is different.
    For example, the value of three pieces of data in the id column is 1. Such data is absolutely not allowed to be added.

  • PRIMARY KEY constraint: the keyword is PRIMARY KEY

    The primary key is the unique identifier of a row of data. It must be non empty and unique. Generally, we will add a primary key column to each table to uniquely identify the data.
    For example, the id in the chart above can be used as the primary key to identify each data. In this way, the value of id in the data cannot be repeated and cannot be null.

  • CHECK constraints: the keyword is CHECK

    Ensure that the values in the column meet a certain condition.
    For example, we can add a range to the age column. The minimum age can be set to 1 and the maximum age can be set to 300. Only in this way can the data be more reasonable.

    Note: MySQL does not support checking constraints.

    Is there no way to ensure that the age is within the specified range? It cannot be guaranteed from the database level. It can be limited in java code in the future, and the requirements can also be realized.

  • DEFAULT constraint: the keyword is DEFAULT

    When saving data, if no value is specified, the default value is adopted.
    For example, when we add this constraint to the english column, we specify that the default value is 0, so when we add data without specifying a specific value, the default given 0 will be adopted.

  • FOREIGN KEY constraint: the keyword is FOREIGN KEY

    Foreign keys are used to establish links between the data of two tables to ensure the consistency and integrity of the data.
    Foreign key constraints may not be easy to understand now. We will focus on them later.

1.3 non null constraints

  • concept
    Non NULL constraints are used to ensure that all data in a column cannot have NULL values

  • grammar

  • Add constraint

-- Add non empty constraint when creating table
CREATE TABLE Table name(
   Column name data type NOT NULL,
   ...
); 

-- Add a non empty constraint after the table is created
ALTER TABLE Table name MODIFY Field name data type NOT NULL;
  • Delete constraint
ALTER TABLE Table name MODIFY Field name data type;

1.4 unique constraints

  • concept
    Unique constraints are used to ensure that all data in the column is different
  • grammar
    • Add constraint
-- Add unique constraints when creating tables
CREATE TABLE Table name(
   Column name data type UNIQUE [AUTO_INCREMENT],
   -- AUTO_INCREMENT: Automatically grow when no value is specified
   ...
); 
CREATE TABLE Table name(
   Column name data type,
   ...
   [CONSTRAINT] [Constraint name] UNIQUE(Listing)
); 

-- Add unique constraint after creating table
ALTER TABLE Table name MODIFY Field name data type UNIQUE;
  • Delete constraint
ALTER TABLE Table name DROP INDEX Field name;

1.5 primary key constraints

  • concept

    The primary key is the unique identifier of a row of data. It must be non empty and unique

    A table can only have one primary key

  • grammar

    • Add constraint
-- Add primary key constraint when creating table
CREATE TABLE Table name(
   Column name data type PRIMARY KEY [AUTO_INCREMENT],
   ...
); 
CREATE TABLE Table name(
   Column name data type,
   [CONSTRAINT] [Constraint name] PRIMARY KEY(Listing)
); 


-- Add primary key constraint after creating the table
ALTER TABLE Table name ADD PRIMARY KEY(Field name);
  • Delete constraint
ALTER TABLE Table name DROP PRIMARY KEY;

1.6 default constraints

  • concept

    When saving data, if no value is specified, the default value is adopted

  • grammar

    • Add constraint
-- Add default constraints when creating tables
CREATE TABLE Table name(   
	Column name data type DEFAULT Default value,   ...
	); 

-- Add default constraints after creating the table
ALTER TABLE Table name ALTER Listing SET DEFAULT Default value;
  • Delete constraint
ALTER TABLE Table name ALTER Listing DROP DEFAULT;

1.7 restraint exercise

Add appropriate constraints to the table as needed

-- Employee table
CREATE TABLE emp (
	id INT,  -- staff id，Primary key and self growth
    ename VARCHAR(50), -- Employee name, non empty and unique
    joindate DATE,  -- Entry date; not blank
    salary DOUBLE(7,2),  -- Salary, non empty
    bonus DOUBLE(7,2)  -- Bonus, if not nearly, the default is 0
);

The specific requirements must be given above. We can create this table according to the requirements and add corresponding constraints for each column. The table creation statement is as follows:

DROP TABLE IF EXISTS emp;

-- Employee table
CREATE TABLE emp (
  id INT PRIMARY KEY, -- staff id，Primary key and self growth
  ename VARCHAR(50) NOT NULL UNIQUE, -- Employee name, non empty and unique
  joindate DATE NOT NULL , -- Entry date; not blank
  salary DOUBLE(7,2) NOT NULL , -- Salary, non empty
  bonus DOUBLE(7,2) DEFAULT 0 -- Bonus. If there is no bonus, it defaults to 0
);

Through the above statement, you can create an emp table with constraints. Can constraints work. Next, let's verify one by one. First, add a piece of data that has no problem:

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'Zhang San','1999-11-11',8800,5000);
  • Verify that the primary key constraint is non empty and unique
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'Zhang San','1999-11-11',8800,5000);

Through the above statement, you can create an emp table with constraints. Can constraints work. Next, let's verify one by one and add a piece of data without problems

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'Zhang San','1999-11-11',8800,5000);
  • Verify that the primary key constraint is non empty and unique
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'Zhang San','1999-11-11',8800,5000);

Error message:

As can be seen from the above results, the field id cannot be null. Let's add another piece of data as follows:

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'Zhang San','1999-11-11',8800,5000);

The results are as follows:

As can be seen from the above results, the value of 1 is repeated. Therefore, the primary key constraint is used to limit the non empty and unique data. Then let's add another piece of data that meets the requirements

INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'Li Si','1999-11-11',8800,5000);

The results are as follows:

  • Verify non empty constraints
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);


From the above results, we can see that the non NULL constraint of the ename field has taken effect.

  • Verify unique constraints
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'Li Si','1999-11-11',8800,5000);

Execution result:

As can be seen from the above results, the unique constraint of the ename field has taken effect.

  • Verify default constraints
INSERT INTO emp(id,ename,joindate,salary) values(3,'Wang Wu','1999-11-11',8800);

Operation results:

After executing the above statement, query the data in the table, as shown in the figure below. You can see that the bonus column of Wang Wu has the default value of 0.
Note: the default constraint will only adopt the default value if no value is given. If NULL is given, the value is null.

INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'Zhao Liu','1999-11-11',8800,null);

After executing the above statement, query the data in the table, as shown in the figure below. You can see that the value of the bonus column of Zhao Liu's data is null.

  • Verify automatic growth: auto_increment when the column is numeric and uniquely constrained
-- Employee table
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- staff id，Primary key and self growth
  ename VARCHAR(50) NOT NULL UNIQUE, -- Employee name, non empty and unique
  joindate DATE NOT NULL , -- Entry date; not blank
  salary DOUBLE(7,2) NOT NULL , -- Salary, non empty
  bonus DOUBLE(7,2) DEFAULT 0 -- Bonus. If there is no bonus, it defaults to 0
);

Next, add data to emp to verify whether the value of id column will increase automatically if no value is added to id column and null value is added to id column:

INSERT INTO emp(ename,joindate,salary,bonus) values('Zhao Liu','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'Zhao Liu2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'Zhao liu3','1999-11-11',8800,null);

1.8 foreign key constraints

1.8.1 general

Foreign keys are used to establish links between the data of two tables to ensure the consistency and integrity of the data.
How to understand the above concept? As shown in the figure below, there are two tables, employee table and department table:

DEP in employee table_ The id field is associated with the id field of the Department table, that is, student No. 1 Zhang San belongs to the employee of the R & D Department of department No. 1. Now I want to delete Department 1, and there will be wrong data (the data belonging to department 1 in the employee table). The relationship between the two tables mentioned above is only that we think they are related. At this time, we need to use foreign keys to make the two tables generate database level relationship. In this way, the data of department 1 in the Department table you want to delete cannot be deleted.

1.8.2 grammar

  • Add foreign key constraint
-- Add foreign key constraints when creating tables
CREATE TABLE Table name(
   Column name data type,
   ...
   [CONSTRAINT] [Foreign key name] FOREIGN KEY(Foreign key column name) REFERENCES Main table(Main table column name) 
); 

-- Add foreign key constraints after creating the table
ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) REFERENCES Main table name(Main table column name);
  • Delete foreign key constraint
ALTER TABLE Table name DROP FOREIGN KEY Foreign key name;

1.8.3 practice

Create employee table and department table according to the above syntax, and add foreign key constraints:

-- Delete table
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- Department table
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- Employee table 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- Add foreign key dep_id,relation dept Tabular id Primary key
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
);

Add data

-- Add 2 departments
insert into dept(dep_name,addr) values
('R & D department','Guangzhou'),('Sales Department', 'Shenzhen');

-- Add employee,dep_id Indicates the Department of the employee
INSERT INTO emp (NAME, age, dep_id) VALUES 
('Zhang San', 20, 1),
('Li Si', 20, 1),
('Wang Wu', 20, 1),
('Zhao Liu', 20, 2),
('Sun Qi', 22, 2),
('Zhou Ba', 18, 2);

If you delete the data of the R & D department, you will find that it cannot be deleted.

Delete foreign key

alter table emp drop FOREIGN key fk_emp_dept;

Add foreign keys again

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

reference resources

Dark horse programmer

Keywords: Java Database MySQL SQL

Added by iRock on Tue, 08 Mar 2022 17:40:06 +0200

Popular Keywords

©2024 Programming VIP