Mysql Study Notes 2 (Table constraints) and mysql Study Notes

Mysql Study Notes 2 (Table constraints) and mysql Study Notes
Operation table Constraints

Integrity is the accuracy and consistency of the index data, and integrity check is to check the data accuracy and consistency. The mysql database management system provides a mechanism to check whether the data in the database meets the specified conditions. To ensure data accuracy and consistency in the database, this mechanism is a constraint.
Integrity constraints supported by mysql

Not null (NK)

DEFAULT

The value of the unique key (UK) Constraint field is UNIQUE.

PRIMATYKEY (PK)

AUTO_INCREMENT

Foreign key (FK)

The check constraint is not supported. You can use the check constraint, but it does not work.

Constraints:

Single Column Constraint

Multi-column Constraints

1. not null (NK)

When creating a table, set the attribute to not null.
Example:
Create table t_name (

Column type not null

)
Create table t_notnull {

Id int not null,

Name varchar (20)

}
Insert into t_notnull (name) values ("dsagdfs ");
The corresponding id is set to 0.
Alter table t_notnull modify id int;
Insert into t_notnull (name) values ("fdgdf ");
The corresponding id is found to be null.

2. DEFAULT

Specify
Create table t_name (

Column type default

)
Example:
Create table t_default (

Id int not null,

Name varchar (20) default 'test'

)
Insert into t_default (id) values (1 );
Insert into t_dedault (name) values ('name ');

3. AUTO_INCREMENT

Create table t_name (

Column type auto_increment

)
Create table t_auto (

Id int primay key auto_increment,

Name varchar (20) default 'test'

)
Note: This column must be specified as the primary key when used. This column is a numerical column.

4. UNIQUE UK

Create table t_name (

Column type unique

)
Create table t_name (

Column type

Constraint uk_column unique (column)

)
Constraint: Constraints
When you set an identifier for a constraint, you are recommended to use the format of "constraint abbreviation _ field name ".
Create table t_unique (

Id int primary key,

Name varchar (20) unique

)
Note:

1. auto_increment can obviously implement unique functions, but it is not unique flexible. unique columns do not have to be primary key columns or numeric columns,
2. varchar must specify the number of bytes; otherwise, it will fail.

5. primary key

Single-field primary key
Create table t_name (

Column type primary key

)
Create table t_name (

Column type

Contraint pk_column primary key (column)

)
Multi-field primary key
Create table t_name (

Column1 type,

Column2 type

...

Contraint pk_column1_column2 primarykey (column1, column2)

)

6. foreign key

The first five constraints are all single tables, and the foreign key constraint involves multiple tables.
A parent-child relationship exists between two tables with foreign key constraints. That is, the value range of a field in the child table is determined by the parent table.
When you set an FK constraint, the field that sets the FK constraint must be dependent on the primary key of an existing parent table in the database, and the foreign key can be null.
Create tablet_name (

Column1 type,

Column2 type,

Constraint fk_column1 foreign key (column1) references tablename (columname)

)
Column1: foreign key, columnname: fields with primary key constraints set in the parent table