IS There anything around this!?!? - 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)

IS There anything around this!?!?

Author

Topic

Danny4003
Starting Member

40 Posts

Posted - 2007-01-11 : 15:58:39

Hey SQL GURU's,

When setting up Cascade Update and Delete,
is there anyway around it working with having Identity Inserts..

EXAMPLE::

create table customers
(ID int NOT NULL PRIMARY KEY CLUSTERED Identity(1,1),
Name varchar(25)NULL)

Create table Orders
(OrderID int NOT NULL Identity(1,1)
ID int NULL
FOREIGN KEY REFERENCES customers(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
)

--INSERT DATA INTO THE 2 TABLES
INSERT Customers
VALUES('Bombast & Floom')
INSERT Customers
VALUES('Dewey Cheatham')
INSERT Orders
Values(1)
INSERT Orders
Values(2)
GO

SELECT * FROM Customers
SELECT * FROM Orders
--Deleting a product will also delete
--the matching row from the Orders table
Delete from Customers
where custID = 1

update Customers
Set CustID = 333
where CustID = 2

select * from Customers
Select * from Orders

now i know that this gives an error because the Identity Insert is on in the customers table. If you take the Identity OUT of the customers table then it will work.

Again my question is::
Is there anyway around that??

Thanks in advanced,

Danny D

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-11 : 16:04:25

I'm not sure what you're asking - you should never be changing the ID (key value) of anything - you can with SET IDENTITY_INSERT but that is primarily for bulk operations, in normal day to day transactions don't update key values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources