Generation of FOREIGN KEY via SQL-Statement

[Timo Göttig]

Hello NG,

how can I create a column with foreign keys in a new table with SQL
statements?

According to my prof this is the right way:

CREATE TABLE Student
(
Matr_No int PRIMARY KEY,
Name varchar(20) NOT NULL,
Forename varchar(20),
Exam_No int FOREIGN KEY references Exam(Exam_No)
);

Access displays an error message "Syntax Error in CONSTRAINT clause".
Unfortunately Access Online Help did not help me to solve this problem.

Can you please tell me the correct statements in Access 2002?

Thanks in advance & best regards from Germany,
Timo

 

[Brian Camire]

You might remove the FOREIGN KEY keywords, as in

CREATE TABLE Student
(
Matr_No int PRIMARY KEY,
Name varchar(20) NOT NULL,
Forename varchar(20),
Exam_No int references Exam(Exam_No)
);

 

[Dan Artuso]

Hi,
I must say this was a tough one. Anyway, this is what finaly worked:

CREATE TABLE Student
(
Matr_No INTEGER CONSTRAINT constr1 PRIMARY KEY,
FullName varchar(20) NOT NULL,
Forename varchar(20),
Exam_No INTEGER CONSTRAINT contr2 REFERENCES Exam(Exam_No)
);

Noticed I've changed Name to FullName. Name is a property of most Access objects so naming
a field that way will only give you grief.

 

[Dan Artuso]

Hi,
I forgot to mention, you can name the constraints anything you want. You probably want
more meaningful names than constr1 and constr1

 

[Timo Göttig]

Hi Brian,

You might remove the FOREIGN KEY keywords, as in

CREATE TABLE Student
(
Matr_No int PRIMARY KEY,
Name varchar(20) NOT NULL,
Forename varchar(20),
Exam_No int references Exam(Exam_No)
);

Thanks, this works fine.

Timo

 

[Timo Göttig]

Thanks, Dan!
Brian found a solution that is much easier...

Timo

 

[Dan Artuso]

That's weird, that statement give me a syntax error!!

 

[Brian Camire]

Interestingly, the statement I posted works only under Access 2000 (and
presumably later), but the statement you posted works in both Access 97 and
Access 2000 (and presumably later).

 

[Dan Artuso]

Yeah, I was using 97 and it complained about the PRIMARY KEY, until I put in
CONSTRAINT. Go figure