How to 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]

Part of the data in the table looks like 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 restriction to make sure that no one is adding or updating the record in order to have IsActive = 1 if there is already an entry for EntityIdNmb, where IsActive = 1.

How to do it?

Answer 1

If you use 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 that your table will only have one EntityIdNmb with IsActive = 1.

Answer 2

, (, db ). , . - (, 2 - , ), , , .

Answer 3

MSSQL ( , ), , IsActive = 1, EntityIdNmb .

PostgreSQL, , : http://www.postgresql.org/docs/8.3/interactive/indexes-partial.html

Answer 4

, , , 0 . 1, , , , ? , , .

, . - - , , - , . , , // ( ) , . , , , 200 000 . ( - !)

Answer 5

? , ? , ? - ...

EntityAttributeRelship (IDNmb, EntityIDNmb, AttributeIDNmb)

EntityAttributeRelshipHistory (IDNmb, EntityIDNmb, AttributeIDNmb)

EntityAttributeRelship, . - ?

, todd.run .