Post reply

Unique constraint for boolean

  • August 24, 2005 at 9:23 pm

    #90796

    Hi, I have a table that relates 2 tables for a many-many relationship type.

    2 columns are keys of other tables: 'IdLocacion' and 'IdCliente

    1 column is a boolean: Fiscal

    I want to set a constraint that lets only one True value for 'Fiscal' for each equal combination of 'IdLocacion' and 'IdCliente'. SQL Server 2000 tells me that there can't be constraints of booleans. How can I do it?

  • August 24, 2005 at 10:01 pm

    #584854

    Well there can be constraints but those are not the types of columns you want to index (a unique constraint is an index). What you need to do is write a trigger that checks if there's already a combinaison IdLocation and IdCliente with fiscal = true. If there is, then you rollback the transaction and raise an error.

  • August 25, 2005 at 7:15 pm

    #585248

    In SQL Server a UNIQUE Constraint can allow NULL while UNIQUE index cannot.  I don't think BIT which is boolean in SQL Server can accept NULL.  Try the link below for more info about UNIQUE Constraint.  Hope this helps.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdbref/html/dvhowcreatinguniqueconstraint.asp

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply