How do I create a table constraint to prevent duplicate values across two columns?

Question

I have the following table:

CREATE TABLE [dbo].[EntityAttributeRelship](
    [IdNmb] [int] IDENTITY(1,1) NOT NULL,
    [EntityIdNmb] [int] NOT NULL,
    [AttributeIdNmb] [int] NOT NULL,
    [IsActive] [bit] NOT NULL CONSTRAINT [DF_EntityAttributeRelship_IsActive]  DEFAULT ((0)),
CONSTRAINT [PK_EntityAttributeRelship] PRIMARY KEY CLUSTERED 
([IdNmb] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

A portion of the data in the table looks like something this:

IdNmb    EntityIdNmb    AttributeIdNmb  IsActive
1        22             7               0
2        22             8               0
3        22             9               0
4        22             10              1

I want to add a constraint to make sure that no one adds or updates a record to have IsActive = 1, if there is already a record for the EntityIdNmb where IsActive = 1.

How do I do this?

Answer 1

Sounds like you need to implement a trigger (assuming your db product supports it). If you only want one active and one inactive entry, a unique index will work. Otherwise, you'll need to write some sort of custom constraint or a trigger (probably 2 - one for inserts, one for updates) that makes sure that you don't have 2 records with the same id where both are active.

Answer 2

What will the inactive records be used for? Are they unused and simply there to keep track of previously active records? If so, would it be possible to split the data in to multiple tables? Something like...

EntityAttributeRelship(IDNmb, EntityIDNmb, AttributeIDNmb)

EntityAttributeRelshipHistory(IDNmb, EntityIDNmb, AttributeIDNmb)

If it's in the EntityAttributeRelship table, it's active. If it's in the history table then it was activated at some point and has since been deactivated?

If you do need everything in one table I would go with todd.run's suggestion of using a trigger.

Answer 3

If you are using SQLServer you can create a clustered indexed view.

CREATE VIEW dbo.VIEW_EntityAttributeRelship WITH SCHEMABINDING AS
SELECT EntityIdNmb 
FROM dbo.EntityAttributeRelship
WHERE IsActive = 1
GO

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_ENTITYATTRIBUTERELSHIP 
  ON dbo.VIEW_EntityAttributeRelship (EntityIdNmb)

This ensures there's only one EntityIdNmb in your table with IsActive = 1.

Answer 4

If you're using MSSQL (I think that's what your syntax looks like), Create a view including only the rows with IsActive = 1, then put a unique index on EntityIdNmb in the view.

In PostgreSQL, which I've worked more with recently, you can create a partial index: http://www.postgresql.org/docs/8.3/interactive/indexes-partial.html

Answer 5

The thing with writing the trigger is to decide whether you want to reject the record, change the value to 0 instead of one or update the old record to zero and let this one stand as one. If you are deleting the record with the value of 1, do you need to change another record to be the active one, how to do you choose which one? Once you can define waht you want to do within the trigger, we can help you better to design the process.

We do the latter two steps to make any address the main mailing address in our database. Our business rule is one and only one address can be the main one and if there are any addresses one must be marked as the main one. The key to this sort of trigger is to remember that inserts/updates/deletes can occur in batches (even if this is the not the norm) and to make sure the trigger works in a set-based fashion. When I got here, ours implemented multi-row processing through a cursor which became a bad thing when I had to update 200,000 addresss in an import. (Note to the inexperienced - do not ever use a cursor in a trigger!)