Mysql additions and deletions search and change statement usage example Summary _mysql

1. Create a column

ALTER TABLE tablename add COLNAME type NOT null default ' 0′;

Cases:

  ALTER TABLE Mmanapp_mmanmedia add appid_id integer NOT null default 372;

2. Delete Column

  ALTER TABLE tablename drop column colname;

Cases:

  ALTER TABLE mmanapp_mmanmedia drop column appid_id;

3. Create a foreign key association on a column that already exists

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}]

Cases:

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;

Cases:

 ALTER TABLE mmanapp_mmanmedia DROP FOREIGN KEY fk_mdappid

Two documents attached:
A. mysql related operations on columns and tables

Add 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 Column

ALTER TABLE T2 drop column C;

Renaming columns/changing column types

ALTER TABLE T1 change a b integer;
ALTER TABLE T1 change b b bigint not null;
ALTER TABLE infos change list tinyint not null default ' 0′;

Renaming tables

ALTER TABLE t1 rename T2;

Add index

Mysql> ALTER TABLE tablename change depno depno int (5) is not null;
Mysql> ALTER TABLE tablename ADD index index name (field name 1[, field Name 2 ...]);
Mysql> ALTER TABLE tablename Add index Emp_name (name);

Index of the Primary keyword

Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);

Index with unique restriction criteria

Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);

Delete an index

Mysql>alter table tablename DROP index emp_name;

Two. Add/delete constraint relations

InnoDB allows you to add a new foreign KEY constraint to a table with ALTER TABLE:

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}]

Remember to create the desired index 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 the exception key:

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

When you create a foreign key, if the FOREIGN KEY clause includes a constraint name, you can refer to that name to move the exception key. In addition, when the foreign key is created, the Fk_symbol value is innodb internal assurance. To find the mark when you want to remove a foreign key, use the show CREATE table statement. Examples are as follows:

Mysql> Show CREATE table Ibtest11c\g
*************************** 1. Row ***************************
    table:  ibtest11c
Create table:create Table ibtest11c (
 A Int (one) not null auto_increment,
 D Int (one) NOT NULL default ' 0′,
 B varchar ' NOT null default ',
 C varchar (175) Default NULL,
 PRIMARY key (a,d,b),
 key B (b,c), 
   key C (c),
 CONSTRAINT 0_38775 FOREIGN KEY (A, D)
REFERENCES ibtest11a (A, D) on
DELETE CASCADE on UPDATE CASCADE,
 CONSTRAINT 0_38776 FOREIGN KEY (b, c)
REFERENCES ibtest11a (b, c) on
DELETE CASCADE on UPDATE CAS CADE
) Engine=innodb charset=latin1
1 row in Set (0.01 sec)
 
mysql> ALTER TABLE ibtest11c DROP FOREIGN KE Y 0_38775;

InnoDB parser allows you to foreign KEY ... REFERENCES ... Use ' (backticks) to enclose the table and column names in the clause. 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 of the output of the show CREATE table statement:

Show CREATE TABLE tbl_name;

From this release, mysqldump also generates the correct definition of the table into the dump file, and does not forget the foreign key.
You can show a foreign key constraint on a table as follows:

Show TABLE STATUS from db_name like ' tbl_name ';

The FOREIGN KEY constraint is listed in the Comment column of the output.
When a foreign key check is performed, InnoDB sets a shared row-level lock on the child or parent record it looks after. InnoDB immediately checks the FOREIGN KEY constraint and checks for no transaction commit delay.
To make it easier to reload a table with a foreign key relationship into a dump file, Mysqldump automatically includes a statement setting Foreign_key_checks 0 in the dump output. This avoids problems associated with tables that have to be reloaded in a special order when dumps are 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 a table with foreign keys in the wrong order, this will import the table in any order. This also speeds up the import operation. Setting Foreign_key_checks to 0 is also useful for ignoring foreign key restrictions in the load data and ALTER TABLE operations.
InnoDB does not allow you to delete a table that is referenced by a FOREIGN KEY table constraint unless you do set set foreign_key_checks=0. When you remove a table, the constraints defined in its creation statement are also removed.
If you recreate a removed table, it must have a definition that complies with 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 to the referenced key. If these are not met, MySQL returns error number 1005 and points to errno 150 in the error message string.