Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011

    Unanswered: Unique Constraint


    I want to implement something that will act as a UNIQUE constraint on a table, but I need a little more flexibility.

    I want to implement a three-column UNIQUE constraint. However, one of the columns may be NULLs and I would want, essentially, that the NULLs be considered equal amongst them.

    Each of the columns define part of a scheme to identify objects. For instance, a row could have the values (1, 3, NULL). This would translate to a CQp.kVI.3, for instance. On the other hand, I don't want another (1,3,NULL) row to be added to the DB.

    Any ideas?

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    I believe you've got two approaches you could take: add either a before insert/update trigger, or a rule, to monitor inserts/updates to table.field2, and when the count of records with field2 is null (and field1 = new.field1) then raise an error if using a trigger, or an 'Instead do nothing' if using rules.

    Although I've needed to use it very little, the rule system is the preferred approach when it will work for you.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    Jun 2011
    Thank you! I'll try the rules system.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts