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...
>> 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|||"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...
>> 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|||... and remember to name your constraints in the future... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vadim" <vadim@.dontsend.com> wrote in message news:%23ir%234z1DHHA.3836@.TK2MSFTNGP02.phx.gbl...
> 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...
>> 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
>
|
No comments:
Post a Comment