MySQL Constraint for Unique Values

Question

How would you go about creating a table with a constraint in MySQL that only applies to 1 particular table where you want 1 field to be unique for each unique record?

For example, let's say that I have a table called "person" with the following fields:

person_social_security (primary key)
person_id
person_name
person_phone_extension

For each record, I want to make sure that field #3 (person_phone_extension) is also always unique for every person_id. In other words, a person_id should never have a duplicate person_phone_extension. However, a different person_id can have the same person_phone_extension as another person_id. There can be multiple instances of person_id in this table.

If I understand you, you can have multiple records with the same person_id, so what does that column represent? — Darwin von Corax
– Darwin von Corax, Commented Nov 13, 2015 at 7:20

Alex · Accepted Answer · 2015-11-13 00:15:21Z

2

http://sqlfiddle.com/#!9/df732/2

just set UNIQUE KEY against your column:

create table person (
person_id int auto_increment primary key,
person_name varchar(50),
person_phone_extension int unique key);

using my fiddle you can see if you uncomment my last query that will bring mysql error about duplicate value.

UPDATE According to your new comments person_id is not primary key and not unique. But combination of person_id+person_phone_extension should be checked. So you need to set a key against those 2 columns like:

http://sqlfiddle.com/#!9/51547/1

create table person (
person_id int,
person_name varchar(50),
person_phone_extension int,
unique key idx (person_id,person_phone_extension));

edited Nov 13, 2015 at 0:15

answered Nov 12, 2015 at 21:05

Alex

17.3k1 gold badge31 silver badges52 bronze badges

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

1 Comment

THE DOCTOR Over a year ago

This SQL fiddle site is awesome for testing things out! Though this isn't quite what I was looking for. Each person_id cannot have a duplicate person_phone_extension, but a different person_id can have the same person_phone_extension. I'll edit my original question to be a little more clear on that.

Collectives™ on Stack Overflow

MySQL Constraint for Unique Values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related