Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)?

share|improve this question

1 Answer 1

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space:

CREATE TABLE dbo.OtherTable (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Blah VARCHAR(100),
    FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL
) ;

ALTER TABLE dbo.OtherTable WITH CHECK 
  ADD CONSTRAINT [FK__OtherTable__Blah] 
    FOREIGN KEY (FieldName, Blah) 
    REFERENCES Lookup (FieldName, Value) ;

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-2


The syntax you hoped for, although looking pretty, has not been implemented in any DBMS I know of:

ALTER TABLE dbo.OtherTable WITH CHECK 
  ADD CONSTRAINT [FK__OtherTable__Blah] 
    FOREIGN KEY('This', Blah) 
    REFERENCES Lookup (FieldName, Value) ;

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.)

share|improve this answer
    
So this would be more or less equivalent to just creating the column with the default value of 'This', then, except it can't be changed? I appreciate it, but I'm looking for an alternative that lies outside of the dbo.OtherTable table entirely. –  Ehryk Apr 4 at 9:45
    
I think there is no alternative with declarative integrity. If you alter your INSERT/UPDATE/DELETE statements/procedures to enforce this, and restrict all your applications to use those altered procedures it could be done. –  ypercube Apr 4 at 9:46
    
It would be close; what I'm really looking to avoid is that select * from dbo.OtherTable would NOT have any additional <unwanted> columns, that really have nothing to do with OtherTable at all (they just specify which Lookup type should be there, like the job of a Foreign Key). –  Ehryk Apr 4 at 9:56
1  
You could define a view that does not include the Fieldname column. You could even insert, delete, update that view. Noone would know the difference :) –  ypercube Apr 4 at 9:59
    
Lets say, for sake of the question, this isn't good enough. What are my options? Take the source of MySQL, SQLite, Postgres, or another open source database code and extending the foreign key system to handle this? Would there be any way to create it in SQL Server with CLR? –  Ehryk Apr 4 at 19:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.