MySQL learning: five major constraints of database tables explained in detail for beginners

1. Constraint concepts and classification

1.1 The concept of constraints:

Limit the data in the table to ensure the correctness, validity and integrity of the data

1.2 Constraint Classification

• 1. Primary key constraint ( primary key): Ensure that the field is non-empty and unique. A table can only have one primary key, which is the unique identifier of the field in the table.
• 2. Non-null constraint ( not null): Ensure that the field cannot be empty
• 3. Unique constraint ( unique): Ensure that the field is unique but can be null
• 4. Foreign key constraints ( foreign key): The primary key or unique key of another table that exists in one table is called the foreign key of this table.
• 5. Default constraints ( default+default value): used to ensure that the field has a default value (enclosed in quotation marks)

2. Adding and removing five constraints

2.1 Six ways to add constraints

1. Add a non-empty constraint when creating a table ( Added as a column-level constraint)

create table + table name(
Column name data type + constraint name
)

2. Add a non-null constraint when creating a table ( Added as table-level constraint)

create table table name(
Column name data type,
...
Column name data type,
(constraint + alias (just give a name to the constraint you added)) Constraint name (corresponding field name)
)

3. When modifying the column name

alter table + table name + change (column) + old column name new column name new column name type + constraint

4. When modifying the data type of a column

alter table + table name + modify + (column) + column name and column type constraints

5. You can add constraints when adding new columns

alter table + table name + add + (column) + new column name + new column name type + constraint

6. Foreign key specific

alter table + table name + add constraint foreign key name (you can give the foreign key a name at will) + foreign key (foreign key field name) references main table name (the field to be associated with the main table)

2.2 Three ways to delete constraints

1. No constraints when modifying column names

alter table + table name + change (column) + old column name new column name type of the new column name

2. No constraints when modifying the data type of a column

alter table + table name + modify + (column) + column name and column type

3. Use drop to delete specific constraints

alter table + table name + drop index/foreign key/primary key + constraint name

Note: View the names of the constraints in the table:

show index from + table name

2.3 Add and delete methods corresponding to the five major constraints (serial numbers correspond to 2.1 and 2.2)

1. Non-null constraint ( not null)

• Corresponding adding method: 1 3 4 5
• Corresponding deletion method: 1 2

2. Unique constraint ( unique)

• Corresponding adding method: 1 2 3 4 5
• Corresponding deletion method: 1 2 3 (corresponding to the third index option)

3. Default constraints ( default)

• Corresponding adding method: 1 3 4 5
• Corresponding deletion method: 1 2

4. Primary Key ( primary key

• Corresponding adding method: 1 2 3 4 5
• Corresponding deletion method: 3 (Note that the name without constraints is added after the primary key option of the third type, because there can only be one primary key in a table)

5. Foreign Key ( foreign key)

• Corresponding adding method: 2 6
• Corresponding deletion method: 3 (corresponding to the third foreign key option)

Let's take a look at an employee table (the fields are employee number, name, age, department, and department location):

We can find that there are redundant parts, that is, the R&D department corresponds to Guangzhou, and the sales department corresponds to Shenzhen. There is no need to write them so many times. In fact, they can be divided into two tables to prevent redundancy. The first table stores employee information, and the other stores department information table 1 (employee table employee):

Table 2 (department table):

The ids of both tables here are primary keys

But there is a problem here, that is, we can delete any row in the department table, such as the row with id=1. Once deleted, the employees with dep_id=1 in table 1 will not be able to find their department information, so we can use foreign keys to solve this problem.

We can specify that dep_id is a foreign key when creating the table, and associate it with the primary key id of department

Statement format (although this is the second method, that is, adding a foreign key with table-level constraints, the syntax is different):

create table table name(
Column name data type,
...
Column name data type,
(constraint + alias (just give a name to the constraint you added)) + foreign key (field name) + references + main table (the corresponding field of the main table)
)

For this example, you can write:

create table employee(
id int primary key,
sname varchar(5),
age int,
dep_id int,
constraint aaa foreign key(dep_id) references department(id)
)

After adding the foreign key, we cannot delete any row in the department table. Also, we cannot fill in dep_id with any number other than 1 or 2 when adding new employee information in employee.

2.4 Summary of creating constraints

When creating a table, adding column-level constraints only supports: default, not empty, primary key, unique. When adding table-level constraints only supports: primary key, unique, foreign key.

2.5 The difference between primary key and unique key

There can be at most one primary key and multiple unique keys. Two columns are allowed to form a primary key and a unique key.

3. Auto-increment column

3.1 Concept

If a column is of numeric type, use auto_increment to achieve automatic value growth (usually used with the primary key)

3.2 Add a primary key constraint when creating a table and complete the example of primary key self-increment

Format:

create table table name(
Column name data type primary key + auto_increment
)

For example:
We create a student table containing student ID and name, and create a statement based on the student ID:

create table student(
id int primary key auto_increment,
sname char(4)
)

If we have a data in the table at this time;

When we insert data again, we don't give the student number.

insert into student value(null, "Li Si")

When I checked the table again, the student number was automatically added to our table based on the auto-increment value:

3.3 Adding and Removing Self-Growth

1. Add (the above adding at the time of creation is one kind)

alter table + table name + modify column + column name type constraint + AUTO_INCREMENT

2. Delete

alter table + table name + modify column column name type

3.4 Setting the auto-increment step size

The starting value of the auto-increment is 1, and the starting value of the step length is 1
The starting value cannot be changed, but the step size can be changed using show VARIABLES LIKE "%auto_increment%"
Check the name of the corresponding step and then use set step to represent the name = the step to be set

The above is the detailed content of the MySQL learning of the five major constraints of database tables for beginners. For more information about the five major constraints of MySQl, please pay attention to other related articles on 123WORDPRESS.COM!