I am working with a 'lookup' style table that is already in place, which has a compound primary key (not my design) in Microsoft SQL Server 2012. I want to add a foreign key to it in another table, but with only a single column, and I am seeking a workaround, no matter how unreasonable (CLR based custom library or dll hijacking, even), just to see how much would need to be done.
Here's an example:
CREATE TABLE dbo.Lookup ( FieldName VARCHAR(100) NOT NULL, Value VARCHAR(100) NOT NULL, PRIMARY KEY (FieldName, Value) ) INSERT INTO Lookup (FieldName, Value) SELECT 'This', 'A' UNION ALL SELECT 'This', 'B' UNION ALL SELECT 'That', 'A' CREATE TABLE dbo.OtherTable ( ID INT PRIMARY KEY IDENTITY(1,1), Blah VARCHAR(100) ) ALTER TABLE dbo.OtherTable WITH CHECK ADD CONSTRAINT [FK__OtherTable__Blah] FOREIGN KEY(Blah) REFERENCES Lookup (Value) --Cannot create this: --"There are no primary or candidate keys in the referenced table 'Lookup' --that match the referencing column list in the foreign key 'FK__OtherTable__Blah'."
This is somewhat expected. I'm acutely aware that the SQL Standard for working with this is that I would need to include another column in
dbo.OtherTable that stores
FieldName for the foreign key to be created.
However, what if I knew ahead of time that
dbo.OtherTable is only ever using
FieldName of 'This'? Why can't I then add a foreign key that uses 'This' for
FieldName for that check?
I know that I could just add another column, set all their values to 'This', and move on. I'm not interesting in going this route. What I'd like to know is how possible it is to wrangle this into submission. Would it even be possible to write my own foreign-key-like integrity check, should it not be possible to do any other way? Or hijack the built-in Foreign Key implementation?
Basically, all I'm looking for is something like:
ALTER TABLE dbo.OtherTable WITH CHECK ADD CONSTRAINT [FK__OtherTable__Blah] FOREIGN KEY('This', Blah) REFERENCES Lookup (FieldName, Value)
Is this feature available in other SQL Implementations (MySQL, Postgres, Oracle, etc.)?