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

Question

Visual Studio 2005 does not provide an interface for creating relationships between tables in a SQL Server CE database (I am using version 3.0), and you cannot open a Compact Edition database using Management Studio, as far as I know. Any ideas?

Answer 1

Unfortunately, there is currently no designer support (unlike SQL Server 2005) for building table relationships in SQL Server CE. To build relationships, you need to use SQL commands, for example:

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

If you are developing CE, I would recommend this FAQ:

EDIT . In Visual Studio 2008, this can now be done in the graphical interface by right-clicking on your table.

Answer 2

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

NTN

Answer 3

You need to create a query (in Visual Studio, right-click on the DB → New Query connection) 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 has been created, run the following SQL:

 SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 

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

Answer 4

Alan is right when he talks about designer support. Rhywun is incorrect when it implies that you cannot select a foreign key table. What he means is that in the user interface the foreign key table is grayed out - all this means that he did not right-click on the correct table to add the foreign key.

In short, right-click on the foriegn key table, and then use the "Table Properties"> "Add Relations" option to select the corresponding primary key table.

I have done this many times and it works.

Answer 5

Walkthrough: Creating a SQL Server Compact 3.5 Database

To create a relationship between tables created in the previous procedure

• In Server Explorer / Database Explorer, expand the table.
• Right-click the Orders table and select Table Properties.
• Click Add Relations.
• Enter FK_Orders_Customers in the Relationship Name field.
• Select CustomerID from the column list of the foreign key table.
• Click Add Columns.
• Click Add Link.
• Click “OK” to complete the process and create the link to the database.
• Click OK again to close the Table Properties dialog box.

Answer 6

 create table employee ( empid int, empname varchar(40), designation varchar(30), hiredate datetime, Bsalary int, depno constraint emp_m foreign key references department(depno) ) 

We must have a primary key to create a foreign key or a relationship between two or more tables.

Answer 7

I know this for a "very long time" since this question was asked first. Just in case this helps someone,

Adding relationships is well supported by MS through the SQL Server Compact Tool Box ( https://sqlcetoolbox.codeplex.com/ ). Just install it, then you will be able to connect to the Compact Database using the Server Explorer window. Right-click on the main table, select "Table Properties". You should have the following window containing the "Add Relationships" tab allowing you to add relationships.