Of Stack

Summary of example usage of MySQL add delete check and change statements

  • 2020-10-07 18:54:30
  • OfStack

1. Create the columns 


alter table tablename add colname type not null default '0 ' ;

Ex. : 


  alter table mmanapp_mmanmedia add appid_id integer not null default 372;

2. Delete the column 


  alter table tablename drop column colname;

Ex. : 


  alter table mmanapp_mmanmedia drop column appid_id;

Create a foreign key association on an existing column 


ALTER TABLE yourtablename  ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name,  ... )  REFERENCES tbl_name (index_col_name,  ... )  [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]  [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Ex. : 


ALTER TABLE mmanapp_mmanmedia ADD CONSTRAINT fk_mdappid FOREIGN KEY(appid_id)

4. Delete foreign key associations: 


  ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

Ex. : 


 ALTER TABLE mmanapp_mmanmedia DROP FOREIGN KEY fk_mdappid

Two copies of documents are attached:
1.mysql's operations on columns and tables

Add a primary key 


alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);

Add a new column 


alter table infos add ex tinyint not null default '0 ' ;

Delete the column 


  alter table mmanapp_mmanmedia add appid_id integer not null default 372;
0

Rename column/Change column type 


  alter table mmanapp_mmanmedia add appid_id integer not null default 372;
1

Rename table 


  alter table mmanapp_mmanmedia add appid_id integer not null default 372;
2

indexed 


mysql> alter table tablename change depno depno int(5) not null;
mysql> alter table tablename add index  Index name  ( The field name 1[ The field name 2  ... ]);
mysql> alter table tablename add index emp_name (name);

Add the index of the primary keyword 


  alter table mmanapp_mmanmedia add appid_id integer not null default 372;
4

Add an index with only 1 constraint 


  alter table mmanapp_mmanmedia add appid_id integer not null default 372;
5

Delete an index 


  alter table mmanapp_mmanmedia add appid_id integer not null default 372;
6

2. Add/delete constraints on the table

InnoDB allows you to add a new foreign key constraint to a table using ALTER TABLE: 


  alter table mmanapp_mmanmedia add appid_id integer not null default 372;
7

Remember to create the required indexes first. You can also use ALTER TABLE to add a self-referencing foreign key constraint to a table.
InnoDB also supports the use of ALTER TABLE to remove foreign keys: 


ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

When a foreign key was created, if the FOREIGN KEY clause contained an CONSTRAINT name, you could remove the foreign key by referring to that name. In addition, the fk_symbol value is guaranteed internally by InnoDB when the foreign key is created. To find the tag when you want to remove a foreign key, use the SHOW CREATE TABLE statement. Examples are as follows: 


  alter table mmanapp_mmanmedia add appid_id integer not null default 372;
9

InnoDB parser allows you to use FOREIGN KEY... REFERENCES... The clause encloses the table and column names with '(backticks). The InnoDB parser also takes into account the setting of the lower_case_table_names system variable.
InnoDB returns the foreign key definition of a table as part 1 of the output of the SHOW CREATE TABLE statement: 


SHOW CREATE TABLE tbl_name;

Starting with this version, mysqldump also generates the correct definition of the table into the dump without forgetting the foreign keys.
You can display foreign key constraints on a table as follows: 


SHOW TABLE STATUS FROM db_name LIKE  ' tbl_name';

The foreign key constraint is listed in the output Comment column.
When a foreign key check is performed, InnoDB sets a Shared row-level lock on the child or parent record it is looking after. InnoDB immediately checks the foreign key constraint for no transaction commit delay.
To make it easier to reload the dump file for tables with foreign keys, mysqldump automatically includes a statement in the dump output that sets FOREIGN_KEY_CHECKS to 0. This avoids the problems associated with tables that have to be reloaded in a particular order when the dump is reloaded. You can also set this variable manually: 


mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;

If the dump file contains tables in which the external keys are not in the correct order, the table is imported in any order. This also speeds up the import operation. Setting FOREIGN_KEY_CHECKS to 0 is also useful for ignoring foreign key limits in LOAD DATA and ALTER TABLE operations.
InnoDB does not allow you to delete a table referenced by the FOREIGN KEY table constraint unless you set SET FOREIGN_KEY_CHECKS=0. When you remove a table, the constraints defined in its create statement are also removed.
If you recreate a removed table, it must have a definition that conforms to the foreign key constraint that also references it. It must have the correct column name and type, and, as mentioned earlier, it must have an index on the referenced key. If these are not satisfied, MySQL returns the error number 1005 and points to errno 150 in the error message string.

Related articles: