What is the proper syntax for moving a foreign key from one table to another.
My foreign key used to be a desciption. I would like it to be an integer
entity.
update Cat2 set Cat2.Cat1ID = (
select cat1.cat1id from Cat1 inner join Cat2 on Cat1.Description =
Cat2.c2c1FKey)
--
Arne Garvander
(I program VB.Net for fun and C# to get paid.)

Is it the referencing or referenced table you want to change? Anyhow, removing a foreign key you use
ALTER TABLE ... DROP CONTRAINT to do. And to add a foreign key you do ALTER TABLE ... ADD
CONSTRAINT...

Your choice of words confuses me however, so you might want to describe it a bit more...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in message
news:BE298AA9-1307-40B7-9AFE-D44FD8966AF8@microsoft.com...
> What is the proper syntax for moving a foreign key from one table to another.
> My foreign key used to be a desciption. I would like it to be an integer
> entity.
> update Cat2 set Cat2.Cat1ID = (
> select cat1.cat1id from Cat1 inner join Cat2 on Cat1.Description =
> Cat2.c2c1FKey)
> --
> Arne Garvander
> (I program VB.Net for fun and C# to get paid.)

I know how to create a foreign key. Now I want to populate my new foreign key.
I can make a join on the old foreign key. I wnat to use the old join to
poulate my new foreign key.
--
Arne Garvander
(I program VB.Net for fun and C# to get paid.)


"Tibor Karaszi" wrote:

> Is it the referencing or referenced table you want to change? Anyhow, removing a foreign key you use
> ALTER TABLE ... DROP CONTRAINT to do. And to add a foreign key you do ALTER TABLE ... ADD
> CONSTRAINT...
>
> Your choice of words confuses me however, so you might want to describe it a bit more...
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in message
> news:BE298AA9-1307-40B7-9AFE-D44FD8966AF8@microsoft.com...
> > What is the proper syntax for moving a foreign key from one table to another.
> > My foreign key used to be a desciption. I would like it to be an integer
> > entity.
> > update Cat2 set Cat2.Cat1ID = (
> > select cat1.cat1id from Cat1 inner join Cat2 on Cat1.Description =
> > Cat2.c2c1FKey)
> > --
> > Arne Garvander
> > (I program VB.Net for fun and C# to get paid.)
>
>

Use an update based on a join. Or of you are on 2008, use MERGE. Examples:

USE tempdb
DROP TABLE p
DROP TABLE c
GO

CREATE TABLE p(oldkey int primary key, newkey char(1) unique)
insert into p (oldkey, newkey) values(1, 'a'), (2, 'b')
GO

CREATE TABLE c(oldkey int references p(oldkey), newkey char(1) NULL)
insert into c(oldkey) VALUES(1), (2)
GO

SELECT * FROM p
SELECT * FROM c
GO

UPDATE c
SET c.newkey = p.newkey
FROM p INNER JOIN c ON c.oldkey = p.oldkey

SELECT * FROM p
SELECT * FROM c

--2008 alternative:
MERGE c
USING p ON c.oldkey = p.oldkey
WHEN MATCHED THEN UPDATE SET c.newkey = p.newkey;


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in message
news:A0803648-D64E-45AD-A064-6B5EEA527041@microsoft.com...
>I know how to create a foreign key. Now I want to populate my new foreign key.
> I can make a join on the old foreign key. I wnat to use the old join to
> poulate my new foreign key.
> --
> Arne Garvander
> (I program VB.Net for fun and C# to get paid.)
>
>
> "Tibor Karaszi" wrote:
>
>> Is it the referencing or referenced table you want to change? Anyhow, removing a foreign key you
>> use
>> ALTER TABLE ... DROP CONTRAINT to do. And to add a foreign key you do ALTER TABLE ... ADD
>> CONSTRAINT...
>>
>> Your choice of words confuses me however, so you might want to describe it a bit more...
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Arne Garvander" <ArneGarvander@discussions.microsoft.com> wrote in message
>> news:BE298AA9-1307-40B7-9AFE-D44FD8966AF8@microsoft.com...
>> > What is the proper syntax for moving a foreign key from one table to another.
>> > My foreign key used to be a desciption. I would like it to be an integer
>> > entity.
>> > update Cat2 set Cat2.Cat1ID = (
>> > select cat1.cat1id from Cat1 inner join Cat2 on Cat1.Description =
>> > Cat2.c2c1FKey)
>> > --
>> > Arne Garvander
>> > (I program VB.Net for fun and C# to get paid.)
>>
>>