.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

The DELETE statement conflicted with the REFERENCE constraint

Posted By:      Posted Date: August 27, 2010    Points: 0   Category :ASP.Net
 

I have created ASP.Net Dynamic data web site using scaffolding in Visual Studio 2010. When I click on delete in data grid for customer table I got below error message.

The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occured in database "../NORTHWIND.MDF", table "dbo.order details", column 'ProductId'

The statement has been terminated

 




View Complete Post


More Related Resource Links

The DELETE statement conflicted with the REFERENCE constraint "FK_tblAdvanceLink_tblLink

  

I"m using table adapters here. I tried to use the following in my table adapter:

ALTER TABLE tblAdvanceLInk

DROP FOREIGN KEY FK_tblAdvanceLink_tblLink

DELETE FROM [tblAdvanceLink] WHERE WebsiteID=@WebsiteID 

 

The DELETE statement conflicted with the REFERENCE constraint "FK_tblAdvanceLink_tblLink". The conflict occurred in database "LinkExchanger", table "dbo.tblAdvanceLink", column 'LinkID'.
The statement has been terminated

I get the same problem every time. So I read you could do this in a sql query. Why doesn't this work in my tableadapter? Do I have this in the right place?  Do I also put this into the other table? (tblLInk)

Isn't there a way to just relax ALL constraints so I can delete some rows? I'm deleting rows from both tables which have the corresponding websiteID


The DELETE statement conflicted with the REFERENCE constraint. The conflict occurred in database.

  

Hello,

I am getting the following error 

The DELETE statement conflicted with the REFERENCE constraint "FK_Person_Country_CountryID_CountryID".
The statement has been terminated.

I know what the problem is, but can anyone help me in catching this exception in the best possible way?

I do not want the user to delete the country if there are any FK contrains. Or a better way will be to ask the user if they want to delete the records referencing this "Country".

int countryID = Convert.ToInt32(gvCountry.DataKeys[e.RowIndex].Value);
        db.DeleteCountry(1, 1, countryID);
        db.SubmitChanges();
        gvCountry.EditIndex = -1;
        BindData();


**i am using linq to sql

Thank you.

-Vishal


The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

  

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_Allergy". The conflict occurred in database "MSCPROJECT", table "dbo.Severity", column 'SeverityCode'.


The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite".

  

How does this happen? the tblLinks doesn't even have a column called websiteID.  websiteID is the primary key of the table tblwebsite. LinkID is the primary key of tblLinks. There is not LinkID column in tblwebsite.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblLinks_tblWebSite". The conflict occurred in database "Linkexchanger", table "dbo.tblWebSite", column 'WebSiteID'. The statement has been terminated.

 

So how does this happen? How do I terminate the foreign key relationship? I understand I can add this to my Insert stored procedure:

ALTER TABLE Links
DROP CONSTRAINT FK_tblLinks_tblWebSite

but that stored procedure is used for other functions in my website, and I don't know if doing that is a good idea. Is there a way to relax the constraints for the purpose of this function here?  I'm taking data from a backup links table and trying to write a new row in the links table.


INSERT statement conflicted with COLUMN FOREIGN KEY constraint...

  

Hi there,

I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.

This is the error i get:

"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "

 

Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.

Thank you

Melanie


SQL DIAGOSITC MANAGER tools capture "SELECT *" statement while ALTER / ADD CHECK CONSTRAINT is execu

  
Hello  ,   I exectuted the code(alter/add with check)  below in SSMS . My_table has 200 millions records . While running the alter/add with check query  the server crashed and the “SQL DIAGOSITC MANAGER” which run on the top of sql serever alert the DBA that select * from my_table was obtained from the last command issued by the user using a DBCC command.      Where does the select * from my_table comes from ? I didn’t excuted select * from my_table at all .   After the sql serever re started I was able to run the command below with out any problem. I checked the SQL PROFILER for the trace there is no “select * from my_table” .   So again where this select * from my_table comes from ?   Here is my initial query ----------------------------------------------------------------- ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key1] FOREIGN KEY([Key1]) REFERENCES [Dbo].[Dim_table1] ([Key1]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key1] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key2] FOREIGN KEY([key2]) REFERENCES [Dbo].[Dim_table2] ([key2]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key2] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key3] FOREIGN KEY([d], [c], [r], [g]) REFERENCES [Dbo].my

