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](http://stackoverflow.com/questions/9565996/difference-between-primary-key-and-unique-key) — e4c5, Nov 23 '16 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, Nov 23 '16 at 14:02
@e4c5, how do you think it's a duplicate of that linked answer? can you explain. — Rahul, Nov 23 '16 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, Nov 23 '16 at 16:05

score 3 · Accepted Answer · answered Nov 23 '16 at 14:02

3

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 '16 at 14:02

Mathieu Turcotte

344
2
14

1

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 Nov 24 '16 at 14:22
1

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! – Mathieu Turcotte Nov 24 '16 at 14:24

score 2 · Answer 2 · answered Nov 23 '16 at 13:59

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 '16 at 13:59

sagi

40,026
6
59
84

In SQL Server a primary key is not necessarily clustered. – Nathan Champion Jan 16 '19 at 05:51

score 2 · Answer 3 · answered Nov 23 '16 at 14:06

2

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.

answered Nov 23 '16 at 14:06

Rahul

76,197
13
71
125

score 1 · Answer 4 · answered Nov 23 '16 at 15:58

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

MySQL. UNIQUE and PRIMARY KEY constraints for the same field

4 Answers4