MySQL learning 12: Modifying data tables (2)

Next we will talk about adding constraints in the previous article. 3. Add CONSTRAINT (3) add foreign key CONSTRAINT altertable_nameadd [CONSTRAINT [symbol] FOREIGNKEY [index_name] [index_type] (index_col_name,...) references_definition; example: ALTERTABLEusers2ADDFOREIGNKEY (pi)

Next we will talk about adding constraints in the previous article. Add constraint 3 (3) add foreign key constraint alter table table_name ADD [CONSTRAINT [symbol] foreign key [index_name] [index_type] (index_col_name ,...) references_definition; example: alter table users2 add foreign key (pi

Next we will talk about adding constraints in the previous article.

3. Add constraints (3) add foreign key constraints

Alter table table_name ADD [CONSTRAINT [symbol] foreign key [index_name] [index_type]

(Index_col_name,...) references_definition;

Example:

Alter table users2 add foreign key (pid) REFERENCES provinces (id );

Show columns from users2;

(4) Add the DEFAULT constraint alter table table_name ALTER [COLUMN] col_name {set default literal };

Example:

First, add a column of fields to be used as the default constraint:

Alter table users2 ADD age tinyint unsigned not null;

Show columns from users2;

Add the default constraint age field as the default value of 15:

Alter table users2 ALTER age set default 15;

Show columns from users2;

Iv. Delete constraints (1) delete a primary key constraint

Delete the syntax format of the primary key constraint (because a data table has only one primary key, you do not need to add a list ):

Alter table table_name drop primary key;

Example:

Alter table users2 drop primary key;

Show columns from users2;

(2) Delete A unique constraint

Delete the syntax format of the unique constraint (a data table can have multiple unique constraints ):

Alter table table_name DROP {INDEX | KEY} index_name;

Example:

First, check the indexed field name and find the unique constraint field:

Show indexes from users2 \ G;

Alter table users2 drop index username;

Show columns from users2;

(3) Delete the foreign key constraint alter table table_name drop foreign key fk_symbol;

Example:

First, check the foreign key constraints:

Show create table users2;

The name of the foreign key constraint is users2_ibfk_1, so you can delete the foreign key constraint:

Alter table users2 drop reign key users2_ibfk_1;

Show create table users2;

(4) Delete the default Constraint

Syntax format for deleting default constraints:

Alter table table_name ALTER [COLUMN] col_name {drop default };

Example:

Alter table users2 ALTER age drop default;

Show columns from users2;

5. Modify column definitions and rename Data Tables (1) Modify column Definitions

Modify the syntax format of the column definition:

Alter table table_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];

Example:

1) if we see that the id field of the data table is not in the first column, we need to do this if we change it to the first column:

Show columns from users2;

Alter table users2 MODIFY id smallint unsigned not null first;

Show columns from users2;

2) We can also modify the field data type:

Show columns from users2;

Alter table users2 MODIFY id tinyint unsigned not null first;

Show columns from users2;

Changing the big data type to the small data type will cause data loss. Exercise caution when changing the data type. Therefore, we need to design the database

You must design the Data Type of fields in advance to avoid unnecessary troubles.

(2) modify the column name

Modify the syntax format of a column Name:

Alter table table_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST |

AFTER col_name];

Example: Modify the column name and column data type

Show columns from users2;
Alter table users2 CHANGE pid p_id tinyint unsigned not null;

Show columns from users2;

(3) modify the data table name

You can modify a data table name in either of the following ways:

Method 1:

Alter table table_name RENAME [TO | AS] new_table_name;

Method 2:

Rename table table_name TO new_table_name [, table_name2 To new_table_name2]...;

Example:

Show tables;

Alter table users2 RENAME users3;

Show tables;

Let's use another method to modify the data table name:

Rename table users3 TO users2;

Show tables;

We should try to modify the column definition and data table name as few as possible, which may make the view or stored procedure unavailable.

Summary:

Modify a data table

Operations on fields: Add/delete fields, modify column definitions, and modify column names.

Constraint-specific operations: Add/delete constraints.

Actions on a data table: rename a data table (two methods ).