Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by KnowledgeWoods Consulting
KnowledgeWoods Consulting
5,607 Points

Make Unique Index of Multiple Columns

I have a mysql table. What i want to do is...i have 3 columns Studentid, Courseid, is_paid

Now i want to make the combination of these 3 columns unique...But only when is_paid='No'. If is_paid='Yes' then the constraint should not apply. Any Idea about this?

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

What do you mean by "make the combination of these 3 columns unique"? Do you want to delete any duplicate records?

And paid or not, why would a student have more than one record for the same course? (or is that the error you are trying to correct?)

3 Answers

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

Assuming my guesses in the comment above are correct, you could do this:

DELETE FROM the_table t
  WHERE (SELECT COUNT(*) FROM the_table
         WHERE Studentid = t.Studentid
           AND Courseid = t.Courseid
           AND is_paid = t.is_paid) > 1
    AND is_paid = 'No';
KnowledgeWoods Consulting
KnowledgeWoods Consulting
5,607 Points

Hi Steven,

Actually i am using the mysql query browser. I want 3 columns in it to be unique such that the combination of these 3 columns do not insert again. But only when the last column is_paid='No'. If the last column value is 'Yes' then the rows can repeat.

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

That sounds like you want a before insert trigger that checks for an existing identical record and then aborts the insert. My MySql is a bit rusty; I do that kind of thing in Oracle, but I'm not sure how or if MySql does it.

KnowledgeWoods Consulting
KnowledgeWoods Consulting
5,607 Points
KnowledgeWoods Consulting
KnowledgeWoods Consulting
5,607 Points

Yes right!

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

In MySql, I don't think you can throw an exception in a trigger to abort the function. But here's an idea, make the trigger delete any rows that match the one you're about to insert:

delimiter $$
CREATE TRIGGER myTrigger
   BEFORE INSERT ON the_table
   FOR EACH ROW
     DELETE FROM the_table
         WHERE Studentid = NEW.Studentid
           AND Courseid = NEW.Courseid
           AND is_paid = NEW.is_paid
           AND is_paid = 'No';
$$
delimiter ;

Another idea would be to make sure one of the columns is a "NOT NULL" column, and have the trigger set that value to null if the new row matches an existing one. That will cause the insert to fail.