Cannot create a foreign key reference (probably my fault)
-
I create a new SQL CE database and try to add two tables with a key linkiing them. The first table is created successfully with the statement:
CREATE TABLE Classes(ClassId INTEGER NOT NULL PRIMARY KEY, Class NVARCHAR(64) NOT NULL )
and I add 3 rows to it successfully. I then try to create the second table with a foreign key thus:
CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER FOREIGN KEY REFERENCES Classes(ClassId) )
but this fails with the message:
The constraint specified is not valid
What am I doing wrong?
-
I create a new SQL CE database and try to add two tables with a key linkiing them. The first table is created successfully with the statement:
CREATE TABLE Classes(ClassId INTEGER NOT NULL PRIMARY KEY, Class NVARCHAR(64) NOT NULL )
and I add 3 rows to it successfully. I then try to create the second table with a foreign key thus:
CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER FOREIGN KEY REFERENCES Classes(ClassId) )
but this fails with the message:
The constraint specified is not valid
What am I doing wrong?
Have a read of the first answer in this StackOverflow Question[^] on the same subject. The first answer suggests that you remove the
FOREIGN KEYpart of your create table statementEvery day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
I create a new SQL CE database and try to add two tables with a key linkiing them. The first table is created successfully with the statement:
CREATE TABLE Classes(ClassId INTEGER NOT NULL PRIMARY KEY, Class NVARCHAR(64) NOT NULL )
and I add 3 rows to it successfully. I then try to create the second table with a foreign key thus:
CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER FOREIGN KEY REFERENCES Classes(ClassId) )
but this fails with the message:
The constraint specified is not valid
What am I doing wrong?
-
Have a read of the first answer in this StackOverflow Question[^] on the same subject. The first answer suggests that you remove the
FOREIGN KEYpart of your create table statementEvery day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
Thanks Eddy, your suggestion did work (for SQL CE). I have been staring at this for over an hour and could not see the subtlety of the optional parameters in the documentation[^]. [edit] For the record the correct syntax is:
CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER, FOREIGN KEY (ClassId) REFERENCES Classes(ClassId) )
[edit]
-
Thanks Eddy, your suggestion did work (for SQL CE). I have been staring at this for over an hour and could not see the subtlety of the optional parameters in the documentation[^]. [edit] For the record the correct syntax is:
CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER, FOREIGN KEY (ClassId) REFERENCES Classes(ClassId) )
[edit]
Richard MacCutchan wrote:
your suggestion did work (for SQL CE).
Cool - one of those rare cases where both implemented the same sql-standard. Makes me curious how easy (or hard) it is to switch from one to the other.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]