CODEWITHSUNDEEP
sql-serverforeign-keys
Ruslan K.
Ruslan K.

Reputation: 1981

SQL Server - conditional / filtered foreign key. Another way

Often we want to reference from Table2 only to rows in Table1 that satisfy some predicate. Like this:

CREATE TABLE dbo.Table1 (
    Id       int NOT NULL,
    IsActive bit NOT NULL,

    CONSTRAINT PK_Table1 PRIMARY KEY ( Id )
);

CREATE TABLE dbo.Table2 (
    Id       int NOT NULL,
    Table1Id int NOT NULL
);

/* Using WHERE is unsupported: */
ALTER TABLE dbo.Table2 
    ADD CONSTRAINT FK_Table2_to_Table1
        FOREIGN KEY ( Table1Id ) REFERENCES Table1 ( Id ) WHERE IsActive = 1;

But this code does not work. Usually in this case there is recommendation to add column IsActive in Table2 (always equal to 1) and add that FK:

ALTER TABLE dbo.Table2
    ADD IsActive AS (CONVERT(bit,1)) PERSISTED NOT NULL;

ALTER TABLE dbo.Table1
    ADD CONSTRAINT UK_Id_IsActive
        UNIQUE ( Id, IsActive );

ALTER TABLE dbo.Table2
    ADD CONSTRAINT FK_Table2_to_Table1
        FOREIGN KEY ( Table1Id, IsActive ) REFERENCES Table1 ( Id, IsActive );

Example in this question: https://dba.stackexchange.com/questions/188379/filtered-foreign-keys

But if we have 10 rows in Table1 and 1 billion rows in Table2 we should store many redundant data.

Can we solve this problem without this overhead?

Upvotes: 2

Views: 1516

Answers (1)

Ruslan K.
Ruslan K.

Reputation: 1981

create table dbo.Table1 (
    Id int not null primary key clustered,
    IsActive bit not null,
    ActiveIdForForeignKey as iif(IsActive = 1, Id, -Id) persisted not null,
    constraint UQ_ActiveIdForForeignKey unique (ActiveIdForForeignKey)
);
go
create table dbo.Table2 (Id int not null, Table1Id int not null);
go
alter table dbo.Table2 add constraint FK_Table2_Table1 foreign key (Table1Id) references Table1(Id);
alter table dbo.Table2 add constraint FK_Table2_Table1_Active foreign key (Table1Id) references Table1(ActiveIdForForeignKey);
go
insert into dbo.Table1(Id, IsActive) values (1, 0);
insert into dbo.Table1(Id, IsActive) values (2, 1);
insert into dbo.Table1(Id, IsActive) values (3, 0);
go
insert into dbo.Table2(Id, Table1Id) values (1, 2); -- success
insert into dbo.Table2(Id, Table1Id) values (2, 1); -- fail
go

This is working without data store overhead for Table2.

Upvotes: 3

Related Questions