CODEWITHSUNDEEP
mysql
eaponz
eaponz

Reputation: 604

Selecting a unique row

How can I achieve this kind of uniqueness.. no column unique only the row, ex. ID # 2 has a record in October, ID # 2 must not have 2 info with res_month = 'October'

res_id | res_month | res_year
   1   | September | 2013
   2   | September | 2013
   3   | September | 2013
   4   | September | 2013 
   2   | October   | 2013
   3   | October   | 2013
   4   | October   | 2013

Upvotes: 1

Views: 61

Answers (2)

Kaii
Kaii

Reputation: 20550

You can build a UNIQUE KEY contraint over multiple fields:

ALTER TABLE tbl ADD UNIQUE KEY foo (res_id, res_month, res_year);

(foo is just the name for the contraint - you may want to choose a more explanatory name)

This constraint ensures that each combination of res_id, res_month and res_year can only occur once.

With this in place an INSERT with an already stored combination yields a Unique constraint failed error. In your example, this would fail because the combination already exists:

INSERT INTO tbl (res_id, res_month, res_year) VALUES (2, 'October', '2013');

Upvotes: 1

Deepak Rai
Deepak Rai

Reputation: 2203

Add unique constraint on res_id column This won't allow duplicate values in res_id column

Upvotes: 0

Related Questions