I have the following table, which has a composite primary key:
CREATE TABLE dbo.Seqs( SequenceKey nvarchar(10) NOT NULL, UsageSequence smallint NOT NULL, col1 bit NOT NULL, col2 nvarchar(30) NULL, CONSTRAINT PK_Seqs PRIMARY KEY CLUSTERED ( SequenceKey ASC, UsageSequence ASC ) )
I'm looking to create the following table, with a 'SequenceKey' column that must only accept values that are present in the 'Seqs' table:
CREATE TABLE dbo.Sometbl ( SomeID int IDENTITY(1,1) NOT NULL, Col1 int NOT NULL, Col2 nvarchar(100) NOT NULL, Col3 nvarchar(30) NULL, SequenceKey nvarchar(10) NOT NULL, CONSTRAINT PK_Sometbl PRIMARY KEY CLUSTERED ( SomeID ASC ) )
Given that the 'SequenceKey' in the 'Seqs' table is not unique (the primary key uniqueness is reliant on the 'UsageSequence' column), what is the best way to ensure data cannot be added or updated in 'Sometbl' with a 'SequenceKey' value that doesn't exist in the 'Seqs' table?