I have the below table structure, where a delivery has 1 or many lines and tracking information (optional, i.e. nullable) can relate to the overall delivery OR a line.

I want to setup relationships/triggers so that deleting a delivery (master) removes all lines and tracking for me.

If the Id column in each case is the primary key, you can easily setup a foreign key between a delivery and it’s lines as:

ALTER TABLE DeliveryLines WITH CHECK ADD CONSTRAINT FK_DeliveryLines_Deliveries FOREIGN KEY (DeliveryId) REFERENCES Deliveries (Id) ON DELETE CASCADE

However, I cannot setup a link between the Deliveries and DeliveryLines and their DeliveryTracking in the same way due to the tracking Id being nullable:

ALTER TABLE DeliveryTracking WITH CHECK ADD CONSTRAINT FK_DeliveryTracking_Deliveries FOREIGN KEY (Id) REFERENCES Deliveries (TrackingId) ON DELETE CASCADE

There are no primary or candidate keys in the referenced table 'Deliveries' that match the referencing column list in the foreign key 'FK_DeliveryTracking_Deliveries'.

Setting up a unique (exclude nulls) index on the TrackingId also does not allow this to work.

What is the correct way to achieve this using MSSQL?

-- This is the master table
CREATE TABLE [dbo].[Deliveries](
    [Id] [varchar](6) NOT NULL,
    [TrackingId] [varchar](10) NULL,
    ...
)

-- A delivery has multiple lines
CREATE TABLE [dbo].[DeliveryLines](
    [Id] [varchar](14) NOT NULL,
    [DeliveryId] [varchar](6) NOT NULL,
    [TrackingId] [varchar](10) NULL,
    ...
)

-- Tracking can relate to the lines or the overall delivery
CREATE TABLE [dbo].[DeliveryTracking](
    [Id] [varchar](10) NOT NULL,
    [Reference] [varchar](50) NULL,
    ....
)