MySQL. UNIQUE and PRIMARY KEY constraints for the same field

Question

I found some legacy code, which sets two almost identical constraints (UNIQUE and PRIMARY KEY) for the primary key field.

Here is the code sample:

CREATE TABLE foofoo ( id NUMERIC(9) NOT NULL , bar VARCHAR(40) NOT NULL, CONSTRAINT PK_foofoo PRIMARY KEY (id), CONSTRAINT UNIQUE_foofoo UNIQUE(id) )

I think it's redundant to have these two set and PRIMARY KEY would do the job.

Of course, I read what's the difference between these two constraints, but

what's the point of setting these two constraints for the same field?

Possible duplicate of difference between primary key and unique key — e4c5
– e4c5, Commented Nov 23, 2016 at 14:00
e4c5 I'm not asking for difference, I'm asking why anyone should set both of these constraints for the same field. Maybe there's a reason behind that. — Benas
– Benas, Commented Nov 23, 2016 at 14:02
@e4c5, how do you think it's a duplicate of that linked answer? can you explain. — Rahul
– Rahul, Commented Nov 23, 2016 at 14:07
There's a very nice upside of doing this - knowing who did that tells you that it's a person who you don't want as your co-worker. It's blatantly stupid to do this, it makes no sense. It's like adding more water to a glass of water to make water wetter or adding white color to white color to make it whiter. The guy who did that had no clue what he was doing at that point of his IT life. Classic human. — Mjh
– Mjh, Commented Nov 23, 2016 at 16:05

Mathieu Turcotte · Accepted Answer · 2016-11-23 14:02:27Z

4

There is no point in doing so. A primary key is always unique by nature. I would advise against making both indexes, as indexes comes with a cost (mainly disk space). Just create the PK and you'll be good!

answered Nov 23, 2016 at 14:02

Mathieu Turcotte

3542 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mathieu Turcotte Over a year ago

Also, since you are asking this question, I strongly recommend you to read as much as you can on indexes. They are a wonderful thing, extremely handy, but they are kind of a double-edged sword. They can become very huge if used carelessly, but can do wonders if used right. A simple comment on here wold barely scratch the surface, so yeah, I recommend reading on the subject. By adding a simple index, I've sped up queries from literally a few minutes to a couple of seconds. But again, be mindful of what you do, they can end up costing you more than they give you.

Mathieu Turcotte Over a year ago

I've seen databases with literally gigabites of useless indexes... Also don'T forget those are also backed up with your database. So if you setup frequent backups, like every night or something like that, your indexes will not only make your production database bigger, but will also make bigger your backups and your development database(s) if any. So again, read up on them, they are wonderful, but don't abuse them!

sagi · Accepted Answer · 2016-11-23 13:59:21Z

2

There is no point on setting the exact same constraint as the PK.

A Primary Key is already making sure that this column is unique and indexed.

answered Nov 23, 2016 at 13:59

sagi

40.6k7 gold badges66 silver badges87 bronze badges

1 Comment

Nathan Champion Over a year ago

In SQL Server a primary key is not necessarily clustered.

Rahul · Accepted Answer · 2016-11-23 14:06:02Z

I think it's redundant ...

Yes indeed it's redundant; since having primary key constraint on the column anyways will make sure that the column has only unique value. There is no point in defining an extra UNIQUE constraint on the same column.

Prashant Kumar Singh · Accepted Answer · 2016-11-23 15:58:32Z

when you declare primary then: *PRIMARY KEY constraint uniquely identifies each record in a database table *Primary keys must contain UNIQUE values so their is no need to declare primary key unique because whenever u declare anything primary key then UNIQUE value is already attached with them. For unique key: *The UNIQUE constraint uniquely identifies each record in a database table. *The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. *A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. The most important point is that *Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. In mysql when i take same as primary key and Unique then it gave me error

Collectives™ on Stack Overflow

MySQL. UNIQUE and PRIMARY KEY constraints for the same field

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Hot Network Questions