[MySQL] (3) constraint and modify data table _ MySQL

1. constraints ensure data integrity and consistency. constraints are classified into table-level constraints and column-level constraints. Constraints include: NOTNULL (non-empty constraint), PRIMARYKEY (primary key constraint), UNIQUEKEY (unique constraint), DEFAULT (DEFAULT constraint), and FOREIGNKEY (Foreign check about 1. constraint

Constraints ensure data integrity and consistency. constraints include table-level constraints and column-level constraints. Constraints include: not null (non-empty constraint), primary key (primary key constraint), unique key (UNIQUE constraint), DEFAULT (DEFAULT constraint), and foreign key (FOREIGN check constraint ).

Foreign key constraints ensure data consistency and integrity, and enable one-to-one or one-to-many relationships.

Foreign key constraints:

(1) the parent and word tables must use the same storage engine, and temporary tables are not allowed.

(2) the data table storage engine can only be InnoDB.

(3). Foreign key columns and reference columns must have similar data types. The length of the number or whether there are symbols must be the same, while the length of the characters can be different.

(4) the foreign key column and reference column must be indexed. If the foreign key column does not have an index, MySQL automatically creates an index.

MySQL configuration file:

Default-storage-engine = INNODB

CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL);SHOW CREATE TABLE provinces;CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id));SHOW INDEX from provinces\G;SHOW INDEX from users\G;

2. Reference operations for foreign key constraints

(1). CASCADE: deletes or updates matched rows from the parent table and automatically deletes or updates the matched rows in the child table.

(2). set null: delete or update rows from the parent table, and SET the foreign key column in the child table as NULL. If this option is used, make sure that not null is NOT specified for the sub-table column.

(3). RESTRICT: the operation to delete or update the parent table is denied.

(4). no action: keyword of standard SQL, same as RESTRICT in MySQL

For example:

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 ); INSERT provinces (pname) VALUES ('A'); INSERT provinces (pname) VALUES ('B'); INSERT provinces (pname) VALUES ('C '); INSERT users1 (username, pid) VALUES ('Tom ', 3); INSERT users1 (username, pid) VALUES ('Jerry', 1 ); # view the added data SELECT * FROM users1; # DELETE a province delete from provinces WHERE id = 3; # view the province SELECT * FROM provinces; # view the personnel table SELECT * FROM users1;

3. table-level constraints and column-level constraints

The constraint on a data column resume is called a column-level constraint. the constraint on multiple data columns is called a table-level constraint. Column-level constraints can be declared either when a column is defined or after a column is defined. Table-level constraints can only be declared after the column is set.

4. modify a data table

(1). add/delete columns

Add a single column:

Alter table tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];

Add multiple columns:

Alter table tbl_name ADD [COLUMN] (col_name column_definition ,...);

Delete column:

Alter table tbl_name DROP [COLUMN] col_name;

For example:

Show columns from users1; alter table users1 ADD age tinyint unsigned not null default 10; alter table users1 ADD password VARCHAR (32) not null after username; alter table users1 ADD truename VARCHAR (20) not null first; # Check the users1 structure show columns from users1; alter table users1 DROP truename; alter table users1 DROP password, DROP age; # Check the users1 structure show columns from users1;

(2) add a primary key constraint

Alter table tbl_name ADD [CONSTRAINT [symbol] primary key [index_type] (index_col_name ,...);

For example:

Create table users2 (username VARCHAR (10) not null, pid smallint unsigned); show create table users2; alter table users2 ADD id smallint unsigned; show columns from users2; # set id to primary key alter table users2 add constraint PK_users2_id primary key (id );

(3) add a unique constraint:

Alter table tbl_name ADD [CONSTRAINT [symbol] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name ,...);

For example:

ALTER TABLE users2 ADD UNIQUE (username);SHOW CREATE TABLE users2;

(4). add a foreign key constraint:

Alter table tbl_name ADD [CONSTRAINT [symbol] foreign key [index_name] (index_col_name,...) reference definition;

For example:

Alter table users2 add foreign key (pid) REFERENCES provinces (id); # View show create table users2;

(5). add/delete default constraints:

Alter table tbl_name ALTER [COLUMN] col_name {set default literal | drop default };
For example:

Alter table users2 ADD age tinyint unsigned not null; show columns from users2; # SET the DEFAULT value alter table users2 ALTER age set default 15; show columns from users2; # Delete the DEFAULT value alter table users2 ALTER age drop default; show columns from users2;

(6). delete the primary key constraint

Alter table tbl_name drop primary key;

For example:

Alter table users2 drop primary key; # View show columns from users2;

(7). delete the unique constraint

Alter table tbl_name DROP {INDEX | KEY} index_name;

For example:

# View the index show indexes from users2 \ G; alter table users2 drop index username; # View show columns from users2; # view the index show indexes from users2 \ G;

(8). delete foreign key constraints

Alter table tbl_name drop foreign key fk_symbol;

For example:

Show create table users2; alter table users2 drop foreign key users2_ibfk_1; # check that the foreign key is missing, but the pid INDEX is still in show create table users2; alter table users2 drop index pid; # show create table users2 is also deleted for pid indexes;

(9). modify the column definition

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

For example:

Show create table users2; # put the id at the beginning of alter table users2 MODIFY id smallint unsigned first; show columns from users2; # MODIFY the field type. Note: changing from a large type to a small type may cause data loss. Alter table users2 MODIFY id tinyint unsigned first;

(10). modify the column name

Alter table tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];

For example:

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

(11) Renaming a data table

Method 1:

Alter table tbl_name RENAME [TO | AS] new_tbl_name;

Method 2:

Rename table tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2]...;

For example:

ALTER TABLE users2 RENAME users3;SHOW TABLES;RENAME TABLE users3 TO users2;SHOW TABLES;

5. Summary

(1). constraints:

By function: not null, primary key, unique key, DEFAULT, FOREIGN KEY

Divided by the number of data columns: table-level constraints and column-level constraints

(2). modify the data table:

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

Constraint-specific operations: add/delete constraints

Operations on data tables: renaming data tables (two methods)