| Author |
 Topic  |
|
|
tech_1
Posting Yak Master
105 Posts |
 Posted - 01/11/2013 : 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
|
Edited by - tech_1 on 01/11/2013 19:28:54
|
|
|
stepson
Yak Posting Veteran
Romania
95 Posts |
Posted - 01/12/2013 : 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
Yak Posting Veteran
Romania
95 Posts |
Posted - 01/12/2013 : 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
105 Posts |
Posted - 01/12/2013 : 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
105 Posts |
Posted - 01/12/2013 : 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
Yak Posting Veteran
Romania
95 Posts |
Posted - 01/12/2013 : 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 |
|
|
| |
Topic |
|
|
|
FK Conflict - not sure how or where
