Using DDL to create a Foreign Key, with Delphi

Question

I am using Delphi 7 to create an Access DB with some DDL statements. It's going to be a relatively simple relational database for a simple stock invoice system. I've managed to create a table called CUSTOMER no problem, but on trying to create a table called ORDER, where in ORDER i have a foreign key field CustomerID, i get the following error message:

"Syntax error in field definition".

CustomerID is the Key field in the CUSTOMER table and I simply want to link the two together. Here is my DDL statements for both.

cs:= 'CREATE TABLE tblCUSTOMER ('+
      'CustomerID Number,' +
      'FName Text(20),' +
      'SName Text(20),' +
      'AddressLine1 Text(35))';
ADOCommand1.CommandText:=cs;
ADOCommand1.Execute;

cs:='CREATE INDEX idxCustomerID ON tblCUSTOMER (CustomerID) WITH PRIMARY';
ADOCommand1.CommandText:=cs;
ADOCommand1.Execute;

cs:= 'CREATE TABLE tblORDER ('+
'OrderID Number,'+
//Here is the line!!
'CustomerID Number CONSTRAINT CustomerID REFERENCES tblCUSTOMER (CustomerID),'+
'OrderDate DateTime,'+
'CREATE INDEX idxPrimary ON tblORDER (OrderID) WITH PRIMARY)';
ADOCommand1.CommandText:=cs;
ADOCommand1.Execute;

I'm guessing the issue is something with the foreign key declaration above. What have I done wrong please?

I have also tried this:

cs:= 'CREATE TABLE tblORDER ('+
'OrderID Number CONSTRAINT PK_OrderID PRIMARY KEY,'+
'CustomerID Number CONSTRAINT FK_CustomerID REFERENCES tblCUSTOMER (CustomerID),'+
'OrderDate DateTime,'+
'CREATE INDEX idxPrimary ON tblORDER (OrderID) WITH PRIMARY';

Still not working.

Answer 1

I found the solution. The CONSTRAINT clause should have went as follows:

cs:= 'CREATE TABLE tblORDER ('+
'OrderID Number,'+
'CustomerID Number CONSTRAINT FK_CustomerID References tblCUSTOMER (CustomerID),' +
'OrderDate DateTime)';
ADOCommand1.CommandText:=cs;
ADOCommand1.Execute;

And the CREATE INDEX Clause should have been a separate DDL command.

cs:='CREATE INDEX idxOrderID ON tblORDER (OrderID) WITH PRIMARY';
ADOCommand1.CommandText:=cs;
ADOCommand1.Execute;

Thanks anyway