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.
Author |
Topic |
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-01-11 : 19:27:59
|
Hi.I created a table:MenuAccessLevelsMenuAccess ID INT PKCustID varchar(6) FKUserID varchar(12) FKMenuID smallint FKIsAdmin bitIsCustomer bitIsVisible bitI am now trying to populate this table via the query below but I ALWAYS get:quote: Msg 547, Level 16, State 0, Line 2The 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.CustIDyour relation are not accuratechange it toINSERT 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 = 1SCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2013-01-12 : 02:01:00
|
Verify thisc.CustID<>mpass.CustIDSELECT 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 = 1AND c.CustID<>mpass.CustIDIn Customer you have an idCustomerand in AccountSoftwareSettings you have another idCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
|
|
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 = 1AND c.CustID<>mpass.CustIDI can't tell you, what ID is rightCe-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb |
|
|
|
|
|
|
|