[SOLVED] Microsoft SQL Server - how to only enforce foreign key upon insertion but allow for orphan foreign keys to remain?

Microsoft SQL Server - how to only enforce foreign key upon insertion but allow for orphan foreign keys to remain?

Here are the technologies being used for local development:

Microsoft SQL Server 2016 Web Edition

Let's say that I have the following table called CarCompanyTable

create table CarCompanyTable(

[CarCompanyid] uniqueidentifier DEFAULT newsequentialid() NOT NULL,
    [CarCompanyName] [nvarchar](200) NOT NULL,
   [AddressOfLocation] [nvarchar](200) NOT NULL,
  CONSTRAINT [PK_CarCompanyid] PRIMARY KEY CLUSTERED (CarCompanyid)
);

Let's say that I have another table called CarTable

create table CarTable(

[CarId] uniqueidentifier DEFAULT newsequentialid() NOT NULL,

[CarName] [nvarchar](200) NOT NULL,
[FKCarCompanyid] uniqueidentifier,

CONSTRAINT [PK_CarId] PRIMARY KEY CLUSTERED (CarId),
 CONSTRAINT [FK_CarCompanyTableCarTable] FOREIGN KEY(FKCarCompanyid) REFERENCES [dbo].[CarCompanyTable] (CarCompanyid),
);

How can the foreign constraint be enforced in such a way that

1) checks if CarCompanyid foreign key exists upon a new insert entry into the CarTable

2) however, allows there to be orphan CarCompanyid foreign key in case a CarCompanyTable entry (with corresponding CarTable entries ) is deleted from the CarCompanyTable

Could someone tell me the exact modifications that I would need to make in order to ensure the aforementioned requirements are met?

Solution

There are 2 strategies that most people follow:

Change the CarTable to have a column "IsInactive". This allows you to mark records as "Deleted" without removing them. This allows you to use normal Foreign keys for integrity. Of course, it means you need to program accordingly, to only show non-deleted (IsInactive=False) records.
Don't use a foreign key for referential integrity. Instead, use an "on insert" trigger to lookup (pseudo) FK records and throw an error to enforce referential integrity. I think most DBAs would tell you this is a bad idea and you should stick with #1.

The down-side to approach #2 is, once you delete your referenced record, you cannot tell what it used to be, and you cannot recover it. With approach #1, you just need to write your program to filter-out inactive records, but you can always look them up if you really need to.