SQL review four (integrity constraints)

The integrity constraint is for the correctness of the table's data. There are mainly primary keys, foreign KEY constraints.

1 PRIMARY Key

When a column has a primary KEY constraint added, the data for that column cannot be repeated. This allows the primary key column of each row to uniquely identify the row. If the student can use the number as the unique logo.

The primary key cannot be null, nor can it be duplicated.

Specifies the primary KEY constraint with the keyword PRIMARY key.

When you create a table, you specify the primary key directly

Create Table  Char(6primarykeyvarchar (  int  varchar(ten))

When a table is created, the primary key is specified after the column is defined

Create Table  Char(6varchar( int   varchar(ten),primarykey(SID));

After creating the table, increase the primary key

Create Table  Char(6varchar( int   varchar(ten))

Add primary Key

Alter Table Add Primary key(SID);

Delete primary KEY (just delete primary KEY constraint without deleting primary key column):

Alter Table Drop Primary key;

2 primary key self-growth

MySQL provides automatic growth of the primary key function! This way the user will not have to worry about whether or not the primary key is repeated. When the primary key is set to autogrow, the value of the primary key is automatically generated when the primary key value is not given, and it is the maximum primary key value of +1, and there is no possibility of repeating the primary key.

Set primary key self-growth when creating a table (primary key must be integer to self-add)

Create Table  intprimarykey varchar (int  varchar(ten))

To set the primary key self-growth when modifying a table:

Alter Table int auto_increment;

Delete primary key self-growth when modifying table:

Alter Table int;

3 Non-empty

A column that specifies a non-null constraint cannot have a value, meaning that a column with a non-null constraint added must be given a value when the record is inserted, and the value of a non-empty column cannot be set to NULL when the record is modified.

Specify a non-null constraint:

Create Table  intprimarykeyvarchar (NOT null  int varchar (TEN)) 

When you insert a record into the Stu table when you specify non-empty for the Sname field, you must specify a value for the Sname field, or you will get an error:

INSERT into Stu (SID) VALUES (1);

The inserted record does not have a specified value in Sname, so an error will be given!

4 Unique

You can also specify a unique constraint for a field! When you specify a unique constraint for a field, the value of the field must be unique. This is similar to the primary key! For example, specify a unique constraint for the sname field of the Stu table:

CREATE TABLE Tab_ab (

Sid INT PRIMARY KEY auto_increment,

Sname VARCHAR (Ten) UNIQUE

);

INSERT into sname (SID, Sname) VALUES (1001, ' Zs ');

INSERT into sname (SID, Sname) VALUES (1002, ' ZS ');

When you insert the same name two times, MySQL will error!

5 FOREIGN Key

The primary foreign key is the only way to form a table-to-table relationship, and the foreign key is the primary key for the other table.

L Create the T_user table, specifying the UID as the primary key column:

Create Table User  intprimarykeyvarchar( Unique  notnull)

Create the T_section table, specify the SID as the primary key column, and u_id the foreign key for the UID column of the relative T_user table:

Create Table  intprimarykeyvarchar (NOT null   int, constraint foreign key   Referencesuser(UID))

Modify the T_section table to specify U_ID as the foreign key for the UID column of the relative T_user table:

Alter Table  addconstraintforeignkey references  User(UID);

Modify the T_section table to remove the foreign KEY constraint for u_id:

Alter Table Drop Foreign key fk_user_id;

SQL review four (integrity constraints)