SQL - Dervied Foreign Key - Possible?

Question

I'm just curious if this is possible, specifically in SQL CE (Express) with support in .NET's Entity Framework:

Table1 (primary)
-nvarchar(2000) url
-...

Table2 (with foreign key)
-nvarchar(2000) domain
-...

foreign key on Table2.domain references Table1.url such that Table.url contains Table2.domain

e.g.

Table1:
http://www.google.com/blah/blah
http://www.cnn.com/blah/
http://www.google.com/foo

Table2:
google.com
cnn.com

Is it possible for this to be scripted and enforced by SQL CE (let alone any relation database) and, if so, can .NET's Entity Framework automatically support this if I import my database into a model?

Answer 1

No, the whole foreign key value must match the whole primary key value in the referenced table.

I'm trying to think of something helpful to suggest.

• You could validate the strings in table2 using a trigger or a check constraint, but this involves writing meticulous expressions that are easy to get wrong.

• You could store the domain redundantly in table1, put a UNIQUE constraint on it, and declare your foreign key in table2 to point to the table1.domain. Foreign key constraints can reference columns in a unique constraint as well as columns in a primary key constraint.