I'm a junior web designer/developer, and I've had this job for just over a week now. I'm learning sql, going through "Instant SQL Programming" by Joe Celko, and I'm stuck on a couple of queries, that just wont work. I'm running SQL Server 2000, and apparently, there's a difference in syntax for foreign keys? It's something that my boss mentioned yesterday, but i'm not sure how to make the following queries work.

-------------------------------------------
CREATE TABLE Orders
(orderid INTEGER NOT NULL PRIMARY KEY,
empid INTEGER NOT NULL DEFAULT 0,
custid INTEGER NOT NULL,
salesdate DATETIME NOT NULL DEFAULT GETDATE(),
FOREIGN KEY empid REFERENCES Customers(custid)
ON UPDATE CASCADE
ON DELETE SET DEFAULT,
FOREIGN KEY custid REFERENCES Customers(custid)
ON UPDATE CASCADE
ON DELETE CASCADE);

CREATE TABLE OrderItems
(orderid INTEGER NOT NULL,
detail INTEGER NOT NULL,
partid INTEGER NOT NULL,
qty INTEGER NOT NULL,
PRIMARY KEY (orderid, detail),
FOREIGN KEY orderid REFERENCES Orders(orderid)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY partid REFERENCES Inventory(partid)
ON UPDATE CASCADE
ON DELETE CASCADE);
-------------------------------------------


The error messages that appear are;
-------------------------------------------
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'empid'.
Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'orderid'.
-------------------------------------------
Can anyone help?
Many thanks.
James Sargison.

One suggestion, since you are not well experienced in SQL straight coding, I'd suggest using the SQL Server Enterprise Manager, which is a wonderful GUI for managing databases. Adding indexes and constraints is really easy via the Enterprise Manager.

And if you'd like to learn SQL codes, there are buttons within Enterprise functions that will allow you to spit out code that you can analyze.
========================================
Buh Bye!

Cheers,
Me

Have you created an Customers table, yet? Line 7 has a foreign key reference to the Customers table. Without the Customers table the CREATE TABLE Orders statement will fail. And this failure means that you will not have an Orders table that is referenced in the CREATE TABLE OrderItems.

Try this:

CREATE TABLE Customers ( custid INTEGER );

Then retry your 2 CREATE statements.