How do you create a foreign key relationship in a SQL Server CE (Compact Edition) Database?

Unfortunately there is currently no designer support (unlike for SQL Server 2005) for building relationships between tables in SQL Server CE. To build relationships you need to use SQL commands such as:

ALTER TABLE Orders
ADD CONSTRAINT FK_Customer_Order
FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)

If you are doing CE development, i would recomend this FAQ:

EDIT: In Visual Studio 2008 this is now possible to do in the GUI by right-clicking on your table.

Visual Studio 2008 does have a designer that allows you to add FK's. Just right-click the table... Table Properties, then go to the "Add Relations" section.

You need to create a query (in Visual Studio, right-click on the DB connection -> New Query) and execute the following SQL:

ALTER TABLE tblAlpha
ADD CONSTRAINT MyConstraint FOREIGN KEY (FK_id) REFERENCES
tblGamma(GammaID)
ON UPDATE CASCADE

To verify that your foreign key was created, execute the following SQL:

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Credit to E Jensen (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=532377&SiteID=1)