Shulou(Shulou.com)06/01 Report--

This article mainly tells you briefly how to restrict and modify the data table in MySQL. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to restrict and modify the data table in MySQL can bring you some practical help.

Constraints:

Classified by function as follows:

NOT NULL, PRIMARY KEY, UNIQUE KEY, DEFAULT, FOREIGN KEY

By the number of data columns, it is as follows:

Table level constraint, column level constraint

Modify the data table:

Actions for fields: add / remove fields, modify column definitions, modify column names, etc.

Actions for constraints: add / remove various constraints

Operations for datasheets: renaming datasheets (two ways)

* constraints ensure the integrity and consistency of data

* constraints are divided into table-level constraints and column-level constraints.

* constraint types include:

NOT NULL (non-null constraint)

PRIMARY KEY (primary key constraint)

UNIQUE KEY (unique constraint)

DEFAULT (default constraint)

FOREIGN KEY (foreign key constraint)

1. Analysis of the requirements of Foreign key constraints

* * parent and child tables must use the same storage engine, and temporary tables are prohibited.

* the storage engine for data tables can only be InnoDB.

* Foreign key columns and reference columns must have similar data types. The length of the number or whether there are signed bits must be the same; the length of the character can be different.

The primary key is indexed by default, and the foreign key column is generally created with reference to the column with the primary key, so if the foreign key column does not create an index, it will be created by MYSQL based on the index of the reference column.

A foreign key column cannot be referenced by a column without an index.

1. The reference column must create an index (if the primary key comes with its own index by default, so do not create it)

two。 Foreign key columns are created at will if the index is not created

[1] default storage engine for editing data tables, configuration file / etc/my.cnf

1 [mysqld] 2 default-storage-engine=INNODB

[2] create a provincial data table and view the storage engine

