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.)?
Best Answer
Yes, you can do this with a
COMPUTED
column, It's a workaround really as the column has to bePERSISTED
to be used for the foreign key constraint so it consumes storage space:Tested at SQL-Fiddle.
The table can then be used as if the
FieldName
does not exist. You could even define a view that does not include this column and make your applications use that view: SQL-Fiddle-2The syntax you hoped for, although looking pretty, has not been implemented in any DBMS I know of:
Note however that the workaround with the computed columns would be 100% equivalent to this syntax, if only SQL-Server removed in the future the restriction of using only persisted columns. You could add a Connect item with your request.
For the question whether there is any other DBMS that allows this, no, there isn't. Check also a related question:
Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?
If you really don't want to use this method (because of the additional storage requirements, alternative paths would be to enforce the constraint via procedures or triggers (Note that you would have to write procedures or triggers for both tables involved.)