CODEWITHSUNDEEP
sqlsql-server
Abhishek Bhatia
Abhishek Bhatia

Reputation: 716

Inter-column uniqueness constraint

DB structure :

Table 1 (T1):

|I1|I2|I3|
|1 |1 |1 |
|2 |2 |2 |
    .
    .

Contraints:

Table 2 (T2):

|AI1|AI2|AI3|BI1|BI2|BI3|
|1  |1  |1  |2  |2  |2  |
            .
            .

Contraints:

What I want to Add
With currently mentioned constraints, there is still a possibility to add another row with such that

|2|2|2|1|1|1|

such possibility should also needs to be stopped.In other words,no combination that exists as AI1,AI2,AI3 cannot occur again as BI1,BI2,BI3

Is there any possible way to achieve this?

I thought of using trigger whenever new entry is added to T2 and later delete the newly added row, but that is not an elegant solution. Plus the code adding the row would assume that row has been added but in reality it is not persisted.

Upvotes: 1

Views: 125

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

You can do this using a helper table and an indexed view:

create table dbo.Two (
    N int not null,
    constraint PK_Two PRIMARY KEY (N),
    constraint CK_Two_Two CHECK (N in (1,2))
)
go
insert into dbo.Two(N) values (1),(2)
go
create view dbo.DRI_T2_MoreUnique
with schemabinding
as
    select
        CASE WHEN N=1 THEN AI1 ELSE BI1 END as I1,
        CASE WHEN N=1 THEN AI2 ELSE BI2 END as I2,
        CASE WHEN N=1 THEN AI3 ELSE BI3 END as I3
    from
        dbo.T2
            inner join
        dbo.Two
            on
                1=1
go

If you have a numbers table, you can use that as the helper rather than creating the special Two table, just make sure that you're only selecting two rows from it within the view.

Basically, the helper table is there to assist us in performing a partial unpivot operation, but in a way that is compatible with indexed views.

Complete script:

create table dbo.T1 (
    I1 int not null,
    I2 int not null,
    I3 int not null,
    constraint PK_T1 PRIMARY KEY (I1,I2,I3)
)
go
insert into dbo.T1 (I1,I2,I3) values
(1,1,1),(2,2,2)
go
create table dbo.T2 (
    AI1 int not null,
    AI2 int not null,
    AI3 int not null,
    BI1 int not null,
    BI2 int not null,
    BI3 int not null,
    constraint PK_T2 PRIMARY KEY (AI1,AI2,AI3,BI1,BI2,BI3),
    constraint FK_T2_T1_A FOREIGN KEY (AI1,AI2,AI3)
        references T1 (I1,I2,I3),
    constraint FK_T2_T1_B FOREIGN KEY (BI1,BI2,BI3)
        references T1 (I1,I2,I3)
)
go
insert into dbo.T2 (AI1,AI2,AI3,BI1,BI2,BI3) values
(1,1,1,2,2,2)
go
create table dbo.Two (
    N int not null,
    constraint PK_Two PRIMARY KEY (N),
    constraint CK_Two_Two CHECK (N in (1,2))
)
go
insert into dbo.Two(N) values (1),(2)
go
create view dbo.DRI_T2_MoreUnique
with schemabinding
as
    select
        CASE WHEN N=1 THEN AI1 ELSE BI1 END as I1,
        CASE WHEN N=1 THEN AI2 ELSE BI2 END as I2,
        CASE WHEN N=1 THEN AI3 ELSE BI3 END as I3
    from
        dbo.T2
            inner join
        dbo.Two
            on
                1=1
go
create unique clustered index IX_DRI_T2
    on dbo.DRI_T2_MoreUnique (I1,I2,I3)
go
insert into dbo.T2 (AI1,AI2,AI3,BI1,BI2,BI3) values
(2,2,2,1,1,1)

And the final insert produces this error:

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object 'dbo.DRI_T2_MoreUnique' with unique index 'IX_DRI_T2'. The duplicate key value is (2, 2, 2).

The statement has been terminated.

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Please refer the SQL CHECK Constraint. We can use a UDF in a CHECK Constraint to Validate a Column. For more info check here

Upvotes: 1

Related Questions