alter column syntax for enabling cascades

Hi,
I have a question about sql syntax on how to enable both
ON Delete cascade and On Update Cascade for already existing data tables.
One table contains all the users:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
The next table contains user protigraphs
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
My QUESTION is how to add both on delete cascade constraint and on update
cascade constraints to
the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
exists for this table.
Thank you
Vadim
"Vadim" <vadim@.dontsend.com> wrote in message
news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a question about sql syntax on how to enable both
> ON Delete cascade and On Update Cascade for already existing data tables.
> One table contains all the users:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
> The next table contains user protigraphs
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> My QUESTION is how to add both on delete cascade constraint and on update
> cascade constraints to
> the EXISTING USER_PICTURES(USER_NAME) FIELD, because primary key already
> exists for this table.
>
You just need to drop the existing constraint and create a new one. The
only trick is finding the name of the constraint, since it is
system-generated.
sp_help user_pictures
--discover the system-generated name of the constraint
alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
And don't use cascade updates, since you shouldn't be updating primary keys
in the first place.
David
|||David,
Thank you for your reply.
I was hoping to be able to write a GENERAL script that would enable cascade
updates and deletes, sp_help might not be an option since the script will
have to be run at multiple customers' locations.
What could be other options? maybe to write a stored procedure that moves
the data to a temp table from the user_pictures table and properly creates
the new table and moves the data back.
I was wondering if it was possible to specify both options on delete and on
update since if user_name changes in the users table the same change would
then propagate to the user_pictures table.
Thanks again
Vadim
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RbR%23S1DHHA.4620@.TK2MSFTNGP04.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:O5YKYB1DHHA.572@.TK2MSFTNGP03.phx.gbl...
> You just need to drop the existing constraint and create a new one. The
> only trick is finding the name of the constraint, since it is
> system-generated.
> sp_help user_pictures
> --discover the system-generated name of the constraint
> alter table USER_PICTURES drop constraint FK__USER_PICT__USER___014935CB
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> And don't use cascade updates, since you shouldn't be updating primary
> keys in the first place.
> David
|||"Vadim" <vadim@.dontsend.com> wrote in message
news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
> David,
> Thank you for your reply.
> I was hoping to be able to write a GENERAL script that would enable
> cascade updates and deletes, sp_help might not be an option since the
> script will have to be run at multiple customers' locations.
> What could be other options? maybe to write a stored procedure that moves
> the data to a temp table from the user_pictures table and properly creates
> the new table and moves the data back.
> I was wondering if it was possible to specify both options on delete and
> on update since if user_name changes in the users table the same change
> would then propagate to the user_pictures table.
>
Sure it's possible, it's just not usually a good idea to modify primary
keys. However if you are doing it, then you should probably cascade the
change.
To write a general script you will need to use a bit of dynamic SQL. Like
this:
CREATE TABLE DBO.USERS(
USER_NAME VARCHAR(20) PRIMARY KEY,
FIRST_NAME VARCHAR(40),
LAST_NAME VARCHAR(40)
)
CREATE TABLE DBO.USER_PICTURES(
USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
PICTURE IMAGE NOT NULL
)
go
declare @.constraint varchar(30)
declare @.sql varchar(2000)
set @.constraint =
(
select object_name(constid) from sysforeignkeys
where rkeyid = object_id('dbo.users')
and fkeyid = object_id('dbo.user_pictures')
)
set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint +
']'
exec (@.sql)
go
alter table user_pictures
add constraint fk_user_pictures_users
foreign key (user_name) references users(user_name)
on delete cascade
on update cascade
David
|||David,
Thank you very much for such a detailed reply, This is what I was looking
for.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:um%23s%23s1DHHA.1748@.TK2MSFTNGP02.phx.gbl...
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:e3tgBe1DHHA.4144@.TK2MSFTNGP06.phx.gbl...
> Sure it's possible, it's just not usually a good idea to modify primary
> keys. However if you are doing it, then you should probably cascade the
> change.
>
> To write a general script you will need to use a bit of dynamic SQL. Like
> this:
> CREATE TABLE DBO.USERS(
> USER_NAME VARCHAR(20) PRIMARY KEY,
> FIRST_NAME VARCHAR(40),
> LAST_NAME VARCHAR(40)
> )
>
> CREATE TABLE DBO.USER_PICTURES(
> USER_NAME VARCHAR(20) PRIMARY KEY REFERENCES USERS(USER_NAME),
> PICTURE IMAGE NOT NULL
> )
> go
> declare @.constraint varchar(30)
> declare @.sql varchar(2000)
> set @.constraint =
> (
> select object_name(constid) from sysforeignkeys
> where rkeyid = object_id('dbo.users')
> and fkeyid = object_id('dbo.user_pictures')
> )
> set @.sql = 'alter table dbo.user_pictures drop constraint [' + @.constraint
> + ']'
> exec (@.sql)
> go
> alter table user_pictures
> add constraint fk_user_pictures_users
> foreign key (user_name) references users(user_name)
> on delete cascade
> on update cascade
> David