CREATE table sql - constraint syntax error

[jason]

Could someone help me get the syntax correct for this CREATE SQL QUERY in
Acccess 2000:

I am getting a SYNTAX CONSTRAINT error when I try to run it....

CREATE TABLE ListingPriceChanges
(
ListingID INT FOREIGN KEY REFERENCES Listings(ListingID),
NewPrice DECIMAL(19,2),
ChangeDate SMALLDATETIME

Many thanks
jason

 

[Tom Ellison]

Dear Jason:

I created this script:

CREATE TABLE Listings(ListingID int NOT NULL PRIMARY KEY)
CREATE TABLE ListingPriceChanges
(
ListingID INT FOREIGN KEY REFERENCES Listings(ListingID),
NewPrice DECIMAL(19,2),
ChangeDate SMALLDATETIME
)

All I did above was create the Listings table so I could reference it,
and add a closing paren to your script. This works fine.

Could it be that ListingID is not a uniquely constrained column in
Listings? Other than that, you might just look through my example and
see where you have done something else different. That might pinpoint
your problem. Otherwise, you could post enough SQL here so we have a
way of reproducing your problem independently.

Could someone help me get the syntax correct for this CREATE SQL QUERY in
Acccess 2000:

I am getting a SYNTAX CONSTRAINT error when I try to run it....

CREATE TABLE ListingPriceChanges
(
ListingID INT FOREIGN KEY REFERENCES Listings(ListingID),
NewPrice DECIMAL(19,2),
ChangeDate SMALLDATETIME

Many thanks
jason

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[jason]

Thanks!

Dear Jason:

I created this script:

CREATE TABLE Listings(ListingID int NOT NULL PRIMARY KEY)
CREATE TABLE ListingPriceChanges
(
ListingID INT FOREIGN KEY REFERENCES Listings(ListingID),
NewPrice DECIMAL(19,2),
ChangeDate SMALLDATETIME
)

All I did above was create the Listings table so I could reference it,
and add a closing paren to your script. This works fine.

Could it be that ListingID is not a uniquely constrained column in
Listings? Other than that, you might just look through my example and
see where you have done something else different. That might pinpoint
your problem. Otherwise, you could post enough SQL here so we have a
way of reproducing your problem independently.



Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts