MySQL column constraint

Column Properties (constraints)
1: Whether NULL is allowed (NOT NULL)--not null is not allowed to be empty
CREATE TABLE T_1 (
A tinyint (3) Zerofill NOT NULL,
b tinyint (3)
);
Example: INSERT into t_1 values (18,29);
Example: INSERT into t_1 values (null,12); --Error
Example: Desc t_1;
Example: ALTER TABLE t_1 Modify a tinyint (3) not NULL Zerofill; --Error Zerofill cannot separate data types
Example: INSERT into t_1 (a) values (19);
Example: INSERT into t_1 (b) VALUES (28);

*************************************************************************************************************** ****

2: Default Value property
CREATE TABLE T_2 (
A tinyint (2) Zerofill NOT null default 18,
b tinyint (2)
);
Example: INSERT into t_2 (b) VALUES (19);
*************************************************************************************************************** ****

3: Column comment (comment)
CREATE TABLE T_3 (
ID tinyint (2) Zerofill NOT null default comment ' number ',
Name varchar (comment ' name ')
);
Use INFORMATION_SCHEMA;
Show tables;
Desc Information_schema.columns;
Select Table_schema, TABLE_NAME, COLUMN_NAME, column_comment from Information_schema.columns;
Select Table_schema, TABLE_NAME, COLUMN_NAME, column_comment from information_schema.columns where table_schema = ' test ' and table_name = ' t_3 ';
*************************************************************************************************************** ****

4: Unique Constraint--no duplicate values allowed
drop table if exists t_4;
CREATE TABLE T_4 (
ID int,
Age tinyint,
Constraint un_id unique (ID)--Create a UNIQUE constraint to the ID field, constraint un_id to this unique constraint named un_id;
);
Desc T_4;
Show CREATE TABLE T_4\g

Example: INSERT into T_4 values (1, 2);
Example: INSERT into T_4 values (1, 2); --Error, ID value if the only
Example: ALTER TABLE T_4 DROP INDEX un_id; --delete the unique constraint un_id (constraint name);
Example: ALTER TABLE T_4 add unique (ID); --Add unique constraints,
PS: Constraint name can not write, if not write, it will default to create a unique constraint name, you can see the constraint name through the show create table t_4\g;
Example: ALTER TABLE T_4 DROP INDEX ID;

Example: INSERT into t_4 values (null, NULL);
Example: INSERT into t_4 values (null, NULL); --Unique constraint allows the repetition of null values
*************************************************************************************************************** ****

5: PRIMARY KEY constraint (primary key)
CREATE TABLE T_6 (
T_no Int (1) primary key,
T_name varchar (30),
T_sex varchar (3)
);
Example: Desc t_6;
Example: INSERT into t_6 values (null, ' chubby ', ' male '); --The error primary key value cannot be null, and the field is set by default when the primary key is created NOT NULL
Example: INSERT into t_6 values (1, ' chubby ', ' male ');
Example: INSERT into t_6 values (1, ' xiaoming ', ' Male '); --Error primary key value cannot be duplicated

PS: Only one primary key can be found in a table;
drop table if exists t_7;
CREATE TABLE t_7 (
a int,
b int
);
ALTER TABLE t_7 Modify a int primary key; --Add primary key
ALTER TABLE t_7 modify b int primary key; --An error can only have one primary key in a table;
ALTER TABLE t_7 drop PRIMARY key; --Delete primary key

PS: You can set the combined primary key
drop table if exists t_8;
CREATE TABLE T_8 (
a int,
b tinyint,
Primary KEY (A, B)
);
Example: INSERT into t_8 values ();
Example: INSERT into t_8 values (1,3);
Example: INSERT into t_8 values (1,3);
*************************************************************************************************************** *****

6: Auto Growth (auto_increment)
CREATE TABLE T_9 (
ID int primary KEY auto_increment,
Name varchar (30)
);
Example: INSERT into t_9 values (null, ' chubby '); --if Auto_increment is added, the primary key value can be null to represent the insert, but the actual insert is not a null value
Example: INSERT into t_9 values (null, ' xiaoming ');
Example: INSERT into t_9 values (null, ' Little Red ');
Example: INSERT into T_9 (name) values (' haha ');
INSERT into t_9 values (' Xiao ha '); --Error
PS: Automatic growth requires shaping and indexing
Example: Create TABLE T_9 (--Error
ID int auto_increment
);

The initial value of autogrow starts at 1, and you can customize this initial value
CREATE TABLE T_10 (
ID int PRIMARY KEY auto_increment
);
Example: ALTER TABLE T_10 auto_increment 10; --Set the initial value of autogrow to 10
Example: INSERT into t_10 values ();
Example: INSERT into T_10 values (50);
Example: INSERT into t_10 values (); -Automatic growth begins with the maximum value that is already present;
*************************************************************************************************************** *****

MySQL column constraint