Mysql > USE test;mysql > CREATE TABLE provinces (- > id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> pname VARCHAR (20) NOT NULL->); mysql > SHOW CREATE TABLE provinces +- -+ | Table | Create Table | | +-+- -+ | tb0 | CREATE TABLE `provinces` (`id` smallint (5) unsigned NOT NULL AUTO_INCREMENT `pname` varchar (20) NOT NULL PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-+- -+

[3] create a user data table in which foreign key columns must have similar data types to reference columns

Mysql > CREATE TABLE users (- > id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> username VARCHAR (10) NOT NULL,-> pid SMALLINT UNSIGNED,-> FOREIGN KEY (pid) REFERENCES provinces (id)->)

Note: the users table with foreign key column is the child table, and the reference column id column provinces is the parent table.

Automatically create an index when you create a primary key, and view 1 index automatically created by the parent table

Mysql > SHOW INDEXES FROM provinces\ G * * 1. Row * * Table: provinces Non_unique: 0 Key_name: PRIMARYSeq_in_index: 1 Column_name: id Collation: a Cardinality: 0 Sub_part: NULL Packed: Index_type: BTREE Comment: 1 row in set (0.00 sec)

If the foreign key column does not have an index, it is automatically created:

Mysql > SHOW INDEXES FROM users\ G * * 1. Row * * Table: users Non_unique: 0 Key_name: PRIMARYSeq_in_index: 1 Column_name: id Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: * * 2. Row * * Table: users Non_unique: 1 Key_name: pidSeq_in_index: 1 Column_name: pid Collation: a Cardinality: 0 Sub_part: NULL Packed: YES Index_type: BTREE Comment: 2 rows in set (0.00 sec)

two。 Reference operations for foreign key constraints

* cascade: delete or update from the parent table and automatically delete or update matching rows in the child table

* set null: delete or update rows from the parent table and set the foreign key column as NULL in the child table. If you use this option, you must ensure that the subtable column does not specify NOT NULL

* restrict: reject delete or update operations on the parent table

* no action: the keyword of the standard SQL, which is the same as restrict in MySQL

CASCADE example:

Mysql > CREATE TABLE provinces (- > id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> pname VARCHAR (20) NOT NULL->); mysql > CREATE TABLE users1 (- > id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> username VARCHAR (10) NOT NULL,-> pid SMALLINT UNSIGNED,-> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE->); mysql > INSERT provinces (pname) VALUES ('Tom'); mysql > INSERT provinces (pname) VALUES (' John') Mysql > INSERT provinces (pname) VALUES ('Driver'); mysql > INSERT users1 (username,pid) VALUES (' Huang',2); mysql > INSERT users1 (username,pid) VALUES ('Li',3); mysql > INSERT users1 (username,pid) VALUES (' Pan',3); mysql > INSERT users1 (username,pid) VALUES ('He',1); mysql > INSERT users1 (username,pid) VALUES (' Long',2); mysql > SELECT * FROM users1 +-SELECT * FROM provinces. +-+ | id | pname | +-+-+ | 1 | Tom | | 2 | John | | 3 | Driver | +-+ mysql > DELETE FROM provinces WHERE id=3;mysql > SELECT * FROM provinces +-+ | id | pname | +-+-+ | 1 | Tom | | 2 | John | +-+-- + mysql > SELECT * FROM users1 +-+ | id | username | pid | +-+ | 1 | Huang | 2 | 6 | He | 1 | 7 | Long | 2 | +-+

3. Table-level and column-level constraints

* * A constraint established on a data column is called a column-level constraint

* constraints established on multiple data columns are called table-level constraints

* column-level constraints can be declared either at column definition or after column definition

* * Table-level constraints can only be declared after they are listed.

4. Modify the data table

Add a single column

ALTER TABLE tbl_name (data table name) ADD [COLUMN] col_name (column name) column_definition (column definition) [FIRST (insert column to front) | AFTER (insert column after specified column) col_name (column name) (insert last column if FIRST and AFTER are not written)]

Example:

Mysql > SHOW COLUMNS FROM users1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar (10) | NO | | NULL | | pid | smallint (5) unsigned | YES | MUL | NULL | | +-- +

Add the age column to the last column:

Mysql > ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10; mysql > SHOW COLUMNS FROM users1 +-+-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar (10) | NO | | NULL | | | pid | smallint (5) unsigned | YES | MUL | NULL | age | tinyint (3) unsigned | NO | | 10 | | +-+-+ |

Add the password column after the username column:

Mysql > ALTER TABLE users1 ADD password VARCHAR (32) NOT NULL AFTER username; mysql > SHOW COLUMNS FROM users1 +-+-+ | Field | Type | Null | Key | Default | Extra | | +-+-+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar (10) | NO | | NULL | | | password | varchar (32) | NO | | NULL | | pid | smallint (5) unsigned | YES | MUL | NULL | | age | tinyint (3) unsigned | NO | | 10 | | +-- | -- +

Add the truename column to the first column:

Mysql > ALTER TABLE users1 ADD truename VARCHAR (32) NOT NULL FIRST;mysql > SHOW COLUMNS FROM users1 +-+-+ | Field | Type | Null | Key | Default | Extra | | truename | varchar (32) | NO | | NULL | | id | smallint (5) unsigned | NO | PRI | | NULL | auto_increment | | username | varchar (10) | NO | | NULL | | password | varchar (32) | NO | | NULL | | pid | smallint (5) unsigned | YES | MUL | NULL | | age | tinyint (3) unsigned | NO | 10 | +- -+

Delete the column:

ALTER TABLE tbl_name (data table name) DROP [COLUMN] col_name (column name)

For example, delete the truename column:

Mysql > ALTER TABLE users1 DROP truename;mysql > SHOW COLUMNS FROM users1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar (10) | NO | | NULL | | password | varchar (32) | | NO | | NULL | | pid | smallint (5) unsigned | YES | MUL | NULL | | age | tinyint (3) unsigned | NO | | 10 | | +-+-- -+

Delete both the age and password columns (you can also add deletions) and separate them with commas:

Mysql > ALTER TABLE users1 DROP age,DROP password;mysql > SHOW COLUMNS FROM users1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar (10) | NO | | NULL | | pid | smallint ( 5) unsigned | YES | MUL | NULL | | +-- +

Add / remove primary key constraints:

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) / / add ALTER TABLE tbl_name DROP PRIMARY KEY / / delete

