one-one relationship - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

one-one relationship

Author

Topic

rajesha
Starting Member

36 Posts

Posted - 2002-10-25 : 06:40:14

i have 3 tables table1,table2,table3 in which they have one to one

relation ship annd table1 is the main table other 2 are subset of

table1.here in table 2 and table3 contains some additional coloumns

other than in table1.
when am deleting a record from table1 curresponding coloumn is getting

deleted either from table2 or table3.but when am updating (a common

field of all the tables)in table1 the curresponding record in table2 or

table3 is not getting updated.what is the problem how can i solve it?
is there any way?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-25 : 06:49:52

Sounds like a questionable design.

Why are you duplicating data in table1, table2 and table3?

How have you set it up so that you would expect these redundant fields to be updated?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-25 : 09:19:20

Can you post your table definition, some sample data and how you are currently trying to solve the problem.

You can detect and update to a table using a trigger and then inspect what the change was and make appropriate changes to other tables.

Edited by - ValterBorges on 10/25/2002 09:19:52

rajesha
Starting Member

36 Posts

Posted - 2002-10-26 : 02:58:50

am using sqk2k
i have checked cascaded updates and deletes under cascaded inserts and updates by going the properties of the relationships in the data diagrams..is it not enough? without using trigger is it not possible?
table structure is like

CREATE TABLE [dbo].[Table1] (
[Sno] [int] NOT NULL ,
[Region_code] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Enter_dt] [datetime] NULL ,
[Disable_dt] [datetime] NULL ,
[Direct_ph] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Common_ph] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extn] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ddd] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table2] (
[sno] [int] NOT NULL ,
[Location] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table3] (
[sno] [int] NOT NULL ,
[Location] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-26 : 19:08:40

First of all I agree with NR why make things harder on yourself and duplicate data that goes against the rules.

However, assumming you do have a valid reason.

Here is what BOL has to say about cascading updates.

quote:

ON UPDATE CASCADE

Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.

So your left with triggers

Maybe you can describe why you have to do it with the 3 tables and we can come up with something better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources