Field Properties (i)

Properties in MySQL:noll/not NULL,default,comment,primary key,unique key,auto_increment

Fields that are empty for the query field:
Selectxfrom stuent where sex is null;

Default: Defaults:

Comment: Remarks

primary Key PRIMARY key: The primary key, which is the meaning of the index, is used to uniquely identify a record; (Uniqueness )

A field of multiple primary keys is called a composite primary key; (representing two field combinations as a primary key); A table can have only one primary key

Delete PRIMARY key: ALTER TABLE stuent drop PRIMARY key;

Add PRIMARY key: ALTER TABLE Stuent add primary key (...); S

The primary key is divided into logical primary keys and business primary keys:
Logical primary key is generally not related to the business, usually the ID is more commonly used

Business PRIMARY key: The primary key itself is a data field used to describe the data;

Auto_increment: Must use an integer type, must be an index, a table can only have one self-growth;

Modified self-growth: set auto_increment_increment = value;

Unique key: Guaranteed field uniqueness, one table allows multiple unique keys unique key;

Index Name: You can use a string directly, or add an inverse quote to a string

What is a foreign key:

Outside key: A field is maintained in a table that points to the primary key of another table, and the corresponding field is called a foreign key.

Relational database: Contains three parts: data structure, operation instruction, integrity constraint;

{Construct foreign Key name} foreign key (field name) reference table name (z primary key);

Reason: foreign key to establish success, you must ensure that the foreign key field itself is an index (key)

Add foreign key

Foreign keys can be added when tables are created and after tables are created.

Adding a foreign key to a new table: After all the fields, use the

[Constraint foreign key name ] foreign key (field name ) References table name (field name );

Add a foreign key after creating a table

ALTER TABLE name add [constraint foreign key name ] foreign key (field name ) References table name (primary key );

Add foreign Key Name

View foreign keys

View table Structure

Reason: foreign key to establish success, you must ensure that the foreign key field itself is an index (key)

Create a statement by looking at the table to see

modifying foreign keys

Foreign keys can not be modified, can only be deleted first, after the new

Delete foreign key

Foreign keys have special syntax: The system adds an operation syntax for foreign keys individually

ALTER TABLE name drop foreign key foreign key name ;

FOREIGN KEY constraints

Child table: A table with a foreign key is called a child table (cz_student)

Parent table: A table referenced by a foreign key is called a parent table (cz_class)

1. If the data is inserted in a child table and the foreign key data does not exist in the parent table, the insertion fails because of the effect of the foreign key constraint.

Correct data comparison

2. If the child table has data that references the primary key field of the parent table, the primary key field corresponding to the parent table cannot be modified.

Correct effect comparison

Constraint mode

Foreign keys in the data constraints can be specified in the constraint mode, there are three types of constraints

Strict mode: default mode,District, parent table does not allow deletion of data that has already been referenced

Cascading mode:Cascade, working together (parent table changes the child table also changes with this)

Empty mode:set NULL, if the parent table deletes data, the child table will empty the foreign key value

Constraint actions:update and Delete (constraint on parent table), child table constraint (cannot add data that does not exist in parent table)

Common constraint Patterns: Cascade operations when updating, empty when deleted

The schema specifies the syntax:

Constraint foreign Key name foreign key (field name ) References table name (primary key) on update constraint mode (cascade) on DELETE constraint mode ( set NULL)

The effect of the update

Effect of deletion

Foreign KEY Operation considerations

1. Foreign key only InnoDB storage engine support
2. The data type of the foreign key field must be exactly the same as the primary key field data type of the referenced table
3. If the foreign key field has a not NULL property: null is not allowed, then the null mode cannot be used when specifying the FOREIGN KEY constraint mode.

The foreign key can restrain the integrality of the data very well, and guarantee the validity of the data. However, the presence of foreign keys can lead to very complex maintenance of data.

Therefore, because the foreign key for the operation of the data is uncertain, so the use of foreign keys is not very wide.

Field Properties (i)