July 27, 2012 at 1:46 am
Dear All
The issue is following:
select ddb.num_cue_s,count(*) from ddb_temp_DDB ddb
group by ddb.num_cue_s
having count(*)>1
--0 records duplicates
select * from deposits_daily_balance ddb
where ddb.val_date_d = cast('2012-07-24' as datetime) or ddb.reg_date_d = cast('2012-07-24' as datetime)
-- 0 records
The types of columns in deposits_daily_balance and ddb_temp_DDB tables are identical!
Primary key for deposits_daily_balance table is composed of num_cue_s,val_date_d,reg_date_d.
declare @t_date_d datetime
set @t_date_d = cast('2012-07-24' as datetime)
INSERT INTO deposits_daily_balance(
num_cue_s,val_date_d,reg_date_d,val_date_balance_n,recalc_int_n,matured_int_n,matured_bonus_int_n,
OD_matured_int_n,int_accrued_n,bonus_int_accrued_n,OD_int_accrued_n,int_mes_n,accrued_fee_n,
paid_maintenance_fee_n,put_on_reservation_n,out_of_reservation_n,od_limit_n,od_fac_l,cor_reg_n,branch_s,
account_definition_id_n,estado_s,fee_offbalance_l,AMF_last_payment_date_d,AMF_last_accrual_date_d
)
SELECT
tdb.num_cue_s,
@t_date_d AS val_date_d,
@t_date_d AS reg_date_d,
val_date_balance_n,
recalc_int_n,
matured_int_n,
matured_bonus_int_n,
OD_matured_int_n,
int_accrued_n,
bonus_int_accrued_n,
OD_int_accrued_n,
tdb.int_mes_n,
accrued_fee_n,
paid_maintenance_fee_n,
put_on_reservation_n,
out_of_reservation_n,
tdb.od_limit_n,
tdb.od_fac_l,
cor_reg_n,
tdb.branch_s,
tdb.account_definition_id_n,
tdb.estado_s,
tdb.fee_offbalance_l,
AMF_last_payment_date_d,
AMF_last_accrual_date_d
FROM ddb_temp_DDB tdb
join accounts acct on acct.num_cue_s = tdb.num_cue_s
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the FOREIGN KEY constraint "CwFKaccounts2deposits_daily_balance". The conflict occurred in database "CW_KOS_20120719_prod", table "dbo.accounts", column 'num_cue_s'.
The statement has been terminated.
Checked the keys in ddb_temp_ddb and everything seems to be ok.
Can anyone suggest something or help in a way
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
July 27, 2012 at 4:53 am
Hi,
What's the definition of the foreign key "CwFKaccounts2deposits_daily_balance"?
Thanks
July 27, 2012 at 5:01 am
Gazareth (7/27/2012)
Hi,What's the definition of the foreign key "CwFKaccounts2deposits_daily_balance"?
Thanks
ALTER TABLE [dbo].[deposits_daily_balance] WITH NOCHECK ADD CONSTRAINT [CwFKaccounts2deposits_daily_balance] FOREIGN KEY([num_cue_s])
REFERENCES [dbo].[accounts] ([num_cue_s])
GO
ALTER TABLE [dbo].[deposits_daily_balance] CHECK CONSTRAINT [CwFKaccounts2deposits_daily_balance]
GO
Igor Micev,My blog: www.igormicev.com
July 27, 2012 at 5:11 am
Do you have any trigger on the table "deposits_daily_balance"?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 27, 2012 at 7:13 am
Kingston Dhasian (7/27/2012)
Do you have any trigger on the table "deposits_daily_balance"?
No triggers, no any other constraints
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
July 27, 2012 at 7:26 am
Hi,
SELECT
FROM ddb_temp_DDB tdb
join accounts acct on acct.num_cue_s = tdb.num_cue_s
It looks that the join is finding more than one unique record on the accounts table. Have you tried using an aggregate on this join to see what is the duplicate?
select acct.num_cue_s,count(*) from accounts
group by acct.num_cue_s
having count(*)>1
July 27, 2012 at 7:59 am
ozkary (7/27/2012)
Hi,SELECT
FROM ddb_temp_DDB tdb
join accounts acct on acct.num_cue_s = tdb.num_cue_s
It looks that the join is finding more than one unique record on the accounts table. Have you tried using an aggregate on this join to see what is the duplicate?
select acct.num_cue_s,count(*) from accounts
group by acct.num_cue_s
having count(*)>1
num_cue_s is the target of an FK constraint so therefore must be unique in the accounts table.
There could be duplicates in the ddb_temp_DDB table, but if that were the case then I'd expect to see a PK violation on deposits_daily_balance table rather than the error seen.
I'd expect that error if you were trying to insert values of num_cue_s that don't exist in accounts, but the join to accounts table would prevent that.
Definitely an odd one!
July 27, 2012 at 8:12 am
The WITH NOCHECK bit in the FK definition is a little worrying too - what's the value of is_not_trusted for the FK in sys.foreign_keys?
July 27, 2012 at 8:29 am
Dear All,
I finally manged to overpass the problem. I replaced the join with not exists ().
Anyway, I was making various selects in order to find any duplicate records, or any already existing records in deposits_daily_balance which would possibly enforce the constraint violation.
It was really unexpected, however resolved.
join accounts acct on acct.num_cue_s = tdb.num_cue_s
replaced with
where not exists (select 1 from deposits_daily_balance ddb_t where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)
Thank you for the replies, I appreciate.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
July 27, 2012 at 8:54 am
Cool, glad you found the solution. That'll definitely remove any attempt to insert duplicates.
Still odd that you were getting the FK violation rather than a PK one!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply