How to prevent an INSERT into a SQL Server table if a value is not in another table?

Question

Let's say I have two SQL Server tables. One contains "transient" data - records are inserted, updated, and deleted as the external data that populates the table changes.

I have another table that uses data in that table, and I need to make sure that one of the column values is found in that table when attempting to insert.

Table #1 - Widgets:

CREATE TABLE [dbo].[Widgets]
(
    [id] [int] NOT NULL,
    [widget_attr_1] [int] NULL,
    [widget_attr_2] [varchar](10) NOT NULL,

    CONSTRAINT [PK_Table_1] 
        PRIMARY KEY CLUSTERED ([id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Table #2 - Transactions:

CREATE TABLE [dbo].[Transactions]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [widget_id] [int] NOT NULL,
    [transaction_data_1] [varchar](50) NOT NULL,

    PRIMARY KEY CLUSTERED ([id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

So in the above example, I need to make sure widget_id is in dbo.Widgets or else throw an error. I can't use a foreign key from Transactions to Widgets, because the Transactions records are permanent, and when a delete of a Widget is attempted, it would fail, because it's referenced by the foreign key.

Can I use a CHECK constraint looks up the value in the Widgets table before inserting? Or maybe a trigger that looks up the value and throws an error if it doesn't exist? I can't figure out how either would work and what the possible performance effects would be. Looking for best practices here.

Thanks in advance!

Answer 1

A FOREIGN KEY is a type of CHECK CONSTRAINT. I would suggest you add an EXISTS clause to your INSERT statement, so that rows you don't want inserted aren't. In Pseudo SQL that would be:

INSERT INTO dbo.Transactions (widget_id,transaction_data_1)
SELECT widget_id,
       transaction_data_1
FROM (VALUES(...))V((widget_id,transaction_data_1)
WHERE EXISTS (SELECT 1
              FROM dbo.widgets w
              WHERE w.widget_id = v.widget_id);

If you need to have an error thrown, you could use a TRIGGER like the below, just note that if even 1 of the values doesn't exist in the table widgets then the entire INSERT will fail:

CREATE TRIGGER dbo.trg_fk_TranasctionWidget
ON dbo.Transactions
AFTER INSERT
AS
IF EXISTS (SELECT 1
           FROM inserted i
               LEFT JOIN dbo.widgets w ON i.widget_id = w.widget_id
           WHERE w.widget_id IS NULL)
    --Change the error number to one suitable for your environment
    THROW 73246, N'The INSERT statement conflict with the trigger constraint trg_fk_TranasctionWidget. The conflict occured on the table "dbo.Transactions", column "widget_id".', 16;
GO

Though, like @MartinSmith has commented it seems like really you should be looking at cascading.