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

Visual Studio doesn't provide an interface for creating relationships between tables in a SQL Server CE database (I'm using version 3.0) and you can't open a Compact Edition DB using Management Studio as far as I know. Any ideas?

From stackoverflow Ryan Shripat

• 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:

  http://sqlserverce.org/blogs/faq/default.aspx

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

  Edo : I believe you are missing the CONSTRAINT keyword. At least in SQL Server CE 3.5... ALTER TABLE Orders ADD CONSTRAINT FK_Customer_Order FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
  From Espo

• 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)

  From Ryan Shripat

• 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.

  HTH

  Ryan Shripat : I do believe I was using VS 2005 when I had this issue. Nice to know you can do it through the UI in VS 2008 now!
  From $pageUsers[$post.author].name

• Alan is correct when he says there's designer support. Rhywun is incorrect when he implies you cannot choose the foreign key table. What he means is that in the UI the foreign key table drop down is greyed out - all that means is he has not right clicked on the correct table to add the foreign key to.

  In summary, right click on the foriegn key table and then via the 'Table Properties' > 'Add Relations' option you select the related primary key table.

  I've done it numerous times and it works.

  Spidey : Thanks Rob, this did the trick for me.
  From Rob