CODEWITHSUNDEEP
sql-serversql-server-2008-r2constraints
przemo_li
przemo_li

Reputation: 4053

TSQL constrain so that there can not be to rows with same column A but different column B values?

Need constrain that will refuse insert/update if there is already some row with same value in column A but different value in B.

So:

Insert TableName(A, B) Values(x, y)
Insert TableName(A, B) Values(x, y)
Insert TableName(A, B) Values(x, z)

Will fail on the third inser as y =/=z. Or even on the first insert if there was already row (x, w) in the table.

On the other hand those inserts are valid:

Insert TableName(A, B) Values(c, y)
Insert TableName(A, B) Values(d, y)

Last remark. Data won't be normalized. Non normalized form is a feature not a bug. That's why I ask for constrain.

SQL Server 2008R2.

Upvotes: 1

Views: 45

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

You can achieve this with an indexed view:

create table dbo.T (
    A char(1) not null,
    B char(1) not null
)
go
create view dbo.V_T
with schemabinding
as
    select
        A,
        B,
        COUNT_BIG(*) as Cnt
    from
        dbo.T
    group by
        A,B
go
create unique clustered index IX_V_T on dbo.V_T (A)
go
Insert T(A, B) Values('x', 'y')
Insert T(A, B) Values('x', 'y')
Insert T(A, B) Values('x', 'z')

The third insert generates the error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.V_T' with unique index 'IX_V_T'. The duplicate key value is (x).
The statement has been terminated.

And continuing:

Insert T(A, B) Values('c', 'y')
Insert T(A, B) Values('d', 'y')

Both execute fine.

The design of the V_T table is such that it has one row for each unique A,B combination (the COUNT_BIG(*) is just a requirement to allow this to be an indexed view, we're not allowed to use DISTINCT). We then simply put a unique index on the A values. Therefore, this view can only contain one row for each possible A value or the unique constraint will be violated.

Upvotes: 1

gofr1
gofr1

Reputation: 15987

You can use INSTEAD OF trigger (with fixes based on comments of @Damien_The_Unbeliever):

At first create a table:

CREATE TABLE temp (
    A nchar(1),
    B nchar(1)
);

Create trigger:

CREATE TRIGGER testtemp
ON temp
INSTEAD OF INSERT, UPDATE
AS 
INSERT INTO temp
SELECT  A,
        B
FROM (
    SELECT  i.*,
            DENSE_RANK() OVER (PARTITION BY i.A ORDER BY i.A,i.B) as DR
    FROM inserted i
    OUTER APPLY (
            SELECT TOP 1 *
            FROM temp
            WHERE A = i.A
        ) t
    WHERE t.b IS NULL OR t.B = i.B
    ) C
where dr = 1

DELETE t
FROM temp t
INNER JOIN deleted d
    ON d.A=t.A and d.B=t.B;

Then run:

Insert temp(A, B) Values
('x','y'),
('x','y'),
('x','z'),
('c','y'),
('d','y'),
('a','b'),
('a','c');

Output:

A   B
a   b
c   y
d   y
x   y
x   y

Upvotes: 2

Related Questions