CODEWITHSUNDEEP
mysqldatabase
Sourav Ghosh
Sourav Ghosh

Reputation: 2024

mysql: unique value in a field with respect to another field

suppose I have a database table with 3 fields col1, col2, col3 say, col1 is the primary index.

I konw that we can set a field as unique.

can I do something in structure that col3 should be unique with respect to col2, that is, for a value in col2, there cannot be 2 same values in col3.

or I have do do this validation in server side before passing the data to database?

example-- (1,10,3) (2,10,4) (3,11,3) is valid, but (1,10,3) (2,10,4) (3,11,3) (4,11,3) is invalid.

one possible solution that came to my mind that-- store the value of col2 and col3 in a separate field like "col2value-col3value" and make that field unique.

I am using mysql.

I am a newbie. so, a little details would be helpful. Thank you.

Upvotes: 0

Views: 229

Answers (2)

Snick
Snick

Reputation: 1042

The concept of primary key can be extended to more than one column. You can find this referenced also as Composite Primary Key.

You need to add col2 and col3 to the set of primary key

In this way there can't be a record with col2 and col3 equal to another record already available in the table

In creation phase, please define the table in this way:

CREATE TABLE myTable (column1 INT NOT NULL, column2 INT NOT NULL, column3 INT NOT NULL,
                      PRIMARY KEY(column1, column2, column3))

Additional informations can be found here.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You just need to make the combination of col2, col3 unique. This does not require making them a primary key. I would do it my creating a unique index on the pair:

create unique index idx_tbl_col2_col3 on (col2, col3)

As a note: NULL values are ignored for this purpose. So, you would still be allowed to have identical values, if one is NULL. One option is to declare the columns NOT NULL. Another would be to use triggers to prevent duplicates.

Upvotes: 2

Related Questions