MySQL: column attributes (column constraints) and mysql column attribute Constraints

MySQL: column attributes (column constraints) and mysql column attribute Constraints

1. Whether to allow Null (Null/not Null)

Specifies whether the value of a field can be null. It can be empty by default.

At this time, the inserted values a = 10 and B are null. you can insert them correctly:

If you try to insert the value B = 11, but a does not process it, you cannot insert it correctly, prompting that a has no default value.

That is, when no information is entered for a field, the column attribute tends to first find whether there is a default value.

Tip: NULL is different from an empty string. Null occupies space and must indicate that some fields can be NULL.

2. Set the default value)

When a blank field is inserted into the data, the system will try to find whether the default value is set for this field.

If the default value is set in advance, the default value is used. For example:

When only a = 11 is inserted, because B cannot be NULL, find the default value and Set B = 20.

The result is as follows:

Insert only B = 11.

Tip: If the value inserted to a field is NULL, the result is NULL instead of the default value;

The default value is enabled only when the field is not inserted with a default value.

The default value is usually used when the field cannot be blank and the default value is set.

3. Primary Key | unique index

Primary key (PK: primary key): when creating a table, you can uniquely identify the field or field set of a record.

It can be an attribute of a real object, such as an ID card number. However, a common solution is to use an attribute unrelated to the object information as a unique identifier.

The primary key is not related to the business logic and is only used to identify records.

ID	Class	Name	Age
1	1520113	Zhang San	24
2	1520114	Li Si	26

In the preceding table, names and ages may be duplicated and cannot be used as primary keys;

If no ID exists, and other fields may be duplicated, you can combine several fields (such as class + name) to implement unique identifiers.

Primary Key setting method:

Solution 1: Set Fields

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

In addition, the t_id of the primary key cannot be set to NULL, although not null is NOT set.

But if the type permits, it can be a negative value, as long as the unique identification principle is met.

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

Solution 2: Final Statement of table Creation

1 create table teacher1 (2 t_id int, 3 t_name varchar (5), 4 class_name varchar (6), 5 days tinyint unsigned, 6 primary key (t_id) // specify which field 7 is the primary key at the end of the definition );

Advantages of solution 2: multiple fields can be labeled as the primary key combination.

Tip: note that the statement is rigorous: currently, a primary key contains two fields, not both of which are primary keys. It can be said that these two fields form the primary key.

Tip: Automatic Growth Mechanism (providing a unique identifier for each record)

Each time a record is inserted, the value of a field is automatically added.

Use the auto_increment identifier, as shown in

For a primary key with auto_increment set, if the value of this field is set to NULL or the content of this field is not input when data is input.

The system will automatically add data numbers, such:

Tip: it is not a primary key field. You can also set it to auto_increment.

In addition, the initial value of automatic growth can be set. The default value is 1.

Use the alter statement to make changes, such:

Insert data again without setting the primary key value. The result is as follows:

Tip: when the value of N is smaller than the value of the current primary key, it can be set successfully, but it will still automatically increase 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 a field of an object points to the primary key of another object, the field class_id of the eg. student table points to the primary key class_id of the class table.

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

The object to be pointed to. It is called a primary entity or a parent entity. Class

The entity to which a person is responsible is called a slave entity or a child entity. Student

Purpose: Restrict entities in a link.

① Whether the corresponding parent table record exists when the sub-Table record is added;

② How should the sub-table process related records when deleting or updating the parent table records;

Definition: Add a foreign key field to the child table to point to the primary key of the parent table.

Create an it_class table and set the field class_id as the primary key:

Create 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.

Statement: foreign key (field name) references parent table name (parent table primary key );

If you want to insert data in the itcast_student table, but the class specified by the student does not exist, the creation will fail:

You can insert data into the parent table it_class even if the child table does not point to its primary key:

Cascade operation: how to deal with the associated slave table data when the master table data changes.

① Primary table update: on update

② Primary table deletion: on delete

Permitted cascade actions:

① Cascade: If the master table is updated or deleted, the slave table will also perform corresponding operations.

② Set null: if the primary table record is deleted, the corresponding record in the table is set to null. Indicates that the slave table does not point to any master table record.

③ Restrict: deny operations on the primary table.

 

Modify foreign key:

Delete the table and create a new table.

Alter table tb_name drop foreign key (class_id );

To delete a foreign key, you must specify a foreign key name. You can specify a name when creating a foreign key, or use the default MySQL name.

Create a foreign key and specify the allowed cascade as set null:

If you delete the record numbered 1 in it_class of the parent table, the associated record from itcast_student of the table is changed to NULL:

On update indicates that the slave table is affected only when the primary key of the master table changes.