Add / remove unique constraints:

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...) / add ALTER TABLE tbl_name DROP {INDEX | KEY} index_name / / delete

Add / remove foreign key constraints:

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) Reference_definition / / add ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol / / delete

Add / remove default constraints:

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} / / add mysql > ALTER TABLE tbl_name ALTER age DROP DEFAULT; / / Delete

Add primary key constraint: create users2 data table, add id column, set id as primary key:

Mysql > CREATE TABLE users2 (- > username VARCHAR (20) NOT NULL,-> pid SMALLINT UNSIGNED->); mysql > ALTER TABLE users2 ADD id SMALLINT UNSIGNED;mysql > ALTER TABLE users2 ADD CONSTRAINT pk_users2_id PRIMARY KEY (id); mysql > SHOW COLUMNS FROM users2 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (20) | NO | | NULL | | pid | smallint (5) unsigned | YES | | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | +-+- -+

Delete the primary key constraint:

Mysql > ALTER TABLE users2 DROP PRIMARY KEY

Add unique constraint: add username as a unique constraint:

Mysql > ALTER TABLE users2 ADD CONSTRAINT OK_users2_username UNIQUE KEY (username)

Delete a unique constraint:

Mysql > SHOW INDEXES FROM users2\ G * * 1. Row * * Table: users2 Non_unique: 0 Key_name: OK_users2_usernameSeq_in_index: 1 Column_name: username Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: * * 2. Row * * Table: users2 Non_unique: 1 Key_name: pidSeq_in_index: 1 Column_name: pid Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment : 2 rows in set (0.00 sec) mysql > ALTER TABLE users2 DROP INDEX OK_users2_username Mysql > SHOW INDEXES FROM users2\ G * * 1. Row * * Table: users2 Non_unique: 1 Key_name: pidSeq_in_index: 1 Column_name: pid Collation: a Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 1 row in set (0.00 sec)

Add a foreign key constraint: add the pid column as the foreign key column, and id in the provincse data table as the reference column:

Mysql > ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id)

Delete a foreign key constraint:

Mysql > SHOW CREATE TABLE users2 +- - -+ | Table | Create Table | | +-+- - -+ | users2 | CREATE TABLE `users2` (`username` varchar (20) NOT NULL `pid` smallint (5) unsigned DEFAULT NULL, `id` smallint (5) unsigned NOT NULL DEFAULT '0mm, `age` tinyint (4) NOT NULL, KEY `pid` (`pid`) CONSTRAINT `users2_ibfk_ 1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-- - -+ mysql > ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1

Add default constraint: add age column, set age column default 15

Mysql > ALTER TABLE users2 ADD age TINYINT NOT NULL;mysql > SHOW COLUMNS FROM users2 +-+-+ | Field | Type | Null | Key | Default | Extra | +-- +-+ | username | varchar (20) | NO | UNI | NULL | pid | smallint (5) unsigned | YES | MUL | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | age | tinyint (4) | NO | | | NULL | | +-- + mysql > ALTER TABLE users2 ALTER age SET DEFAULT 15 | Mysql > SHOW COLUMNS FROM users2 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | username | varchar (20) | NO | UNI | NULL | | pid | smallint (5) unsigned | YES | MUL | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | age | tinyint (4) | | NO | | 15 | | +-+-+ |

Delete the default value:

Mysql > ALTER TABLE users2 ALTER age DROP DEFAULT;mysql > SHOW COLUMNS FROM users2 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | username | varchar (20) | NO | UNI | NULL | | pid | smallint (5) unsigned | YES | MUL | NULL | | id | smallint (5) unsigned | NO | PRI | 0 | | age | tinyint (4) | | NO | | NULL | | +-- + |

Modify the column definition:

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

Modify the column name (cannot be changed casually):

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_dufinition [FIRST | AFTER col_name]

Modify the name of the data table (cannot be changed casually):

Method 1:

ALTER TABLE old_tbl_name RENAME new_tbl_name

Method 2:

RENAME TABLE old_tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2]...

MySQL how to constrain and modify the data table will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.