CODEWITHSUNDEEP
mysqlsqldatabaseconstraintsunique
THE DOCTOR
THE DOCTOR

Reputation: 4555

MySQL Constraint for Unique Values

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:

  1. person_social_security (primary key)
  2. person_id
  3. person_name
  4. 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.

Upvotes: 0

Views: 112

Answers (1)

Alex
Alex

Reputation: 17289

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));

Upvotes: 2

Related Questions