MySQL Forums

Hi!

[this is off topic for the sttored procedure forum, try in the general or newbie forum next time]

Yes, I keep doing it wrong too...this syntax normally works on oracle and MS SQL alike, but in mysql insists that you really specify the type of constraint for primary key and foreign key constraints, and index for unique constraints:

Alter table <table_name> drop index <constraint_name>

(in mysql, a unique constraint is actually synonymous with a unique index:

mysql> select * from information_schema.table_constraints
-> where table_schema = schema()
-> and table_name = 'un';
Empty set (0.24 sec)

mysql> create unique index uk_un on un (id);
Query OK, 0 rows affected (0.61 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from information_schema.table_constraints
-> where table_schema = schema()
-> and table_name = 'un';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| NULL | test | uk_un | test | un | UNIQUE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
1 row in set (0.00 sec)

So it does make sense when you know that. Of course, you could debate whether a unique constraint is the same as a unique index. Oracle and I believe MS SQL too distinguish between these two concepts. But consistently implement a unique constraint through a unique index
)



Edited 1 time(s). Last edit at 02/18/2006 01:26PM by Roland Bouman.