MySQL Learning 12: Modifying data tables (ii)

Let's go on to the previous article on adding a constraint.

Three add constraints (3) Adding 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) Adding a DEFAULT constraint ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal};

Example:

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

ALTER TABLE users2 ADD age TINYINT UNSIGNED not NULL;

SHOW COLUMNS from Users2;

Add default constraint The Age field is the default value of 15:

alter TABLE USERS2 alter age SET DEFAULT;

SHOW COLUMNS from Users2;

Four Deleting a constraint (1) Delete primary KEY constraint

Delete the PRIMARY KEY constraint syntax format (since there is only one primary key for a data table, you do not need to include the following):

ALTER TABLE table_name DROP PRIMARY KEY;

Example:

ALTER TABLE users2 DROP PRIMARY KEY;

SHOW COLUMNS from Users2;

(2) Delete Unique constraint

Remove the syntax format for a unique constraint (a data table can have multiple unique constraints):

ALTER TABLE table_name DROP {index| KEY} index_name;

Example:

To see the name of the field indexed, find the unique constraint field:

SHOW INDEXES from Users2\g;

ALTER TABLE users2 DROP INDEX username;

SHOW COLUMNS from Users2;

(3) Delete foreign KEY constraint ALTER TABLE table_name DROP FOREIGN KEY Fk_symbol;

Example:

To see the FOREIGN KEY constraint first:

SHOW CREATE TABLE users2;

You know that 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 default constraints

The syntax format for deleting default constraints is:

ALTER TABLE TABLE_NAME ALTER [COLUMN] col_name {DROP DEFAULT};

Example:

alter TABLE USERS2 alter age DROP DEFAULT;

SHOW COLUMNS from Users2;

Five modifying column definitions and renaming data tables (1) Modifying a column definition

To modify the column definition syntax format:

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, you need to do this if you change 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 data type of the field:

SHOW COLUMNS from Users2;

ALTER TABLE users2 MODIFY ID TINYINT UNSIGNED not NULL first;

SHOW COLUMNS from Users2;

Changing large data types to small data types can result in loss of data. Use caution to change the data type. So that's what we're going to do with database design.

Be sure to design the data type of the field beforehand to avoid unnecessary trouble.

(2) modifying column names

To 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: Modifying the data type of a column name and column

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

There are two ways to modify a data table name:

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 use as little as possible to modify column definitions and data table names, which may cause views or stored procedures to be disabled.

Summary:

modifying data tables

Actions for fields: Add/Remove fields, modify column definitions, modify column names, and so on.

Actions for constraints: Add/Remove various constraints.

Operations on data tables: Data table renaming (two ways).

MySQL Learning 12: Modifying data tables (ii)