MySQL Database-constraints

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.

Some of the most common constraints in MySQL are:

Constraint type:	PRIMARY Key	Default Value	only	FOREIGN Key	Non-empty
Key words:	PRIMARY KEY	DEFAULT	UNIQUE	FOREIGN KEY	Not NULL

===================================================

The primary key (PRIMARY key) is used to constrain a row in the table, and as an identifier for this row, the primary key is the key to the exact row in one table, and the primary key is important. The primary key requires that the data for this row cannot be duplicated and cannot be empty.

There is also a special primary key-the composite primary key. A primary key can be a column in a table, or it can be identified by two or more columns in a table

===================================================

The default value constraint specifies what to do when there is a column with the default constraint and the Insert data is empty.

The default constraint will only be reflected when using the INSERT statement (as described in the previous experiment), where the position of the default constraint will be populated with the value of default if there is no value in the INSERT statement

===================================================

A unique constraint is simple, which specifies that the value of a column specified in a table must not have duplicate values, that is, each value in this column is unique.

Insert fails if there is a unique constraint when the newly inserted data and the existing data are duplicated by the INSERT statement.

===================================================

The foreign key (FOREIGN key) ensures both data integrity and the relationship between tables.

A table can have multiple foreign keys, and each foreign key must references (reference) the primary key of another table, the column that is constrained by the foreign key, and the value must have a corresponding value in the column it references.

Insert fails if the value of the foreign KEY constraint does not correspond in the reference column, such as the following command, if there is no DPT3 in the Reference column (Dpt_name of the Department table) at insert

===================================================

A non-null constraint (NOT NULL), which can be understood by a name, is a non-null constrained column that must not be empty when inserting a value.

Violations of non-null constraints in MySQL, will not error, there will only be warnings.

Attached: SQL statements

1 CREATE DATABASEMysql_shiyan;2 3  UseMysql_shiyan;4 5 CREATE TABLEDepartment6 (7Dpt_nameCHAR( -) not NULL,8People_numINT(Ten)DEFAULT 'Ten',9   CONSTRAINTDpt_pkPRIMARY KEY(Dpt_name)Ten  ); One  A CREATE TABLEEmployee - ( -IdINT(Ten)PRIMARY KEY, theNameCHAR( -), -AgeINT(Ten), -SalaryINT(Ten) not NULL, -PhoneINT( A) not NULL, +In_dptCHAR( -) not NULL, -   UNIQUE(phone), +   CONSTRAINTEmp_fkFOREIGN KEY(IN_DPT)REFERENCESDepartment (Dpt_name) A  ); at   - CREATE TABLEProject - ( -Proj_numINT(Ten) not NULL, -Proj_nameCHAR( -) not NULL, -Start_date Date not NULL, inEnd_date DateDEFAULT '2015-04-01', -Of_dptCHAR( -), to   CONSTRAINTProj_pkPRIMARY KEY(Proj_num,proj_name) +);

MySQL Database-constraints