Mysql field constraints

Mysql field constraints
Field constraints are used to ensure data integrity (reliability and accuracy ). 1 primary key constraint
1> Primary Key introduction:
A primary key is a table flag column. One or more fields can be used as a primary key in mysql. Relational databases depend on primary keys. Primary keys have only two physical purposes:
A unique flag record is a valid object that can be referenced by a foreign key. 2> set the primary key:
Primary keys are divided into single-word primary keys and composite primary keys.
To create a single-word primary key, you only need to add the primary key behind the field.

As shown in, create a student table in the mysqltest database and set the student's sid column to a single-word primary key.
Create a composite primary key:

If a table is created without a primary key, add a primary key to it:
Alter table table_name add primary key (field name );

For example, set gid as the primary key for an existing good table.
3> modify the primary key:
There are two possibilities for modifying the primary key: one is that the table has a primary key, and the primary key needs to be modified on other fields (such as adding a primary key to the good table above), and the other is that the table does not have a primary key, you need to modify the field type to make it a primary key. Type modification requires the modify keyword.
For tables with primary keys already set, modification of primary keys cannot be directly performed. You must first Delete the original primary key and then reset the primary key. Deleting a primary key only deletes the primary key constraint of a specified field or field group, and cannot delete a field or field group. A table can only have one primary key.
The book table created in the front has a primary key field group (bnum and typeid). Now, the original primary key in the table is deleted and the field bnum is changed to the primary key.
Delete primary key: alter table table_name drZ partition? Http://www.bkjia.com/kf/ware/vc/ "target =" _ blank "class =" keylink "> vcCBwcmltYXJ5IGtleTs8YnIgLz4NCjxpbWcgYWx0PQ =" modify primary keys "src =" http://www.bkjia.com/uploads/allimg/160420/040K21550-3.png "title =" \ "/>

2 foreign key constraints

1> foreign key Introduction
The foreign key records the relationship between fields in the table. The foreign key associates fields in different tables to associate the data during modification and deletion, and maintains the data integrity during data operations.
The foreign key has two functions:

Allow data to use foreign keys to ensure data integrity and consistency, and increase the readability of database table relationships.
The definition of foreign keys must also follow the following conditions: All Tables must be InnoDB-type and cannot be temporary tables, in mysql, only InnoDB tables support foreign keys. All fields for which foreign keys are to be created must be created. For non-InnoDB tables, the foreign key clause is ignored.

2> Use of Foreign keys
Syntax:
Alter table table_name add [constraint foreign key name] foreign key [id] (index_cil_name ,...) References table_name (index_col_name ,...)
[On delete {cascade "set null | no action | restrict}]
[On update {cascade | set null | no action | restrict}]
We can see that mysql has strict requirements on creating foreign keys, accurate to the type and length of fields.
We recommend that you use the on delete restrict and on update cascade methods for Foreign keys.
Cascade: The foreign key field values in the foreign key table will be followed by the parent table, or the column where they are located will be deleted.
No action: no join operation is performed.
Restrict: restrict is equivalent to no action, that is, no operation is performed. Reject the modification of foreign key Association columns in the parent table and delete records.
Set null: when the foreign key Association field of the parent table is modified and deleted, the foreign key column of the External table is set to null ).

For data addition, the value entered in the foreign key column of the child table can only be an existing value in the foreign key Association column of the parent table. Otherwise, an error occurs.
Currently, student and book tables have just been created. Map the typeid field in the book to the sid field in the student table:

You can use show create table table_name to create the table code and storage engine, and view the settings of Foreign keys.
Delete foreign key:
Alter table table_name drop foreign key name;
A table may have more than one foreign key. You can use the preceding statement to delete multiple foreign keys.

Note: When defining foreign keys, use "constraint foreign key name foreign key ..." Statement.
3. Non-empty Constraint
Fields with non-null constraints must have data when adding data. The content of a non-empty constraint field cannot be blank, but it can be an empty string or 0.
When creating a table, you can directly add not null or null behind the field type to set it to non-empty or empty fields.
Set a field in an existing table to non-empty:
Alter table table_name modify Field Name field type not null;
4. Default Value
The default value is to specify the default data value for the field. The use of the default value reduces the burden on adding data. The default value can be defined as the specified value and the current time. It is recommended that the field with the default value be not empty. Otherwise, the system will not be able to determine whether to add null or default value for this field when it is added.
Add default value:
When creating a table, add "default data" directly behind the field type. For existing tables, use the set Keyword:
Alter table table_name alter field name set default value data;
Delete existing default values:
Alter table table_name alter field name drop default;
Modify the default value: run the delete statement to add the default value.

In the preceding SQL statement, add the default value 2 to typeid and then modify it to 5.
5. uniqueness constraints
Unique constraint requires that the values of the column fields to be added must be unique. It can be null, but only one null value can appear. The unique constraint ensures that duplicate values are not present in one or more columns.
Create a unique constraint field:
When creating a table, you can directly create a unique constraint. You can directly add the unique keyword after the data type of the field, you can also use [constraint <constraint name>] unique (<field Name>) after defining all fields)
For existing tables and fields, use the add keyword to add a unique constraint:
Alter table table_name add unique (Field List );
Delete a unique constraint:
Alter table table_name modify column field Name field type [field constraints];
6. Auto-increment constraint
The auto-increment constraint is a constraint that is automatically added by the system and filled in field values.
Auto_increment is an attribute of a data column. It is only applicable to integer data columns,
The auto_increment data column must have unique constraints to avoid repeated serial numbers,
In mysql, a table can have only one auto_increment field,
When you use truncate table table_name to delete all data, auto_increment starts numbering from 1,
The value of the auto-increment field can only be added 1 at a time, instead of adding 2 and 3 at a time, just like the auto-increment constraint in the SQL Server database,
In the InnoDB data table, you cannot use the auto_increment = n Statement of create table or alter table to change the auto-increment initial value.
7. Delete the constraint of the specified name
In mysql, all constraints of a field can be deleted with the same name. Use the drop index statement:
Alter table table_name drop index constraint name;