How to create this SQL constraint

Question

The situation I was in is that I have a set of existing tables with incremental integers as identifiers. Now I have added a GUID for each table as a new PC.

I have a primary table

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

and child table

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

and you want to create a relationship between them (basically LINQ to SQL from .Net will build realationships) based on ParentTableId. I understand that I can’t create an FK relationship between them, because now they have GUIDS for PC. Should I update the GUIDs in the Children table so that they refer to the parent table, or can I still create the association based on the existing ParentId column?

The relationship between the two is one thing for many (parent to child), and I use SQL Server 2008.

Answer 1

I think you are mistaken in thinking that you can only create FOREIGN KEYone that refers to PRIMARY KEY. In fact, you can create FOREIGN KEYone that refers to a constraint UNIQUE, for example.

ALTER TABLE ParentTable ADD 
   UNIQUE (ParentTableId);

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

Answer 2

I don’t think you will want to update your GUIDs in the child table to refer to the parent card, as this will probably break your PC in the child table. I would do to change the column "ParentTableId" in the child table as a GUID instead of INT. Then update the child table using the relationship between ParentTable.GUID ↔ ChildTable.ParentTableID.

Answer 3

( LINQ to SQL .Net )

The accepted answer is great. However, LinqToSql does not care about what is in your database. You can add an association even if FKey has not been defined.