Database table data modifications with PK/FK relationship

[ebade2000@gmail.com]

I have two tables (T1 and T2). In T1 I have a field FT1 that is a
primary key in T2 I have a field FT2 that is a foreign key linked to
FT1. These fields have been populated with data. Lets say that in one
row of data I have in T1 under FT1 "my cell" as the data entry,
similarly with T2 under FT2 I have 2 rows of data that also have "my
cell" as the data entry. What is the best line of action is I wanted
to change "my cell" to "my data"?

[David Portas]

Re: Database table data modifications with PK/FK relationship

On 21 Mar, 21:48, ebade2...@gmail .com wrote:

I have two tables (T1 and T2). In T1 I have a field FT1 that is a
primary key in T2 I have a field FT2 that is a foreign key linked to
FT1. These fields have been populated with data. Lets say that in one
row of data I have in T1 under FT1 "my cell" as the data entry,
similarly with T2 under FT2 I have 2 rows of data that also have "my
cell" as the data entry. What is the best line of action is I wanted
to change "my cell" to "my data"?

-- Method 1.
INSERT INTO T1 (FT1) VALUES ('MY DATA');
UPDATE T2 SET FT2 = 'MY DATA' WHERE FT2 = 'MY CELL';
DELETE FROM T1 WHERE FT1 = 'MY CELL';

-- Method 2.
ALTER TABLE T2 ADD CONSTRAINT fk_t2_t1
FOREIGN KEY (ft2) REFERENCES T1 (FT1) ON UPDATE CASCADE;
UPDATE T1 SET FT1 = 'MY DATA' WHERE FT1 = 'MY CELL';

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Ed Murphy]

Re: Database table data modifications with PK/FK relationship

ebade2000@gmail .com wrote:

I have two tables (T1 and T2). In T1 I have a field FT1 that is a
primary key in T2 I have a field FT2 that is a foreign key linked to
FT1. These fields have been populated with data. Lets say that in one
row of data I have in T1 under FT1 "my cell" as the data entry,
similarly with T2 under FT2 I have 2 rows of data that also have "my
cell" as the data entry. What is the best line of action is I wanted
to change "my cell" to "my data"?

Revise the table structures so that the primary key is something that
never changes (e.g. INT IDENTITY) and "my cell" / "my data" is a non-PK
field in T1.

Side note: Please use real table/column names (e.g. table = Customers,
column = CustomerKey) rather than confusingly similar abbreviations
(table = T1, column = FT1).

[ebade2000@gmail.com]

Re: Database table data modifications with PK/FK relationship

Sorry for not specifying extra details, I thought it was a general
issue, I am running MS SQL 2000.

Thanks for your help, I very much prefer method 1. After looking at
your solution, it seemed so obvious. I probably need some rest LOL.

PS: Doesnt make any sense to post the error now but I will incase it
helps someone elses search
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with TABLE REFERENCE constraint
'FK_T2_T1'. The conflict occurred in database 'DB_Test', table 'T2'.
The statement has been terminated.

Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with TABLE FOREIGN KEY constraint
'FK_T2_T1'. The conflict occurred in database 'DB_Test', table 'T1'.
The statement has been terminated.