| Author |
Topic |
|
blocker
Yak Posting Veteran
89 Posts |
 Posted - 2010-04-04 : 20:54:14
|
| I have two tables, tbl_custid and tbl_trans. Ive created a primary key in tbl_custid in the cusid field which has a foreign key in tbl_trans. It works when i delete a data in tbl_custid who's cusid is the same as in tbl_trans. But when ive changed the value of the balance field in tbl_custid, the balance field value in tbl_trans was not changed/affected. Whats wrong with my relationships.?Heres the T-SQL of the two tables./****** tbl_custid creation ******/USE [Referencing_Test]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbl_custid]( [cusid] [varchar](50) NOT NULL, [balance] [varchar](50) NULL, CONSTRAINT [PK_tbl_custid_1] PRIMARY KEY CLUSTERED ( [cusid] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO/****** tbl_trans creation ******/USE [Referencing_Test]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbl_trans]( [transid] [varchar](50) NOT NULL, [balance] [varchar](max) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[tbl_trans] WITH CHECK ADD CONSTRAINT [FK_tbl_trans_tbl_custid] FOREIGN KEY([transid])REFERENCES [dbo].[tbl_custid] ([cusid])ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE [dbo].[tbl_trans] CHECK CONSTRAINT [FK_tbl_trans_tbl_custid]GO |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2010-04-04 : 23:31:32
|
| So that means i will manually update the value of the balance field.? Is their any way to update the balance field via CONSTAINTS or foreign key by just referencing its ID from the main table(tbl_custid).? Thank you guys gor giving time.. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2010-04-04 : 23:56:20
|
| The user only changes the value of the balance on tbl_custid. Once updated, it will automatically change the balance value of the tbl_trans table for viewing purposes. |
|
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2010-04-05 : 01:40:38
|
| Anyway,Thank you for this composite foreign key. Ill take a read for this to solve the problem. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-05 : 05:04:52
|
| i think what you need to do is to implement a trigger to enforce this change upon updation on cust table. Alternatively you could implement this as a part of your original update procedure also------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2010-04-05 : 23:50:24
|
| Once again, thank you guys. I made it correct using triggers. I just want to ask about the efficiency of triggers in terms of data transmission and reliability compare to constraints. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-06 : 00:28:29
|
| triggers certainly have some impact on performance. If you can, you may add the logic along with update procedure itself.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2010-04-06 : 21:58:43
|
| thank you very much. will post the trigger result as new topic.God bless us all |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
HELP WITH CONSTRAINTS...