Archives

The List

« MySQL Pop Quizzes: Now in Spanish and Russian! | Main | MySQL Function of the Day: Week 4, ehr, 5, uhm.. something »

MySQL Pop Quiz #23

By Carsten | April 14, 2008

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

Here’s something I came across several months back. Watch in wonder as we create a PRIMARY KEY which is already there, then drop it again, only and to see that it’s still present in the table…:

mysql> DESC t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists
mysql> ALTER TABLE t1 ADD PRIMARY KEY(i);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

Explain (or simply read the answer to see) how that sequence of events is possible.

Show answer

Do you have your own favorite MySQL question? Is there something in MySQL that has you scratching your head? Send your ideas for new pop quiz questions to carsten (at) bitbybit (dot) dk. Send it in, even if you don’t know the answer — that only makes it more interesting.
More Pop Quiz entries: WWW or RSS.

Topics: MySQL, Pop Quiz |

Comments