SQLServer 2000 syntax problem? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

SQLServer 2000 syntax problem?

Author

Topic

sargimedia
Starting Member

2 Posts

Posted - 2002-10-01 : 06:01:30

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.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2002-10-01 : 06:18:46

u can use the following syntax for creating the foriegn keys:
CREATE TABLE Orders
(orderid INTEGER NOT NULL PRIMARY KEY,
empid INTEGER NOT NULL DEFAULT 0 references customers(custid) ,
custid INTEGER NOT NULL references customers(custid),
salesdate DATETIME NOT NULL DEFAULT GETDATE(),
);

harshal.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-10-01 : 07:30:29

FOREIGN KEY (empid) REFERENCES Customers(custid)

According to the docs, SQL server does not support

ON update set default

or

ON delete set default

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-10-01 : 08:17:41

Congratulations on your new job, but please, first read the book very carefully!!!

And tell your boss, that there are many other books to read...

Osangar
Starting Member

7 Posts

Posted - 2005-03-16 : 06:59:41

Sorry if this has been asked elsewhere, but does SQL 2000 support the “On Delete Set Default” and if so how do you implement it ?

cheers

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 09:05:26

Nope. You could do that with a trigger though

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources