How to create this SQL constraint.

Question

The situation I am in is that I have a set of existing tables with incrementing Integers as Ids. I have now added a GUID to each table to be the new PK.

I have a primary table

dbo.ParentTable(GUID - PK, ParentTableId INT, Name VARHCHAR) 

and a child table

dbo.ChildTable(GUID - PK, ParentTableId INT, other fields.....) 

and want to create a relationship between the two (mainly so LINQ to SQL from .Net will build the realationships) based on ParentTableId. I realise I can't create a FK relationship between the two as they have GUIDS for PK now. Should I be updating the GUIDs in the Child table so that they link back to their parent table, or can I create a relationship still based on the pre-existing ParentId column?

The relationship between the two is One to many (parent to child) and I am using SQL Server 2008.

Answer 1

I don't think you would want to update your GUIDs in the child table to link to the parent tabel, as this would probably break your PK in the child table. What I would do would be to alter you "ParentTableId" column in the child table to be a GUID instead of INT. Then update the child table with the relationship between ParentTable.GUID <-> ChildTable.ParentTableID.

Answer 2

I think you may be mistakenly thinking that you can only create a FOREIGN KEY that references a PRIMARY KEY. In fact, you can create a FOREIGN KEY that references a UNIQUE constraint e.g.

ALTER TABLE ParentTable ADD 
   UNIQUE (ParentTableId);

ALTER TABLE ChildTable ADD 
   FOREIGN KEY (ParentTableId)
   REFERENCES ParentTable (ParentTableId);

Answer 3

want to create a relationship between the two (mainly so LINQ to SQL from .Net will build the realationships)

The accepted answer is great. However, LinqToSql doesn't really care what's in your database. You may add an association even when a FKey has not been defined.