Vyhledávání
Přidávání a mazání cizích klíčů
Přidání cizího klíče k obyčejnému sloupci:
ALTER TABLE `table`
ADD CONSTRAINT table_fk_colname FOREIGN KEY (colname) REFERENCES other_table (other_col);
Drop cizího klíče:
ALTER TABLE table_name DROP FOREIGN KEY table_fk_colname;
Změna pořadí sloupců v tabulce
Pořadí sloupců jde změnit příkazem ALTER TABLE ... MODIFY.
Při zadávání vlastností sloupců je dobré využít příkazu SHOW CREATE TABLE.
mysql> CREATE TABLE test (a INT, b VARCHAR(3), c DATETIME);
Query OK, 0 rows affected (0.04 sec)
mysql> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | varchar(3) | YES | | NULL | |
| c | datetime | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE test MODIFY b VARCHAR(3) AFTER c;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| c | datetime | YES | | NULL | |
| b | varchar(3) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Query OK, 0 rows affected (0.04 sec)
mysql> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | varchar(3) | YES | | NULL | |
| c | datetime | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE test MODIFY b VARCHAR(3) AFTER c;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| c | datetime | YES | | NULL | |
| b | varchar(3) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Zdroj: http://forums.phpfreaks.com/topic/125927-solved-move-column-order-with-phpmyadmin/