Altering cascade setings on an existing table

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 2008 Forums

Other SQL Server 2008 Topics

Altering cascade setings on an existing table

Author

Topic

sangoku
Starting Member

19 Posts

Posted - 2009-03-09 : 17:37:57

Hy i am almost a noob when it comes to SQL, i use 2008 so i figured i will poust it here,

i use the book sql 2005 from beginning and i got half away trough when i figured i wanted to edit an exsisting table which had cascade delete on...

cans me tell me the syntax which aloe's me to remove the table seting i just coldent figure it on my own, i know im dumb but,,, hey im student

I know how to edit row preferences but not how to edit the other stuff yet...

while earth still spining
live on, have fun
else
move to mars

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-10 : 01:57:45

Cascade Deletes are handled by FK Constraints. The table with FK delete constraint using ..

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName
GO

Then recreate the FK Constraint with ..

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName FOREIGN KEY (FieldNameInChildTable)
REFERENCES ParentTable (FieldNameInParentTable)

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

sangoku
Starting Member

19 Posts

Posted - 2009-03-13 : 09:29:27

ONE small question which caries a bigg NOOB sign on it ,,,,, how is the name of the constraint i only defined on delete cascade,,,, I am not aware of the name XD

U created a table :
CREATE TABLE OrderDetails
(
OrderID int NOT NULL,
PartNO varchar(10) NOT NULL,
Description varchar (25) NOT NULL,
UnitPrice money NOT NULL,
Qty int NOT NULL
CONSTRAINT FKOrderDetails Primary key (OrderID, PartNO),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
ON UPDATE NO ACTION
ON DELETE CASCADE
)

so how do i delete the "ON DELETE CASCADE " part? how is his name oi realy dont get it ~.~

while earth still spining
live on, have fun
else
move to mars

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 11:54:07

This is why I don't like defining constraints like that ;-). What SQL Server does is is it generates a constraint name for you. You can give it a constraint name like you did for your primary key.

Primary Constraint Name: FKOrderDetails
Foreign Key Constraint Name: FK__OrderDetailed__OrderID__SomeRandomHexNumber

So to find the Constraint name with following query:

SELECT OBJECT_NAME(parent_object_id),* FROM sys.foreign_keys WHERE parent_object_id = (Select Object_id From sys.tables Where Name like 'Applications')

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

sangoku
Starting Member

19 Posts

Posted - 2009-03-13 : 15:22:05

AAAAAAAAAAAAAAAAAAAAAAAAHHHHHHHHHHHHHhhhh

"BOW BEFORE YOU" thy sooooooo much onw i can finly go to sleep, i figured that some kind of name is provisoric,,,

now i see it.... O.o‚ whata stupid name it has a uniq identifier on it a no idea how long hexadecimall bumber,,,, shesh,,, i will from now on make constrains by my self....

while earth still spining
live on, have fun
else
move to mars

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 16:20:06

Aye .. ;-) The worst I seen is a developer did all his constraints manually but he named them...

Constraint_1, Constraint_2, Constraint_3 ....

So when I had to drop/recreate Constraints I was like hmmm which one do I create and drop gawd it was a nightmare. At least SQL puts the table name/field name in hehe.

Thanks Have fun :).

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

sangoku
Starting Member

19 Posts

Posted - 2009-03-13 : 16:27:14

LOL, (Laugh on floor), that was a good one, really? No il go strictly by rules like SQL il just skip the the 123144523552a243b4324f part,,, you mean he rally did that, lol

while earth still spining
live on, have fun
else
move to mars

sangoku
Starting Member

19 Posts

Posted - 2009-03-13 : 18:42:50

~.~, gues im a bit to much noob when i execute your query i get an empty result,,,

i try
SELECT *
FROM sys.foreign_keys
and i get
FK_EmployeeCreatesOrder
FK_EmployeeHasManager
FK__OrderDeta__Order__078C1F06
FK__Orders__Customer__7D0E9093

and i made the first 2, and i find that FK__OrderDeta__Order__078C1F06 has a cascade tuned on,, and try to drop it but it wont let me >.<`

i use the following query :
ALTER TABLE shippers
DROP CONSTRAINT FK__OrderDeta__Order
GO
the one you sugested but nothing ~.<,

eny sugestions on my noobsih queries?

EDIT:

ok tried with thouse queries as well after i found out hte name of the constrain, just forgot to mention it earlier:

ALTER TABLE shippers
DROP CONSTRAINT FK__OrderDeta__Order
GO

and it says it is not a constrain

Msg 3728, Level 16, State 1, Line 2
'FK__OrderDeta__Order' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-13 : 19:04:34

By the name it seems your table name is wrong .. Those constraints are for table OrderDeta and Orders.

And you would have to do ...

ALTER TABLE OrderDeta
DROP CONSTRAINT FK__OrderDeta__Order__078C1F06
GO

ALTER TABLE Orders
DROP CONSTRAINT FK__Orders__Customer__7D0E9093
GO

----

SELECT OBJECT_NAME(parent_object_id),* FROM sys.foreign_keys WHERE parent_object_id = (Select Object_id From sys.tables Where Name like 'Applications')

In this query replace the "Application" with your table name so in this case 'Shippers'. And if you don't see any FK constraints it means this table doesn't have FK constraints in it; but it can have other constraints like CHECK, DEFAULT, etc.

To check for Check constraints run:

SELECT OBJECT_NAME(parent_object_id),* FROM sys.check_constraints WHERE parent_object_id = (Select Object_id From sys.tables Where Name like 'Applications')

To check for default constraints run:

SELECT OBJECT_NAME(parent_object_id),* FROM sys.default_constraints WHERE parent_object_id = (Select Object_id From sys.tables Where Name like 'Applications')

To check for primary key constraints run:

SELECT OBJECT_NAME(parent_object_id),* FROM sys.key_constraints WHERE parent_object_id = (Select Object_id From sys.tables Where Name like 'Applications')

You can also use:

sp_help 'table'
GO

to get details on the table if you are unsure; it will list out following information:
table creation
table structure
identity information
storage location
indexes
and all the constraints with their names.

Thanks.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources