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

Question

Visual Studio 2005 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

User · Answer

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

User · Answer

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

User · Answer

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 should have an primary key to create foreign key or relationship between two or more table

User · Answer

Walkthrough  Creating a SQL Server Compact 3 5 Database  To create a relationship between the tables created in the previous procedure   In Server Explorer Database Explorer  expand Tables  Right-click the Orders table and then click Table Properties  Click Add Relations  Type FK Orders Customers in the Relation Name box  Select CustomerID in the Foreign Key Table Column list  Click Add Columns  Click Add Relation  Click OK to complete the process and create the relationship in the database  Click OK again to close the Table Properties dialog box

User · Answer

I know it s a  very long time  since this question was first asked  Just in case  if it helps someone   Adding relationships is well supported by MS via SQL Server Compact Tool Box  https   sqlcetoolbox codeplex com    Just install it  then you would get the option to connect to the Compact Database using the Server Explorer Window  Right click on the primary table   select  Table Properties   You should have the following window  which contains  Add Relations  tab allowing you to add relations

User · Answer

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 amp SiteID 1

User · Answer

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

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

Examples related to database

Examples related to visual-studio

Examples related to sql-server-ce

Examples related to visual-studio-2005