Alter table to drop primary key and foreign key
|
|
|
mysql>
mysql>
mysql> CREATE TABLE Publishers
-> (
-> PubID SMALLINT NOT NULL DEFAULT 1
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql>
mysql> CREATE TABLE Books
-> (
-> BookID SMALLINT NOT NULL,
-> BookName VARCHAR(40) NOT NULL,
-> PubID SMALLINT NOT NULL DEFAULT 1
-> )
-> ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql>
mysql> ALTER TABLE Books
-> ADD PRIMARY KEY (BookID),
-> ADD CONSTRAINT fk_1 FOREIGN KEY (PubID) REFERENCES Publishers (PubID),
-> ADD COLUMN Format ENUM('paperback', 'hardcover') NOT NULL AFTER BookName;
ERROR 1005 (HY000): Can't create table '.test#sql-a98_3a.frm' (errno: 150)
mysql>
mysql> desc Books;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| BookID | smallint(6) | NO | | | |
| BookName | varchar(40) | NO | | | |
| PubID | smallint(6) | NO | | 1 | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> desc Publishers;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| PubID | smallint(6) | NO | | 1 | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> ALTER TABLE Books
-> DROP PRIMARY KEY,
-> DROP FOREIGN KEY fk_1,
-> DROP COLUMN Format;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists
mysql>
mysql>
mysql> desc Books;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| BookID | smallint(6) | NO | | | |
| BookName | varchar(40) | NO | | | |
| PubID | smallint(6) | NO | | 1 | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> desc Publishers;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| PubID | smallint(6) | NO | | 1 | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql>
mysql> drop table Books;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> drop table Publishers;
Query OK, 0 rows affected (0.03 sec)
|
|
|
Related Scripts with Example Source Code in same category :
-
-
-
-
-
-
|
|
