MySQL: Column properties (column constraints)

1. NULL is allowed (null/not null)

Specifies whether the value of a field can be null. The default is to be empty.

At this point, insert the value A = ten, B is empty, and the discovery can be inserted correctly:

If you try to insert a value of B = 11, and a does not handle it, the discovery cannot be inserted correctly, suggesting that a does not have a default value.

That is, when no information is entered for a field, the column properties tend to look for a default value first.

Tip:null is different from an empty string, NULL takes up space and needs to indicate that some fields can be null.

2. set Defaults (default value)

When you insert data that has an empty field, you try to find the field that has no default values set.

Default values are used if the default values are set in advance. Such as:

When only a = 11 o'clock is inserted, B is not allowed to be NULL, the default value is found, and B = 20 is set.

Results such as:

inserting only B = 11 is the same thing.

Tip: If the value inserted for a field is NULL, the display will be null instead of the default value;

The default value is enabled when and only if no value is inserted for the field, and there is a default value.

The default value is common: The field cannot be empty and the default value is set.

3. PRIMARY Key | Unique index

Primary KEY (Pk:primary key): When you create a table, you can uniquely identify a record's field or field collection.

Can be a property of a real entity, such as a social security number, but a common solution is to take advantage of an attribute that is not related to entity information as a unique identifier

The primary key does not have a relationship with the business logic and is used only to identify the record.

Id	Class	Name	Age
1	1520113	Tom	24
2	1520114	John doe	26

For the above table, the name and age may be duplicated, not as the primary key;

If you do not have an ID, and other fields can be duplicated individually, consider combining some of these fields (such as class + name) to achieve a unique identity.

How to set the primary key:

Scenario One: setting on a field

At this point, if you try to insert a data that is t_id 1 again, you will be prompted with an error and the primary key cannot be duplicated.

Also, the primary key t_id cannot be set to null, although not NULL is not set.

However, if the type allows, a negative value can be used as long as the unique identification principle is met.

Query table, you will find that t_id has been set as the primary key.

Scenario Two: Final statement of the construction table

1 Create TableTeacher1 (2t_idint,3T_namevarchar(5),4Class_namevarchar(6),5Daystinyintunsigned,6 Primary Key(t_id)//indicate at the end of the definition which field the primary key is above7);

Advantage of Scenario Two: You can label multiple fields as a combined primary key .

Tip: Pay attention to the rigor of the argument: it is now a primary key that contains two fields, not to say that both fields are primary keys, it can be said that these two fields make up the primary key.

Tip: Auto-grow mechanism (provides a unique identity for each record)

Each time you insert a record, the value of a field is automatically added to one.

Use the Auto_increment identity, as shown in

For a primary key set Auto_increment, when entering data, set the value of the field to null, or do not enter the field contents.

The system will number the data in the form of an automatic increment, such as:

Tip: It's not actually a field for the primary key, or it can be set to auto_increment.

In addition, the initial value of autogrow can be set , which is 1 by default.

Make changes by using the ALTER statement, such as:

Insert data at this point, do not set the value of the primary key, you will find the result is as follows:

Tip: When you set the value of N to less than the value of the current primary key, you can set the success, but it will still automatically grow based on the existing primary key value.

Question: Can I manually insert the value of the primary key after Auto_increment is set? yes!! As long as there is no conflict.

Can I update the current data? YES, use the UPDATE statement.

4. FOREIGN KEY constraint (foreign key)

If one of the fields of an entity points to the primary key of another entity, eg. The fields of the student table class_id point to the primary key class_id of the class table.

The class_id of the current student entity is a foreign key.

The entity that is pointed to, called the primary entity, is also called the parent entity. Class

The entity that is responsible for pointing, called from the entity, also called the child entity. Student

Role: used to constrain entities that are within a relationship .

① When a child table record is added, is there a corresponding parent table record;

② How the child table should handle related records when deleting or updating the parent table record;

Definition: Add a Foreign key field on the child table and point to the primary key of the parent table.

Establish the It_class table, set the field class_id as the primary key:

Establish the Itcast_student table, define the field class_id, and set it as a foreign key, pointing to the primary key field class_id in the It_class table.

The statement is foreign key (field name) references the parent table name (parent table primary key);

To insert data in the Itcast_student table, but the class that the student refers to does not exist, it causes the creation to be unsuccessful:

You can insert data in the parent table It_class, even if there is no data in the child table that points to its primary key:

cascading operations : When the primary table data changes, how the table data associated with it should be handled.

① Main Table update: on update

② Main Table Delete: on delete

allowable Cascade actions:

①cascade: If the primary table is updated or deleted, the corresponding action is also taken from the table.

②set null: If the primary table record is deleted, the corresponding record from the table is set to NULL. Represents a record from a table that does not point to any primary table.

③restrict: Rejects the related operation of the primary table.

To modify a foreign key:

First delete, then new, by modifying the table to complete.

ALTER TABLE Tb_name drop FOREIGN key (class_id);

Deleting a foreign key is accomplished by specifying a foreign key name, either by specifying a name when creating a foreign key, or by using the default generated name for MySQL.

Creates a new foreign key, specifying the allowed level linkage as SET NULL:

When you delete a record numbered 1 in the parent table It_class, you find that the corresponding record in table itcast_student is associated with it and becomes null:

On update refers to a change in the primary key of only the primary table, which affects the table from which it occurs.

MySQL: Column properties (column constraints)