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
 SQL Server Administration (2008)
 SQL Server 2008 Database Merge.

Author  Topic 

Anto318
Starting Member

1 Post

Posted - 2015-03-11 : 05:27:20
Hi All,

I'm hoping you might be able to help. I'm pretty new to the SQL world and I'm a little over my head.

I've merged 2 databases (exact same table schema) which collect data for an Energy UI. The merge basically found the max ID for each of the "source" individual tables and added that ID to the corresponding ID in the "Target" tables.

However when I now log into the Energy UI and try to delete anything I get the following error:

The statement has been terminated.
The DELETE statement conflicted with the REFERENCE constraint "Users_RolesUsers_FK1". The conflict occurred in database "EMMA", table "dbo.RolesUsers", column 'UserId'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: The statement has been terminated.
The DELETE statement conflicted with the REFERENCE constraint "Users_RolesUsers_FK1". The conflict occurred in database "EMMA", table "dbo.RolesUsers", column 'UserId'.


Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


Stack Trace:

[OleDbException (0x80040e2f): The statement has been terminated.
The DELETE statement conflicted with the REFERENCE constraint "Users_RolesUsers_FK1". The conflict occurred in database "EMMA", table "dbo.RolesUsers", column 'UserId'.]
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +745
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
PEMS.EMMA.DAL.DbUtil.RSExecuteNonQuery(String sqlString) +142
PEMS.EMMA.DAL.User.Delete(Int32 userId) +92
[CustomException: Unable to delete user with ID 342.]
PEMS.EMMA.DAL.User.Delete(Int32 userId) +194
EMMA.UserSetup.deleteButton_Click(Object sender, ImageClickEventArgs e) +17
System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +108
System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +118
System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565


Any help which you can give would be extremely appreciated.

Anto318

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-11 : 07:52:16
It looks like you are trying to delete a user in the table Users while references to the user occur in the table RoleUsers.

If you want to delete the user, you will need to delete all references to the user in UserRoles first. When that is done you still might not be able to delete the user as references may exist in other tables; you will have to delete those references as well.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-11 : 15:50:16
Alternately, you could modify the Foreign Key constraints to use CASCADING DELETES. You would need to know (or find) all of the tables that have a foreign key constraint that references the Users table in order to make the modifications.



Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)
Go to Top of Page
   

- Advertisement -