July 12, 2012 at 5:39 am
Hi,
I am a newbie and I am trying to change the content of a field in a column which is the primary key and in relation with other tables. (In Sql server management studio 2005)
When I try to change the contents, it gives me the error message:
(Data in row 113 was not committed:
Error source: Net SqlClient Data Provider.
Error message: The UPDATE statement conflicted with the REFERENCE constraint "Fk_Inventory_BookLocation". The conflict occured in Database "books" , table "dbo.Inventory". The statement has been terminated.)
Could somebody please help?
Thank you
July 12, 2012 at 5:45 am
Hi
This error means that you have a foreign key referencing your table.
To perform this update you will have to remove the constraint Fk_Inventory_BookLocation from the table
I would be very careful doing this, the constraint was put there for a reason and you could end up orphaning records or worse..
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 12, 2012 at 5:47 am
kevinsd1 (7/12/2012)
Hi,I am a newbie and I am trying to change the content of a field in a column which is the primary key and in relation with other tables. (In Sql server management studio 2005)
When I try to change the contents, it gives me the error message:
(Data in row 113 was not committed:
Error source: Net SqlClient Data Provider.
Error message: The UPDATE statement conflicted with the REFERENCE constraint "Fk_Inventory_BookLocation". The conflict occured in Database "books" , table "dbo.Inventory". The statement has been terminated.)
Could somebody please help?
Thank you
Share the DDL for the table concerned and the tables named Inventory
There is a column( probably BookLocation ) in the table Inventory which has a FOREIGN KEY relationship with the PRIMARY KEY column you are trying to update
Additionally check if there is any other table which has a FOREIGN KEY relationship with the PRIMARY KEY column you are talking about
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 12, 2012 at 5:58 am
Andy Hyslop (7/12/2012)
HiThis error means that you have a foreign key referencing your table.
To perform this update you will have to remove the constraint Fk_Inventory_BookLocation from the table
I would be very careful doing this, the constraint was put there for a reason and you could end up orphaning records or worse..
Removing the FOREIGN KEY is probably the worst option
Let the OP share the DDL, we can try for some other solutions based on that
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 12, 2012 at 6:46 am
Thank you for your promt response,
Here is the DDL for the tables with primary key and the ones with relations.
I am trying to update a field in the CODEEMPLACEMENT column. I am sorry , I am not familair with SQL.
Books_Location table: (Livres = Books, LivreEmplacement = bookslocation, Inventaire = inventory)
CREATE TABLE [dbo].[LivreEmplacement](
[NOLIVRE] [int] NOT NULL,
[CODEEMPLACEMENT] [varchar](50) COLLATE French_CI_AS NOT NULL,
CONSTRAINT [PK_LivreEmplacement] PRIMARY KEY CLUSTERED
(
[NOLIVRE] ASC,
[CODEEMPLACEMENT] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Livres]
GO
ALTER TABLE [dbo].[LivreEmplacement] WITH NOCHECK ADD CONSTRAINT [FK_LivreEmplacement_Emplacements] FOREIGN KEY([CODEEMPLACEMENT])
REFERENCES [dbo].[Emplacements] ([CODEEMPLACEMENT])
GO
ALTER TABLE [dbo].[LivreEmplacement] CHECK CONSTRAINT [FK_LivreEmplacement_Emplacements]
GO
ALTER TABLE [dbo].[LivreEmplacement] WITH NOCHECK ADD CONSTRAINT [FK_LivreEmplacement_Livres] FOREIGN KEY([NOLIVRE])
REFERENCES [dbo].[Livres] ([NOLIVRE])
GO
ALTER TABLE [dbo].[LivreEmplacement] CHECK CONSTRAINT [FK_LivreEmplacement_Livres]
Inventory table:
CREATE TABLE [dbo].[Inventaire](
[CODEEMPLACEMENT] [varchar](50) COLLATE French_CI_AS NOT NULL,
[NOLIVRE] [int] NOT NULL,
[QUANTITE] [int] NOT NULL,
CONSTRAINT [PK_Inventaire_1] PRIMARY KEY CLUSTERED
(
[CODEEMPLACEMENT] ASC,
[NOLIVRE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Code d''emplacement' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Inventaire', @level2type=N'COLUMN', @level2name=N'CODEEMPLACEMENT'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Numéro du livre' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Inventaire', @level2type=N'COLUMN', @level2name=N'NOLIVRE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Quantité en inventaire' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Inventaire', @level2type=N'COLUMN', @level2name=N'QUANTITE'
GO
USE [Livres]
GO
ALTER TABLE [dbo].[Inventaire] WITH NOCHECK ADD CONSTRAINT [FK_Inventaire_LivreEmplacement] FOREIGN KEY([NOLIVRE], [CODEEMPLACEMENT])
REFERENCES [dbo].[LivreEmplacement] ([NOLIVRE], [CODEEMPLACEMENT])
GO
ALTER TABLE [dbo].[Inventaire] CHECK CONSTRAINT [FK_Inventaire_LivreEmplacement]
thanks
July 12, 2012 at 8:18 am
Try the below steps:
1. Update the appropriate NOLIVRE and CODEEMPLACEMENT in Inventaire first to NULL
2. Update the appropriate CODEEMPLACEMENT column in LivreEmplacement table by using your query
3. Update the appropriate NOLIVRE and CODEEMPLACEMENT in Inventaire to the new values as per your previous query in Step 2
Share the UPDATE query you are using if you have any doubts, I will try to create the script based on that.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 12, 2012 at 9:49 am
Kingston Dhasian (7/12/2012)
Try the below steps:1. Update the appropriate NOLIVRE and CODEEMPLACEMENT in Inventaire first to NULL
2. Update the appropriate CODEEMPLACEMENT column in LivreEmplacement table by using your query
3. Update the appropriate NOLIVRE and CODEEMPLACEMENT in Inventaire to the new values as per your previous query in Step 2
Share the UPDATE query you are using if you have any doubts, I will try to create the script based on that.
NOLIVRE+CODEEMPLACEMENT can't both be null, since they're the primary key in both tables. (That kind of looks like another issue, but that's not the primary focus here). It sounds lke you might need to use one or more tamp entries to "hold" the references while you adjust them.
OP - You're not actually showing us the table which is giving you the trouble. Per the error message the "error occurred in table Books", which is NOT part of the DDL you provided.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 13, 2012 at 12:48 am
Matt Miller (#4) (7/12/2012)
Kingston Dhasian (7/12/2012)
Try the below steps:1. Update the appropriate NOLIVRE and CODEEMPLACEMENT in Inventaire first to NULL
2. Update the appropriate CODEEMPLACEMENT column in LivreEmplacement table by using your query
3. Update the appropriate NOLIVRE and CODEEMPLACEMENT in Inventaire to the new values as per your previous query in Step 2
Share the UPDATE query you are using if you have any doubts, I will try to create the script based on that.
NOLIVRE+CODEEMPLACEMENT can't both be null, since they're the primary key in both tables. (That kind of looks like another issue, but that's not the primary focus here). It sounds lke you might need to use one or more tamp entries to "hold" the references while you adjust them.
OP - You're not actually showing us the table which is giving you the trouble. Per the error message the "error occurred in table Books", which is NOT part of the DDL you provided.
I didn't notice that the NOLIVRE+CODEEMPLACEMENT combination was a PRIMARY KEY in both tables
In that case, as you said the Step 1 will not be possible
The OP will then probably need to delete the entry in Inventory table, store it in a temporary table, update the main table and re-insert the updated rows into the Inventory table back from the temporary table. In case the OP comes back with his actual update query, we can provide the script.
Additionally, just for clarification, the table giving the problem is Inventory( Inventaire ) with the database as Books.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply