Is there some particular reason you're worried about how much work your computer has to do to join three tables?
The purpose of normalizing your schema is to make it much harder to corrupt your data when you insert, update and delete it. Normalizing your schema means that reading your data will be more work (generally), but your code for maintaining your data will be much simpler and therefore much less prone to data corruption errors, as well as being cheaper to build and maintain, of course.
If your data is pretty static, i.e. you write it once and then read it a lot, then the benefits of normalization are less important.
If your performance or resource utilization is very critical, and you can't get the performance you need from normalized data, then you can consider denormalizing your schema. However, whenever you use denormalization you should do it with full knowledge of all of the potential traps you've set for yourself with respect to data corruption.
Normalize by default and denormalize as necessary, and with caution.
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.)
Best Answer
No.
information_schema
contains views. You can't have a foreign key to a view.You can't set a foreign key to point at the underlying
pg_catalog.pg_attribute
table that defines columns, either, because foreign keys to system catalogs are not supported.