April 20, 2012 at 11:22 am
HI all ,
I am deleting some rows in the table ..
I am Getting an Error Message : The DELETE statement conflicted with the REFERENCE constraint "FK_Users_Divisions". The conflict occurred in database "", Table.
CREATE TABLE [DBo].[Table1](
[DivisionName] [dbo].[typeRFQDivision] NOT NULL,
[DivisionDescription] [nvarchar](150) NULL,
CONSTRAINT [PK_Divisions] PRIMARY KEY CLUSTERED
(
[DivisionName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
]
GO
/****** Object: Table [UserInfo].[Users] Script Date: 04/20/2012 13:16:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo.].[Table2](
[SalesID] [dbo].[typeSalesID] NOT NULL,
[VistaID] [nvarchar](20) NULL,
[FirstName] [dbo].[typeFirstName] NULL,
[LastName] [dbo].[typeLastName] NULL,
[MiddleInitial] [dbo].[typeMiddleInitial] NULL,
[Zone] [dbo].[typeZone] NULL,
[District] [dbo].[typeDistrict] NULL,
[Office] [dbo].[typeOffice] NULL,
[UserType] [int] NOT NULL,
[DistributorVistaNo] [dbo].[typeCustomerNumber] NULL,
[Division] [dbo].[typeRFQDivision] NULL,
[Email] [dbo].[typeEmail] NULL,
[Createddate] [datetime] NULL,
[LastChangeDate] [datetime] NULL,
[Password] [nvarchar](50) NULL,
[TeamID] [dbo].[typeOMANTeamID] NULL,
[RegisterDate] [datetime] NULL,
[HomeDrawer] [dbo].[typeDrawerName] NULL,
[RepCode] [nvarchar](10) NULL,
[SalesOfficeCode] [dbo].[typeSalesOfficeCode] NULL,
[InstallIndex] [int] NULL,
[LE] [int] NOT NULL,
[OfficePhone] [dbo].[typePhoneNumber] NULL,
[CellPhone] [dbo].[typePhoneNumber] NULL,
[Fax] [dbo].[typePhoneNumber] NULL,
[Email2] [dbo].[typeEmail] NULL,
[EmployeeNumber] [nvarchar](50) NULL,
[SMSProviderID] [int] NULL,
[IsEmployee] [bit] NOT NULL,
CONSTRAINT [PK_USER_PROFILE_1] PRIMARY KEY NONCLUSTERED
(
[SalesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2] WITH CHECK ADD CONSTRAINT [FK_Users_BidManagerDrawers] FOREIGN KEY([HomeDrawer])
REFERENCES [Config].[BidManagerDrawers] ([DrawerName])
GO
ALTER TABLE [dbo].[Table2] CHECK CONSTRAINT [FK_Users_BidManagerDrawers]
GO
ALTER TABLE [dbo].[table2] WITH CHECK ADD CONSTRAINT [FK_Users_Divisions] FOREIGN KEY([Division])
REFERENCES [RFQ].[Divisions] ([DivisionName])
GO
ALTER TABLE [dbo].[table2] CHECK CONSTRAINT [FK_Users_Divisions]
GO
ALTER TABLE [dbo].[table2] WITH CHECK ADD CONSTRAINT [FK_Users_Users] FOREIGN KEY([SalesID])
REFERENCES [UserInfo].[Users] ([SalesID])
GO
ALTER TABLE [dbo].[table2] CHECK CONSTRAINT [FK_Users_Users]
GO
ALTER TABLE [dbo].[table2] WITH NOCHECK ADD CONSTRAINT [FK_Users_UserTypes] FOREIGN KEY([UserType])
REFERENCES [dbo].[table2] ([UserType])
GO
ALTER TABLE [dbo].[table2] CHECK CONSTRAINT [FK_Users_UserTypes]
GO
ALTER TABLE [dbo].[table2] ADD CONSTRAINT [DF__USER_PROFILE__LE__0E0EFF63] DEFAULT ((1)) FOR [LE]
GO
ALTER TABLE [dbo].[table2] ADD CONSTRAINT [DF_Users_IsEmployee] DEFAULT ((0)) FOR [IsEmployee]
GO
I need to delete the data in table 1. I know before delete the data in table1 I need to delete it in table2. I don't want to delete the data in table and also I don't want to disable the constraint. Please let me know is there any possible way to delete the data in table1
April 20, 2012 at 11:24 am
Sorry, confused. Would you please elaborate on the following:
I need to delete the data in table 1. I know before delete the data in table1 I need to delete it in table2. I don't want to delete the data in table and also I don't want to disable the constraint. Please let me know is there any possible way to delete the data in table1
April 20, 2012 at 11:31 am
I need to delete rows in table 1 :
I am getting an Error message :
The DELETE statement conflicted with the REFERENCE constraint "FK_Users_Divisions". The conflict occurred in database "BidManagerCommon", table "Table2", column divison .
I believe the rows in the table 2 should be deleted before deleting the rows in table1. but, I don't want to delete the rows in the table2 and also I don't want to drop or disable the constraint .
April 20, 2012 at 11:43 am
kodalisridevi (4/20/2012)
I need to delete rows in table 1 :I am getting an Error message :
The DELETE statement conflicted with the REFERENCE constraint "FK_Users_Divisions". The conflict occurred in database "BidManagerCommon", table "Table2", column divison .
I believe the rows in the table 2 should be deleted before deleting the rows in table1. but, I don't want to delete the rows in the table2 and also I don't want to drop or disable the constraint .
Well those are your options...why do you want to violate the declared referential integrity constraint in place but yet you say you are not interested in dropping it? This request sounds a bit unscrupulous.
However, in the spirit of enabling you to do what you asked, despite saying you do not want to drop the constraint you could drop the constraint, delete rows from Table1, and then re-add the FK WITH NOCHECK which happens fast because it bypasses the checking for rows that violate the constraint. Just know that it creates these orphans, obviously, and can have a negative impact on the optimizer preventing it from generating optimal plans because it will no longer consider the constraint "trusted" until you have the constraint "CHECK"ed.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2012 at 11:46 am
There is a third option. Update the rows in Table2 so that the constraint will still be valid after the delete. You can either update that column to reference a record that will remain in Table 1 after the delete or update that column to NULL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 20, 2012 at 11:51 am
drew.allen (4/20/2012)
There is a third option. Update the rows in Table2 so that the constraint will still be valid after the delete. You can either update that column to reference a record that will remain in Table 1 after the delete or update that column to NULL.Drew
A distinct possibility...my post does not make the assumption that the rows in Table2 are open for modification or that the FK column is valid as NULL, even though it is NULLable.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply