[MDEV-10038] ALTER TABLE DROP CONSTRAINT not working for unique constraints Created: 2016-05-06  Updated: 2018-01-05  Resolved: 2016-05-09

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.1.12
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Rogier Schouten Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Windows 7 Professional

Issue Links:
Relates
relates to MDEV-14873 ALTER TABLE DROP CONSTRAINT does not ... Closed

 Description   

Create a table 'newtable' with a varchar column 'myfield'.

Add a unique constraint:
ALTER TABLE newtable ADD CONSTRAINT UNIQUE bla (myfield);

Drop the constraint again
ALTER TABLE newtable DROP CONSTRAINT bla RESTRICT;

You get an error 'You have an error in your SQL syntax'.

According to the knowledge base, this should work (it's basically copy-pasted):
https://mariadb.com/kb/en/sql-99/alter-table-statement/#drop-constraint-clause

Workaround: use DROP INDEX instread of DROP CONSTRAINT
ALTER IGNORE TABLE newtable DROP INDEX bla;

 Comments   
Comment by Elena Stepanova [ 2016-05-09 ]

Hi,

You are quoting not MariaDB (or MySQL) documentation, but SQL-99 standard, which is also provided in the KB, but in a separate section.

This syntax has never been implemented or documented either in MySQL or MariaDB.

Generated at Thu Feb 08 07:39:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.