FK Conflict - not sure how or where

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 2008 Forums

Transact-SQL (2008)

FK Conflict - not sure how or where

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2013-01-11 : 19:27:59

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-01-12 : 01:57:45

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-01-12 : 02:01:00

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

tech_1
Posting Yak Master

129 Posts

Posted - 2013-01-12 : 08:56:10

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?

tech_1
Posting Yak Master

129 Posts

Posted - 2013-01-12 : 08:59:14

yes I do see the differences in records between customer and accountsoftwaresettings.
how can I find the bad records and not include them?

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-01-12 : 10:14:58

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources