473,320 Members | 1,867 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Database table data modifications with PK/FK relationship

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"?

Mar 21 '07 #1
3 7334
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/m...S,SQL.90).aspx
--

Mar 21 '07 #2
eb*******@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).
Mar 21 '07 #3
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.
Mar 21 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Christopher Brandsdal | last post by:
Hi! I have a delicatg problem.... I have made a registration form for adding my friends information in a database. The problem is that I want to connect the persons with companies in the same...
11
by: dixie | last post by:
If I wanted to be able to copy all of the tables in an existing database called Original.mde to another database called New.mde from a button click event in Original.mde, is there an easy way of...
6
by: Noozer | last post by:
I'm developing a database using MS Access and have come across a problem. The majority of my database is pretty straightforward "many to one" relationships. I have one relationship that is...
7
by: vsiat | last post by:
I am trying to create a treeview out of a database table with the typical structure ID, NAME, PARENTID, TYPE, EXTRA_INFO, where is linked to the . What I want to achieve is create a tree made...
29
by: MP | last post by:
Greets, context: vb6/ado/.mdb/jet 4.0 (no access)/sql beginning learner, first database, planning stages (I think the underlying question here is whether to normalize or not to normalize this...
15
by: philip | last post by:
On a form, I have a datagridview. This datagridview is constructed on a dataset filled by a tableadapter. The table adapter do very well what it must do when filling dataset. Insertions,...
76
MMcCarthy
by: MMcCarthy | last post by:
Normalisation is the term used to describe how you break a file down into tables to create a database. There are 3 or 4 major steps involved known as 1NF (First Normal Form), 2NF (Second Normal...
12
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a...
4
by: dgleeson3 | last post by:
Hello all I am creating a VB.Net distributed SQL server 2005 application. Each computer in the system has a database with a table of users and their telephone numbers. Each computer has a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.