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.)
Taken from the MSDN article on FOREIGN KEY Constraints
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY
KEY constraint in another table; it can also be defined to reference
the columns of a UNIQUE constraint in another table.
I think it is perfectly fine to define a UNIQUE CLUSTERED INDEX on an IDENTITY column and define the PRIMARY KEY on different columns, often with a NONCLUSTERED INDEX but you don't have to specify an index.
Best Answer
The referencing column may not be unique, as you point out. If the referenced column is also not unique, you have created a many-to-many relationship, which is a right pain to implement and can cause problems maintaining data integrity.
For example, I have two rows in table 1 with the same value in the referenced column. I also have two rows in table 2 with the same value in the referencing column. I then change one of the rows in table 1; which rows in table 2 should also be updated?
If the referenced column is unique, but is not the primary key, then your table may be in an "abnormal form" and the additional unique column may be redundant. The test for this is to ask yourself two questions:
In practice, there are sometimes very good reasons for denormalizing tables, but these must be considered carefully on a case-by-case basis.
Note that it is possible to reference a unique non-primary-key column or column group. However, since conceptually the primary key is "The" canonical identifier for the row, it makes more sense to reference the primary key.