mysql command: Constraint

A constraint is a restriction that ensures the integrity and uniqueness of a table's data by restricting the data on the table's Rows or columns

Constraint classification:

Constraint types and keywords:

• Primary KEY PRIMARY Key
• Default value Defaults
• Uniquely unique
• External FOREIGN KEY
• Non-empty NOT NULL

Example MySQL statement code:

GitHub Address: Https://github.com/kumataahh/xxx_database

CREATE DATABASEMysql_kumata; UseMysql_kumata;/*Class Table*/CREATE TABLEClass (Class_nameCHAR( -) not NULL,--non-null keyStudent_numINT(Ten)DEFAULT 'Ten',--default Key  CONSTRAINTCls_pkPRIMARY KEY(Cls_name)--PRIMARY Key  --Custom PRIMARY Key name: CLS_PK, PRIMARY key: Cls_name);/*Student Information Form*/CREATE TABLEStudent (IDINT(Ten)PRIMARY KEY,--PRIMARY KeyNameCHAR( -),--no non-null constraintAgeINT(Ten) not NULL,--non-null constraintPhoneINT( A) not NULL,    UNIQUE(ID),--Unique Key  CONSTRAINTStu_fkFOREIGN KEY(IN_CLS)REFERENCESClass (Class_name)--FOREIGN Key  --Custom FOREIGN Key name: STU_FK foreign key: In_cls, refer to class_name column for CALSS table);/*Personal Work Table*/CREATE TABLEProject (Pro_numINT(Ten) not NULL, Pro_nameCHAR( -) not NULL, Start_data DATE not NULL, end_date dateDEFAULT '2018-07-01', Of_clsCHAR( -)REFERENCESClass (class_name),CONSTRAINTPro_pkPRIMARY KEY(Pro_num,pro_name)--Composite PRIMARY Key);

Description of each keyword: primary key PRIMARY key

A PRIMARY KEY constraint is equivalent to a combination of a unique constraint + non-null constraint, a PRIMARY KEY constraint column does not allow duplicates, and null values are not allowed, and if the combination of multiple columns is a PRIMARY KEY constraint, the columns are not allowed to be null, and the combined values do not allow duplicates.

only one primary key is allowed per table, and a PRIMARY key constraint can be created at the column level or at the table level.

MySQL's primary key name is always primary, and when a primary key constraint is created, the system defaults to establishing a unique index on the columns column combination.

1. Typically, we will set a primary key field for each table to mark the uniqueness of the record
2. However, it is not recommended to use the business meaning field as the primary key, as business fields may appear duplicated as the business changes.
3. It is recommended that you add a field called ID to each sheet independently and set the ID field as the primary key to be used as the uniqueness of the record.

Default value Defaults

The default value does not work when no value is inserted

Uniquely unique

Unique represents a unique constraint. The only constraint is that the column or combination of columns of the specified table cannot be duplicated, guaranteeing the uniqueness of the data. Although a unique constraint does not allow duplicate values, it can be more than one null, and the same table can have multiple unique constraints, multiple combinations of columns.

External FOREIGN KEY

FOREIGN KEY constraints are guaranteed referential integrity between one or two tables, which is a reference relationship between two fields built into a table or two fields of two tables. That is, the foreign key value from the table must be found or empty in the primary table. When the primary table's records are referenced from a table, the records of the primary table are not allowed to be deleted, and if you want to delete the data, you need to delete the data from the table that relies on that record before you can delete the data from the primary table.

Non-empty NOT NULL

Add a non-empty constraint to this field when a field must have a value (you cannot insert a value or null)

mysql command: Constraint