MySQL Forums
Forum List  »  Stored Procedures

Re: How to drop unique constraint
Posted by: Roland Bouman
Date: February 18, 2006 01:25PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
28020
February 17, 2006 10:33PM
Re: How to drop unique constraint
46667
February 18, 2006 01:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.