2

I keep getting errors using Sql Compact. I have seen that it is possible to create table constraints in compact edition as shown here. And according to the documents found here it provides "Full referential integrity with cascading deletes and updates". So am I really not allowed to do the following or am I making a mistake? I keep getting complaints from sql server compact edition that the constaint is not valid, though it works fine on express edition.

CREATE TABLE [A] (AKey int NOT NULL PRIMARY KEY);
CREATE TABLE [B] (AKey int NOT NULL FOREIGN KEY REFERENCES A(AKey));

Community
  • 1
  • 1
  • Looks like a year and a half after posting this a munch better answer has come along by psur. Haven't tested it, but if that works, terrific. –  Oct 09 '12 at 17:32

2 Answers2

3

On Compact Edition you shouldn't write FOREIGN KEY keyword, you should omit it and start defining foreign key constraint from REFERENCES keyword: http://msdn.microsoft.com/en-us/library/ms173393(v=sql.100).aspx

So in your case you should write:

CREATE TABLE [A] (AKey int NOT NULL PRIMARY KEY);
CREATE TABLE [B] (AKey int NOT NULL REFERENCES A(AKey));

And no ALTER is needed!

psur
  • 4,400
  • 26
  • 36
  • i spent a painful two hours trying to use Foreign Key References A(AKey). Any idea why they made it that way? – yohannist Oct 07 '12 at 15:38
2

Since it works on express edition I will take it that I am not making a mistake. This gives the desired effect, with cascading thrown in, on compact edition.

CREATE TABLE [A] (AKey int NOT NULL PRIMARY KEY);  
CREATE TABLE [B] (AKey int NOT NULL);  
ALTER TABLE [B] ADD CONSTRAINT references_a FOREIGN KEY (AKey) REFERENCES A(AKey)
    ON DELETE CASCADE ON UPDATE CASCADE;
psur
  • 4,400
  • 26
  • 36