SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 FK Conflict - not sure how or where
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

127 Posts

Posted - 01/11/2013 :  19:27:59  Show Profile  Reply with Quote
Hi.

I created a table:

MenuAccessLevels
MenuAccess ID INT PK
CustID varchar(6) FK
UserID varchar(12) FK
MenuID smallint FK
IsAdmin bit
IsCustomer bit
IsVisible bit


I am now trying to populate this table via the query below but I ALWAYS get:

quote:


Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MenuAccessLevels_Customer". The conflict occurred in database "Production", table "dbo.Customer", column 'CustID'.
The statement has been terminated.



I'm not sure how there can be a conflict?
any ideas where I am going wrong?

quote:


INSERT INTO MenuAccessLevels (CustID, UserID, MenuID, IsAdmin)
SELECT mpass.CustID, ual.UserID, sm.MenuID, sm.IsAdmin
FROM SoftwareMenus sm
INNER JOIN UsersAccessLevels ual ON
ual.MenuID = sm.MenuID
INNER JOIN UsersList mul ON
mul.UserID = ual.UserID
INNER JOIN Customer c ON
c.CustID = mul.CustID
INNER JOIN AccountSoftwareSettings mpass ON
mpass.MenuID = sm.MenuID
WHERE sm.MenuID = 1


Edited by - tech_1 on 01/11/2013 19:28:54

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 01/12/2013 :  01:57:45  Show Profile  Reply with Quote
Hi,

An idee :

You have a value in CustID, that is not "good"

In MenuAccessLevels table, CustID it's Fk to Customer table.

In your insert , the value for CustID it's from AccountSoftwareSettings mpass.CustID.Change it to c.CustID

your relation are not accurate

change it to

INSERT INTO MenuAccessLevels (CustID, UserID, MenuID, IsAdmin)
SELECT c.CustID, ual.UserID, sm.MenuID, sm.IsAdmin
FROM SoftwareMenus sm
INNER JOIN UsersAccessLevels ual ON ual.MenuID = sm.MenuID
INNER JOIN UsersList mul ON mul.UserID = ual.UserID
INNER JOIN Customer c ON c.CustID = mul.CustID
INNER JOIN AccountSoftwareSettings mpass ON mpass.MenuID = sm.MenuID
WHERE sm.MenuID = 1


S




Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 01/12/2013 :  02:01:00  Show Profile  Reply with Quote

Verify this
c.CustID<>mpass.CustID


SELECT c.CustID, mpass.CustID , *,--ual.UserID, sm.MenuID, sm.IsAdmin
FROM SoftwareMenus sm
INNER JOIN UsersAccessLevels ual ON ual.MenuID = sm.MenuID
INNER JOIN UsersList mul ON mul.UserID = ual.UserID
INNER JOIN Customer c ON c.CustID = mul.CustID
INNER JOIN AccountSoftwareSettings mpass ON mpass.MenuID = sm.MenuID
WHERE sm.MenuID = 1
AND c.CustID<>mpass.CustID

In Customer you have an idCustomer
and in AccountSoftwareSettings you have another id


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

tech_1
Posting Yak Master

127 Posts

Posted - 01/12/2013 :  08:56:10  Show Profile  Reply with Quote
Thanks. The relationship does match between MenuAccessLevels and Customer.
However the AccountSoftwareSettings does not have the relationship defined to the customer which is a problem so yes, maybe the problem is here.
Is there a way to do the insert and not include the bad records?
Go to Top of Page

tech_1
Posting Yak Master

127 Posts

Posted - 01/12/2013 :  08:59:14  Show Profile  Reply with Quote
yes I do see the differences in records between customer and accountsoftwaresettings.
how can I find the bad records and not include them?
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 01/12/2013 :  10:14:58  Show Profile  Reply with Quote
Bad Records.Here is the select that show u difference

SELECT c.CustID, mpass.CustID , *,--ual.UserID, sm.MenuID, sm.IsAdmin
FROM SoftwareMenus sm
INNER JOIN UsersAccessLevels ual ON ual.MenuID = sm.MenuID
INNER JOIN UsersList mul ON mul.UserID = ual.UserID
INNER JOIN Customer c ON c.CustID = mul.CustID
INNER JOIN AccountSoftwareSettings mpass ON mpass.MenuID = sm.MenuID
WHERE sm.MenuID = 1
AND c.CustID<>mpass.CustID

I can't tell you, what ID is right


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000