sql server 2000 syntax problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sql server 2000 syntax problems

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-03 : 09:47:52

James Sargison writes "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 you help?
Many thanks.
James Sargison."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-03 : 10:21:06

This looks familiar - wasn't it answered a few days ago?

empid INTEGER NOT NULL DEFAULT 0
FOREIGN KEY REFERENCES Customers(custid)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources