CODEWITHSUNDEEP
sql-servert-sql
Joe Schmoe
Joe Schmoe

Reputation: 1768

Can this business rule be enforced with an index?

Consider following table:

ID, Name
==============
1, 'Name1'
1, 'Name1'
1, 'Name1'
2, 'Name2'
2, 'Name2'

Trying to insert (3, 'Name2') should fail because rows with the same Name value should always have the same ID value.

Can this be enforced with an index? I know it could be enforced with a trigger or constraint but both of these seem to be rather inelegant solutions.

Note that this question originates from real (more complex) business logic I reduced to this example to keep it simple.

Upvotes: 3

Views: 59

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

No it cannot be enforced with an index (on the base table).

It can be enforced with a CHECK constraint that calls a UDF.

It can also be enforced with a TRIGGER.

Upvotes: -1

Martin Smith
Martin Smith

Reputation: 453395

Yes it can be enforced with an index.

Not an index on the base table though. You can create a view selecting and grouping on ID, Name.

Then create a unique index on that view with key column Name

CREATE TABLE dbo.YourTable
  (
     ID   INT,
     Name VARCHAR(10)
  );

GO

CREATE VIEW dbo.SomeView
WITH SCHEMABINDING
AS
  SELECT count_big(*) AS c,
         ID,
         Name
  FROM   dbo.YourTable
  GROUP  BY ID,
            Name

GO

CREATE UNIQUE CLUSTERED INDEX SomeIndex
  ON dbo.SomeView(Name)

GO
---SUCCEEDS
INSERT INTO dbo.YourTable
VALUES      (1,'Name1'),
            (1, 'Name1'),
            (1, 'Name1'),
            (2, 'Name2'),
            (2, 'Name2');

GO

---FAILS
INSERT INTO dbo.YourTable
VALUES      (3,
             'Name2');

Upvotes: 3

Related Questions