-1

I want to add a UNIQUE INDEX on 3 fields when one of the fields is True. ie equal to 1

UNIQUE INDEX pkg_box_delivered (package_id, box_id, delivered) WHERE delivered = 1;

I need a Alter statement and how add it to a Create Table statement.

Avril
  • 25
  • 4
  • To be clear, you want to enforce a constraint that no two records have the same values for `package_id` and `box_id` - but only where `delivered = 1`? – eggyal Jul 23 '13 at 08:39

1 Answers1

0

You can't create an index on some of the rows in a table - only on all the rows in a table regardless of value.

I need a Alter statement and how add it to a Create Table statement

You use ALTER TABLE to change the definition of an existing table, while CREATE TABLE creates a table and it's fields, hence if the table exists already....

 ALTER TABLE yourtablename 
   ADD UNIQUE INDEX pkg_box_delivered (package_id, box_id, delivered);

or if it does not already exist....

 CREATE TABLE yourtablename (
         package_id INT NOT NULL,
         box_id INT NOT NULL,
         delivered BOOLEAN,
         ...
         UNIQUE INDEX pkg_box_delivered (package_id, box_id, delivered)
 );

Just as you can't selectively apply an index to some rows, you can't selectively apply the unique constraint. You'd need to add a trigger to enforce this.

symcbean
  • 47,736
  • 6
  • 59
  • 94
  • Thank you but I already knew those statements. Problem is I only want those to be unique when delivered = 1 but I can't have trigger as I don't have access to when items are entered into the table. – Avril Jul 23 '13 at 09:07
  • I don't understand what you mean by "I...want those to be unique when delivered = 1" If you mean that the combined valuie of the fields is only unique when delivered=1 then see the last sentence in my answer. – symcbean Jul 23 '13 at 10:12
  • I understand I need a trigger now but I have no idea what it should look like. Also I already have data in my database which I can not delete. – Avril Jul 23 '13 at 10:25