locked
primary key/foreign key relationship RRS feed

  • Question

  • I'm using MS SQL Server Studio Express 9.0.  When selecting SQL Server Compact Edition, I'm able to create tables and primary keys with no problem.  But how do I create a relationship with another table (foreign key)?

    Bob K.

    Friday, May 11, 2007 2:47 PM

Answers

  • Sorry, the correct syntax is (using the sample Northwind.sdf):

    Code Snippet

    ALTER TABLE Orders ADD CONSTRAINT Reference FOREIGN KEY ([Customer ID]) REFERENCES Customers ([Customer ID])

     

    Sunday, May 13, 2007 8:55 AM

All replies

  • There is not designer support for doing this with SQL CE, so you must use:

     

    Code Snippet

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

     

    Designer support for this is promised for the next version of SQL CE (3.5) - in the "Orcas" timeframe.

     

     

    Friday, May 11, 2007 4:00 PM
  • thanks Eric.
    Friday, May 11, 2007 4:06 PM
  • Eric,

    is there a way to enter the above sql statement from with Studio Express?
    Friday, May 11, 2007 6:06 PM
  • Connect to the SDF file, and select File, New, New query, type the text in the query editor and press F5 to execute...
    Friday, May 11, 2007 6:17 PM
  • once again, thank you.
    Friday, May 11, 2007 6:21 PM
  • uhhh... and the beat goes on...

    i've entered the following sql statement:

    ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)

    and received the following error:
    Major Error 0x80040E14, Minor Error 25501
    > ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)
    There was an error parsing the query. [ Token line number = 1,Token line offset = 41,Token in error = FOREIGN ]

    i've attached a link that shows the tables and columns i'm attempting to update.  i just can't see what i'm doing wrong.  could someone help?

    bob k.


    Saturday, May 12, 2007 2:08 PM
  • the link:

    http://www.rkamarowski.com/errors/error.jpg


    Saturday, May 12, 2007 2:11 PM
  • Sorry, the correct syntax is (using the sample Northwind.sdf):

    Code Snippet

    ALTER TABLE Orders ADD CONSTRAINT Reference FOREIGN KEY ([Customer ID]) REFERENCES Customers ([Customer ID])

     

    Sunday, May 13, 2007 8:55 AM
  • perfect!  thanks eric.
    Sunday, May 13, 2007 12:54 PM
  • Good Afternoon Erik,

     

    Can you help me out with a project of my?! I programming for the first time in the .net cf and i create my database all manualy thought SqlCeEngine..

     

    But i want to know if there is a better approach to do this by the "next, next theory" (visuali) . if i create the database using a GUI of sql serv mobile edition, how can i choose where this database will be created and how can i indicate to the datasource in the connection string of an sqlConnection where is my database?! can you help me? i have a checkpoint day 17 to present my work and i would like to finish the hole project will that date to get some time to another project that is coming up, so its kind of urgent subject.

     

    Thank you anyway!

     

    Sunday, May 13, 2007 5:32 PM