CODEWITHSUNDEEP
sqlsql-server
Kappa100
Kappa100

Reputation: 29

Is there a way to create a conditional foreign key depending on a field's value?

Trying to create a table like

 CREATE TABLE SearchUser(
  SearchID int NOT NULL PRIMARY KEY,
  UserID int FOREIGN KEY REFERENCES Users(UserId),
  [scan_id] NVARCHAR(8),
  [scan_type] NVARCHAR(3),
  [tokens_left] INT);

where depending on scan_type value, I would like to be able to utilize different foreign keys i.e.

if [scan_type] = 'ORG' I would like [scan_id] to be a foreign key to Org(scan_id)

if [scan_type] = 'PER' I would like [scan_id] to be a foreign key to Per(scan_id)

Upvotes: 2

Views: 462

Answers (2)

Harshad Vekariya
Harshad Vekariya

Reputation: 1052

As other devs mention, it is not possible directly in nutshell. But there is still hope if you want to implement the same.

You can apply check constraint on column. You can create function for check and call it for check constraint like below.

ALTER TABLE YourTable
ADD CONSTRAINT chk_CheckFunction CHECK ( dbo.CheckFunction() = 1 );

In function you can write your logic accordingly.

CREATE FUNCTION dbo.CheckFunction ()
RETURNS int
AS
BEGIN
  RETURN ( SELECT 1 );
END;

Upvotes: 0

Ivan
Ivan

Reputation: 373

In SQL Server it's impossible to create a dynamic foreign key but, you can implement table inheritance, which solves your problem e.g.:

CREATE TABLE BaseScan(
Id INT PRIMARY KEY,
SharedProperties....);

CREATE TABLE OrgScan(
Id INT...,
BaseScanId INT NOT NULL FOREIGN KEY REFERENCES BaseScan(Id));

CREATE TABLE dbo.PerScan(
Id INT...,
BaseScanId INT NOT NULL FOREIGN KEY REFERENCES BaseScan(Id));

This way you'll be able to reference BaseScan.Id in SearchUser and then join the data you need depending on 'scan-type' value.

Upvotes: 1

Related Questions