nenad-zivkovic (4/14/2014)
The logic behind the question suggest that the `backuplist` is subset of `databases`, and that FK should be created on `backuplist` to reference `databases`. Check query seems to support that, because it checks whatever all rows from `backuplist` have a match in `databases`
select * from backuplist d left join databases b on d.name = b.name where b.name is nullFollowing query tries to create FK in opposite direction, on object `databases` which previous check query has nothing to do with.
ALTER TABLE [dbo].[databases] WITH CHECK ADD CONSTRAINT [FK_backlist_backuplist] FOREIGN KEY([name]) REFERENCES [dbo].[backuplist] ([name])
I agree with this, but my answer is that the check query is incorrect. If you are wanting to create a FK on databases (even without knowing if it is a table or a view), you would go and check if all records in databases have a match in backuplist, not the opposite.
So based on your requirements, the FK is incorrect and should be on the backuplist table. Based on you FK on databases, your check query should do a left join on databases.
Nowhere there was a mention of the creation of a synonym called databased. This is an assumption. So you could also assume that there was a table created called databases...