Mysql - Unique field based on 2 columns while ignoring null values

Question

I have a table with the following fields

id - (int) primary
project_id - (int)
post_id - (int)

I need to set it so that the post_id field is unique but only on a specific project id.

ie:

id || project_id || post_id

1     1             1
2     1             2
3     1             3
4     2             1
5     2             2
6     2             3

is this possible?

EDIT: I now have a new problem, sometimes the post_id field will be null, is there a way of using

      ALTER TABLE TableName
      ADD CONSTRAINT uc_PostProject UNIQUE (post_id,project_id)

But ignore null post_id's?

possible duplicate of [How do I specify unique constraint for multiple columns in MySQL?](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) — saamorim, Jul 12 '13 at 09:56

score 0 · Accepted Answer · answered Jul 12 '13 at 09:55

0

Add the following

ALTER TABLE TableName
ADD CONSTRAINT uc_PostProject UNIQUE (post_id,project_id)

For more information see http://www.w3schools.com/sql/sql_unique.asp

answered Jul 12 '13 at 09:55

saamorim

3,685
15
22

Mysql - Unique field based on 2 columns while ignoring null values

1 Answers1