updation of primary key coulmn value

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

Transact-SQL (2005)

updation of primary key coulmn value

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-16 : 02:42:54

Hi,
I Have a table which has a primary key column, which is been referenced in two other child tables.. When i try to update a value in parent table or child table it throws a foreign key violation error..
Even though i change the value in the primary key table column, it still throws the error.

When i update the Primary key value, all the child tables possesing the value whould also get updated.
I tried this Query on the parent table..

alter table t1 alter column ID on update cascade

But still the above query shows the following error

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'on'.

Is this the right way to do.. Or is there any other way to do this..

I Reall dont have any idea on this concept.. Can anyone help me on this.

Thanks Before hand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 02:46:31

nope. cascade options can only be specified with foreign key definition

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-16 : 03:01:29

Thanks vishakh.. So i need to write the cascade statement for all the dependent tables irrespective of whether i'm using the data in those tables.. is It..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 03:06:05

quote:
Originally posted by swathigardas

Thanks vishakh.. So i need to write the cascade statement for all the dependent tables irrespective of whether i'm using the data in those tables.. is It..

yup. provided those columns are linked to main column by means of foreign key.

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-16 : 03:11:20

oh.. ok vishakh,,. thanks for the Valuable Info

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 03:12:39

No probs

you're welcome

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-16 : 07:42:04

Hi.. I have one more Doubt on this.. I have Dropped the previosuly created constarint and created a new constarint with cascade option.
Is there any way to update the existing foreign key constraint with cascade option through query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 07:48:52

Nope. you should drop it and create again with cascade option

swathigardas
Posting Yak Master

149 Posts

Posted - 2008-12-16 : 09:27:55

oh. ok. Thanks.. Again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 09:57:21

welcome again