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?
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.