MySQL database Constraints

MySQL database Constraints

Database constraints:
A constraint is a data validation rule enforced on a table. It is mainly used to ensure data integrity in the database.
In addition, when the data in the table is mutually dependent, the related data is not deleted.

Constraints on data columns can be divided into the following two types:
Single Column constraint: each constraint only limits one column.
Multi-column constraints: each constraint can constrain multiple data Columns

There are two opportunities to specify constraints for a data table:
Specify constraints for corresponding data columns when creating a table
After creating a table, add constraints by modifying the table.

Most databases support the following five integrity constraints:
1. not null: non-NULL constraint. A specified Column cannot be blank. It can only be used as a column-level constraint and can only be defined using the column-level constraint syntax.
The null Value in SQL is case-insensitive and has the following features:
Values of all data types can be null, including int, float, and boolean.
A null string is not equal to null, and 0 is not equal to null.

When creating a table, set a non-null constraint for the specified column. You only need to add not null after column definition. For example:
Create table t_test
(
Id int not null,
Name varchar (255) default 'xyz' not null,
Gender varchar (2) null
);
When you use alter table to modify a table, add or delete non-null constraints. For example:

# Add a non-empty Constraint
Alter table t_test modify gender varchar (2) not null;

# Cancel non-empty Constraints
Alter table t_test modify gender varchar (2) null;

# Cancel non-null constraints and specify the default value
Alter table t_test modify name varchar (255) default 'abc' null;

2. UNIQUE: UNIQUE constraint. The combination of specified columns or columns cannot be repeated.
Although duplicate values cannot appear in columns with unique constraints, multiple null values can appear because null in the database is not equal to null.

You can create multiple unique constraints in the same table. The unique constraint can also be composed of multiple columns. When you create a unique constraint for a column, MySQL creates a unique index for the column. If you do not name the unique constraint, the unique constraint is the same as the column name by default.

Unique constraints can be created using column-level syntax or table-level syntax. If you create a composite constraint for multiple columns or specify a constraint name for the constraint, you can only use table-level syntax.

To use the column-level syntax to create a unique constraint, you only need to add the unique keyword after the column definition:

# Create a unique constraint when creating a table using column-level syntax
Create table unique_test
(
# Create a non-null constraint, meaning the id cannot be null
Id int not null,
# Creating a unique constraint means that the column values of multiple rows of data cannot be equal
Name varchar (255) unique
);

Table-level syntax:

[Constraint name] unique (column name [, column name,...])
The preceding table-level constraint syntax format can be placed in the create table statement in parallel with the column definition, or in the alter table statement using the add keyword to add:

# Create a unique constraint using table-level syntax when creating a table
Create table uniques_test2
(
# Create a non-null constraint for id, meaning id cannot be null
Id int not null,
Name varchar (255 ),
Pass varchar (255 ),
# Use the table-level syntax to create a unique constraint for name
Unique (name ),
# Use the table-level syntax to create a unique constraint for pass and specify the constraint name test2_uk.
Constraint test2_uk unique (pass)
);

# When creating a table, use the table-level syntax to create a unique constraint for the composite Column
Create table unique_test3
(
Id int not null,
Name varchar (255 ),
Pass varchar (255 ),
# The combination of the specified name and pass Columns cannot be repeated.
Constraint test3_uk unique (name, pass)
);

# Use the add keyword to add a unique constraint when modifying a table
Alter table unique_test3 add unique (id, name );

# When modifying a table, use the modify keyword to set unique constraints for a single column
Alter table unique_test3 modify name varchar (100) unique;

Delete a unique constraint in MySQL:
Syntax format:

Alter table tableName drop index constraint name;
Example:

# Delete the unique constraint test3_uk in the unique_test3 table
Alter table unique_test3 drop index test3_uk;
 

3. primary key: primary key constraint. The value of this column can uniquely identify this record.

4. foreign key: foreign key constraint, which specifies that the row record belongs to a record in the master table and is mainly used to ensure the integrity of the reference.

5. CHECK (not supported by MySQL): Checks constraints and specifies a Boolean expression to specify that the values of the corresponding columns must meet this expression.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151362.htm