How i can modify the Parent table for my Sql server 2008 r2 FK

Question

I have a FK inside my table but i want to modify the parent table for the FK . so is there an alter command that can achieve this ? or i need to remove the FK and create a new one ? Thanks

i want to chnage the parent table which the FK is referring ,, i am not talkign about the data , i want the parent table which is the FK refering to to be chnaged — John John, Apr 17 '14 at 14:03
What exactly you want to modify in your parent table? looks to me, you need an `alter` statement. — Rahul, Apr 17 '14 at 14:18
i can do this from the management studio easily, but i want to generate an sql command to do so .. — John John, Apr 17 '14 at 14:20
well I mean you want to add column/delete column ... what kind of alteration? be specific please — Rahul, Apr 17 '14 at 14:21
now my current FK inside TableA is referring to another table primary key TableB. but i need my modify my current FK to refer to tableC instead of tableB ... this what i need (to modify the parent table for my FK) — John John, Apr 17 '14 at 14:42
See my edited answer. You should clarify all this in your question before hand. — Rahul, Apr 17 '14 at 17:01

Racil Hilan · Accepted Answer · 2014-04-17T16:54:33.077

Add this to your PK and it will automatically update all FKs for you:

ON UPDATE CASCADE

For full details, you can read this article.

EDIT Based on your comment, if you want to change the PK data type, it depends on the change:

If the old type can be implicitly casted to the new type without any loss:
1. Change the PK type first.
2. Change the FK type to the same.
If the old type cannot be implicitly casted to the new type without any loss:
1. Break the relationship first (i.e. remove the FK restriction/index).
2. Convert the PK. If the data needs to be modified, save both the old values and the new ones in a temporary table.
3. Convert the FK. If the PK data was changed in previous step, update the FK using the mapped values from the temporary table.
4. Create the relationship again (i.e. create the FK restriction/index).

To modify the data type, use the ALTER command, the syntax is:

ALTER TABLE table_name
ALTER COLUMN column_name datatype

Examples:

ALTER TABLE table_name
ALTER COLUMN id NUMBER(10,2);

ALTER TABLE table_name
ALTER COLUMN id VARCHAR(20);

For full details, you can read this article.

i want to change the parent table which my FK is referring to ? i do not want to chnage the data .. — John John, Apr 17 '14 at 14:03

Rahul · Answer 2 · 2014-04-17T17:05:45.330

Looks like you are looking for alter statement but since you didn't mention exactly what you are looking to modify; I assume that you want to change column data type size. You can do something like this (an example; say you want to change size from 10 to 15)

alter table sample3 
alter column name varchar(15)

EDIT:

In that case this is what you should be doing. You need to drop the existing constraint and recreate the constraint to point to TableC

alter table TableA
drop constraint your_FK_constraint_name

alter table TableA
add constraint constraint_name 
FOREIGN KEY (column_name) references TableC(some other column name)

An Example:

alter table sample2
drop constraint FK__sample2__realnam__09DE7BCC

alter table sample2
add constraint FK__sample2__realnam 
FOREIGN KEY (realname) references sample1(name)

@johnG, yes that's what should be done and in that order only. same like I have posted in my edited answer. Also, don't forget to mark it as answer if it helped. — Rahul, Apr 17 '14 at 17:22

score 1 · Answer 3 · edited May 23 '17 at 10:26

Based on this comment, "now my current FK inside TableA is referring to another table primary key TableB. but i need my modify my current FK to refer to tableC instead of tableB ... this what i need (to modify the parent table for my FK)– "

The parent table is TableB. No action is required on that table.

On TableA, you have to:

Drop the existing foreign key constraint.
Update as necessary so that all values in the applicable column have a matching value in TableC.
Add a new foreign key constraint.

in that order.

Edit Starts Here

Here is a link to the syntax,

How i can modify the Parent table for my Sql server 2008 r2 FK

3 Answers3