2

In MySQL 5.7

  1. alter table mydb.roles add constraint uk1 unique (role_name);

  2. alter table mydb.roles add constraint uk2 unique (role_name);

The 2nd alter command threw me a warning stating:

Duplicate index 'uk2' defined on the table 'world_x.roles'. This is deprecated and will be disallowed in a future release.

When I tried the same in Postgres(10.x), it didn't warn me.

So, what would the SQL standard be in this scenario?

  • 3
    I don't know the answer, but I guess that the SQL standard doesn't say much about duplicate constraints. I'm almost certain that it says nothing about duplicate indexes, since indexes are considered a physical implementation, and not part of the standard. – Lennart Aug 14 '18 at 8:58
  • your error message isn't possible as you don't mention uk3 in that code anywhere. – Evan Carroll Aug 16 '18 at 22:47
  • @EvanCarroll edited/correct the same – Dinesh Kumar Aug 17 '18 at 11:58
1

It can be result very confusing mixing names constraints and slow down your queries because they have the same name

maybe this results helpful

CREATE ONLY ONE CONSTRAINT INDEX AND INCLUDE THOSE NECESSARY COLUMNS

Example

ALTER TABLE users ADD CONSTRAINT findUser UNIQUE (nameUser,emailUser);

No double constraint and no warning message and you keep the SQL rules alive

Remember SQL is an standard but not implemented at the same time for everyone with the same rules

As you can see at the following link, in fact these scenario is a bug

https://bugs.mysql.com/bug.php?id=37520

and finally recognized in: https://bugs.mysql.com/bug.php?id=8565

and finally fixed in MySQL Server 5.6.7

  • He's using MySQL 5.7 (which is post-patch). On the contrary, he's getting the error message and complaining about it. The is no bug, he's suppose to get that message. There was a bug when there was no message. – Evan Carroll Aug 17 '18 at 6:38
0

When I tried the same in Postgres(10.x), it didn't warn me.

This simply isn't true.

CREATE TABLE foo ( a int );
ALTER TABLE foo ADD CONSTRAINT uk1 UNIQUE (a);
ALTER TABLE foo ADD CONSTRAINT uk1 UNIQUE (a);
ERROR:  relation "uk1" already exists

However, in PostgreSQL most people don't name anything because the system is awesome and the auto-generated names work fine,

ALTER TABLE foo ADD UNIQUE (a); -- run once
ALTER TABLE foo ADD UNIQUE (a); -- run twice (no problem)

-- run thirce, and four times
ALTER TABLE foo ADD UNIQUE (a), ADD UNIQUE (a);

So, what would the SQL standard be in this scenario?

This is called a <table constraint definition> in the spec, which says this (SQL:2011n).

4) Let S be the schema identified by the explicit or implicit of the <constraint name>. S shall not include a constraint descriptor whose constraint name is <constraint name>.

Duplicate constraints are not permitted for the entire schema. As an aside, the spec also states the implementation can not generate implicit names that conflict (must be unique).

  • ,as It is clear that there are SQL standards on avoiding duplicate constraint names. So, the constraint (constraint created on same column, uk1(col1),uk2(col1)...)can be created 'N' times with unique names for each. – Dinesh Kumar Aug 17 '18 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.