Is this a bug in MERGE statement with DELETE/INSERT?

  
This looks to me like a bug with MERGE statement. This does not work on SQL 2008 and 2008 R2. Note that the first commneted statement works properly but the second does not. Is there any explanation why not, other than a bug? Duplication script below. use tempdb go /* IF object_id('dbo.Test', 'U') IS NOT NULL DROP TABLE dbo.Test go IF object_id('dbo.Src', 'U') IS NOT NULL DROP TABLE dbo.Src go */ IF object_id('dbo.Test', 'U') IS NULL BEGIN     CREATE TABLE dbo.Test     (         intID int NOT NULL IDENTITY PRIMARY KEY         ,sysID int NOT NULL         ,ioID int NOT NULL         ,Code nvarchar(10) NOT NULL     )     INSERT dbo.test (sysID, ioID, Code) VALUES (1, 1, 'A')       CREATE UNIQUE NONCLUSTERED INDEX [UIXF_Test] ON [dbo].[Test]     (           [sysID] ASC,           [ioID] ASC     )     WHERE ([ioID] IN ((1)))     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

Why single SQL delete statement will cause deadlock?

  

Hello everyone,

I am using SQL Server 2008 Enterprise. I am wondering why even a single delete statement of this stored procedure will cause deadlock if executed by multiple threads at the same time? Any tools or sample codes which could be used to reproduce this scenario?

For the delete statement, Param1 is a column of table FooTable, Param1 is a foreign key of another table (refers to another primary key clustered index column of the other table). There is no index on Param1 itself for table FooTable. FooTable has another column which is used as clustered primary key, but not Param1 column.

    create PROCEDURE [dbo].[FooProc]   
    ( 
     @Param1 int
     ,@Param2 int 
     ,@Param3 int 
    )   
    AS   
       
    DELETE FooTable WHERE  Param1 = @Param1    
        
    INSERT INTO FooTable   
     ( 
     Param1 
     ,Param2 
     ,Param3 

Delete statement running slow

  

Dear Friends

I've got a delete statement which is taking 40 secs to run. There's only a row to delete but it's taking 40 secs . The table has got foreign keys which references other tables. I have checked indexes exist on these foreign key columns.

I have already updated statistics with fullscan on the this table.

DELETE FROM konten WITH(ROWLOCK) WHERE KONTONR = 123456

Any ideas

Many thanks.

 


Delete Statement with nested select in where clause deletes all entries in a table

  

I have fallen into a trap unadvertedly causing all records in a table being deleted. Can anybody tell me, if this a the intended behaviour of SQL Server 2008 R2 or does anybody know a method how to safely discover those kinds of typos in advance?

Regards, Markus

Here follows a script reproducing the behaviour:

SET

 

ANSI_NULLS ON

GO

SET

 

QUOTED_IDENTIFIER ON

GO

Delete Statement Code for C# ASP.NET SQL

  

I'm having problems with creating detele functionality to my asp.net page.  I have found lots of bits of code indicating parts of how to do it, but not in its entirety.  Could someone display it in a more complete version so I could have something to compare and figure out what i am doing wrong?

 


Delete or Truncate statement for data purging

  

Hi,

I am writting a stored procedure to purge data from table every month. There is no constraint on table column except primary key.

I am using 'delete' statement as it records an entry in the transaction log for each deleted row. But it's a slow process compared to 'Truncate' statement.

Can you please suggest what should I choose (Delete or truncate) as per best practice.

Thanks

Sandy

 

 

 

 


SandyLeo

Record Count on MERGE Statement on Insert,Update,Delete

  

HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 

Thanks.

 


khrizz tell

insert statement violated because Foreign key constraint

  

I  create an SSIS package, i use data flow to import rows from different server to local server. Unfortunately, there always be error message something about insert statement violated because of foreign key "The INSERT statement conflicted with the FOREIGN KEY constraint" since the table is transactional table that has some foreign key. I want to minimize using temporary table and trying to use toolbox item in ssis.I would appreciate any since i am ssis newbie.

thanks


Saving primary key constraint when creating new table using SELECT INTO statement - SQL Server 2008

  

Using SQL Server 2008:

We have a main database - MAIN.   For monthly data extracts, I create new tables with relevant data in another database called EXTRACT.

I use SELECT INTO statements to create the tables in the EXTRACT schema.  How do I preserve the primary key constraints in the EXTRACT tables?  Do I need to write separate queries to set them?

 


Saving primary key constraint when creating new table using SELECT INTO statement - SQL Server 2008

  

Using SQL Server 2008:

We have a main database - MAIN.   For monthly data extracts, I create new tables with relevant data in another database called EXTRACT.

I use SELECT INTO statements to create the tables in the EXTRACT database.  How do I preserve the primary key constraints in the EXTRACT tables?  Do I need to write separate queries to set them?

 


A better way to reference your wizard steps using named steps

  
Note: this article uses the plain vanilla but the concepts apply equally well to its popular counterpart .

By far the most common way that I see wizard steps reference in code snippets is by their index.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend