Mysql integrity constraints

Defined:

An integrity constraint is a restriction on a field that meets the desired effect of the field, such as a field with a default value, NOT NULL, etc., mainly unique, self-increment, primary key, FOREIGN KEY constraint

Unique constraint:

A unique constraint can have more than one but the value of the indexed column must be unique, and the value of the indexed column allows for a null value.

If you can determine that a data column will contain only values that are different from each other, you should use the keyword unique when creating an index for that data column.

Example:

CREATE TABLET5 (IDINTauto_increment, nameVARCHAR( -)DEFAULT NULL,  PRIMARY KEY(ID),UNIQUE KEYuk_t5_name (name));--To add a constraint after a table is built:Alter TableT5Add constraintUk_t5_nameUnique(name);--If you do not need a unique constraint, you can delete thisALTER TABLET5DROP INDEXUk_t5_name;

Grammar:

-- To create a unique constraint: Create Unique Index  on  --  add constraint after building table:altertableaddconstraint  Unique-  If you do not need a unique constraint, you can delete the ALTERTABLEDrop  INDEX uk_t5_name;

Self-increment constraint:

MySQL can have only 1 autogrow fields per table, which is usually used as a primary key or as a non-primary key, but note that the autogrow field must be added as a unique index when used as a non-primary key, or the system will get an error.

 mysql>  create  table   t4 ( ->  ID int  not  null   ->  name varchar  (20  ) ,  ->  age int  unique   auto_increment  ->   0  rows affected (0.13  sec) 

PRIMARY KEY constraint:

A primary key is a constraint that uniquely identifies a record, like an identity card.

The primary key has two constraints: non-empty and unique!

There are two ways of creating a PRIMARY KEY constraint:

--Mode 1CREATE TABLET1 (IDINT PRIMARY KEYauto_increment, nameVARCHAR( -));--Mode 2CREATE TABLET2 (IDINT  not NULL, nameVARCHAR( -));

Additions and deletions of primary KEY constraints:

Add primary KeyAlter TableTab_nameAdd Primary Key(field name,...) Delete primary KeyAlter TableUsersDrop Primary Key;CREATE TABLETest (numINT PRIMARY KEYAuto_increment);<Br> --think, how do I delete a primary key?     ALTER TABLETest Modify IDint;--Auto_increment is gone, but the primary key is still there, so add the following sentence    ALTER TABLETestDrop Primary Key;--You can't just delete the primary key with this sentence .

Primary Key considerations:

Note: 1 , there can be at most one primary key in a single table 2 , if the primary key is not set in the table, the default is not NULL for the field to be the primary key, and if there are multiple fields that are not NULL in the table, the first field set to NOT NULL is set as the primary key in order. Conclusion: The primary key must be non-null and unique, but non-null and unique fields are not necessarily primary keys.  3, the primary key type must not necessarily be integral type

Composite PRIMARY KEY constraints:

The so-called composite primary key means that the primary key of your table contains more than one field.

If a column cannot uniquely distinguish a table of records, consider multiple columns combined to achieve uniqueness of the table record, in the form

① Create: Create Table SC (    int,    int, int    ,Primary  key  (Studentno,courseid));        ② Modify:altertableaddprimarykey (Field 1, Field 2, Field 3);

FOREIGN KEY constraints:

外键的定义语法：

[ CONSTRAINT symbol]  FOREIGN KEY [id] (index_col_name, ...)                      REFERENCES tbl_name (index_col_name, ...)      [ ON DELETE { RESTRICT |  CASCADE |  SET NULL |  NO ACTION |  SET DEFAULT }]      [ ON UPDATE { RESTRICT |  CASCADE |  SET NULL |  NO ACTION |  SET DEFAULT }]

Mysql integrity constraints