MySQL's constraint note point

2. Not a null constraint.

   The NOT NULL constraint can only be declared after the column name, cannot be declared with the constraint statement, and of course there is no way to declare the constraint name. The change field is NULL or NOT NULL only with ALTER TABLE table_name modify COL_NAME (not) null, and cannot be modified with the constraint statement. In other words, NOT NULL is a constraint, but it is not related to the constraint statement. The NOT NULL constraint cannot be found in the Table_constraints table in INFORMATION_SCHEMA.

   
   

4. The PRIMARY KEY constraint.

   A PRIMARY KEY constraint can be declared either after the column name or after all the columns have been declared. The former only needs to follow the PRIMARY key on the list, the latter method is [CONSTRAINT [symbol]] PRIMARY key (COL1,...), where the declaration of the name of the constraint is meaningless, Because if you use the constraint [symbol] statement, MySQL automatically changes the constraint name to primary, which can be verified in the Table_constriants table inside the INFORMATION_SCHEMA. So we declare all columns and then declare that primary key does not use the CONSTRAINT keyword. The primary key is a unique index with a NOT NULL constraint, a unique index is created on the primary key column, the index has the same name as the constraint, the default is the Btree index, and the index information can be queried in the statistics table inside the INFORMATION_SCHEMA.

   Delete PRIMARY KEY constraint using ALTER TABLE table_name drop PRIMARY key;

   
   

   
   

   
   

6. Unique constraint.

   A unique constraint can also be called a unique index, because both are present at the same time. There are two ways to create a unique constraint when creating a table:

   (1) CREATE TABLE t1 (ID int primary key,name varchar), cannot specify constraint name, system default constraint name and field name are the same.

   (2) CREATE TABLE t1 (ID int primary key,name varchar), constraint t1_unq unique (name));

   A unique constraint can be added in two ways when a table already exists.

   (1) ALTER TABLE T1 modify name varchar (a) unique;

   (2) ALTER TABLE t1 ADD constraint t1_unq unique (name);

(3) ALTER TABLE T1 add unique index T1_UNQ (name);

Because the unique constraint is a unique index, we will add a unique index in the third way, and if you do not have a unique keyword, you are adding a normal index. The unique constraint (unique index) information is also consistent within the statistics table and the Table_constraints table inside the INFORMATION_SCHEMA.

Deleting a unique constraint (index) can only use the following statement:

ALTER TABLE T1 DROP INDEX T1_UNQ;

4. foreign KEY constraints.

Creating a FOREIGN KEY constraint while building a table can be done using the following method (note that the column of the referenced table must have a unique constraint):

(1) CREATE TABLE t1 (ID int primary key, name varchar references T2 (name)); The method cannot declare a constraint name.

(2) CREATE TABLE t1 (ID int primary key,name varchar, constraint t1_fk_name foreign key (name) references T2 (name)), this method can Declares a constraint name.

To add a foreign key constraint when a table already exists, you can use the following statement:

ALTER TABLE ADD constraint T1_fk_name foreign key (name) references T2 (name));

Deleting a foreign key can use the following statement:

ALTER TABLE drop FOREIGN key t1_fk_name;

This article is from the "Trikker" blog, make sure to keep this source http://trikker.blog.51cto.com/7478890/1538373