Alter table to drop primary key and foreign key : Foreign Keys : Table MySQL TUTORIALS


MySQL TUTORIALS » Table » Foreign Keys »

 

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, rows affected (0.08 sec)

mysql>
mysql>
mysql> CREATE TABLE Books
    -> (
    ->    BookID SMALLINT NOT NULL,
    ->    BookName VARCHAR(40NOT NULL,
    ->    PubID SMALLINT NOT NULL DEFAULT 1
    -> )
    -> ENGINE=INNODB;
Query OK, rows affected (0.06 sec)

mysql>
mysql>
mysql> ALTER TABLE Books
    -> ADD PRIMARY KEY (BookID),
    -> ADD CONSTRAINT fk_1 FOREIGN KEY (PubIDREFERENCES 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       |       |
+----------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql>
mysql> desc Publishers;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| PubID | smallint(6| NO   |     | 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       |       |
+----------+-------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql>
mysql> desc Publishers;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| PubID | smallint(6| NO   |     | 1       |       |
+-------+-------------+------+-----+---------+-------+
row in set (0.01 sec)

mysql>
mysql> drop table Books;
Query OK, rows affected (0.03 sec)

mysql>
mysql> drop table Publishers;
Query OK, rows affected (0.03 sec)



Leave a Comment / Note


 
Verification is used to prevent unwanted posts (spam). .

Follow Navioo On Twitter

MySQL TUTORIALS

 Navioo Table
» Foreign Keys