Ms sql error 547

Home > SQL Server Error Messages > Msg 547 — DELETE statement conflicted with COLUMN REFERENCE constraint Constraint Name.  The conflict occurred in database Database Name, table Table Name, column Column Name.

SQL Server Error Messages — Msg 547 — DELETE statement conflicted with COLUMN REFERENCE constraint Constraint Name.  The conflict occurred in database Database Name, table Table Name, column Column Name.

SQL Server Error Messages — Msg 547 Error Message Server: Msg 547, Level 16, State 1, Line 1 DELETE statement conflicted with COLUMN REFERENCE constraint Constraint Name. The conflict occurred in database Database Name, table Table Name, column Column Name. The statement has been terminated. Causes: This error occurs if you are trying to delete a record from a table that has a PRIMARY KEY and the record being deleted is being referenced as a FOREIGN KEY in another table. To illustrate, assuming that in your Loans System, you have two tables, a table containing the different loan types accepted by the system ([dbo].[Loan Type]) and a table containing the loan applications ([dbo].[Loan Application]). The Loan Type ID in the Loan Application table references the Loan Type ID in the Loan Type table. CREATE TABLE [dbo].[Loan Type] ( [Loan Type ID] VARCHAR(20) NOT NULL PRIMARY KEY, [Name] VARCHAR(50) NOT NULL ) GO CREATE TABLE [dbo].[Loan Application] ( [Loan ID] INT NOT NULL PRIMARY KEY IDENTITY(1,1), [Loan Type ID] VARCHAR(20) NOT NULL REFERENCES [dbo].[Loan Type] ( [Loan Type ID] ), [Borrower] VARCHAR(100) NOT NULL ) GO Here’s some sample records from the 2 tables: [dbo].[Loan Type] Loan Type ID Name ------------- ------------------ CAR Car Loan HOME Home Loan HOME EQUITY Home Equity Loan PERSONAL Personal Loan STUDENT Student Loan [dbo].[Loan Application] Loan ID Loan Type ID Borrower -------- ------------- -------------------- 1 HOME Old MacDonald 2 HOME Three Little Pigs 3 CAR Cinderella 4 STUDENT Peter Pan Due to changes in business requirements, you may be asked to delete the Student Loan from the available loan types accepted by the company. DELETE FROM [dbo].[Loan Type] WHERE [Loan Type ID] = 'STUDENT' GO But since there’s an existing record in the Loan Application table that references the Student Loan loan type, you get the following error: Server: Msg 547, Level 16, State 1, Line 1 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Loan Appl_Loan'. The conflict occurred in database 'TestDb', table 'Loan Application', column 'Loan Type ID'. The statement has been terminated. Solution / Work Around: One way to avoid this error is to first delete all records from the other tables that reference the PRIMARY KEY. DELETE FROM [dbo].[Loan Application] WHERE [Loan Type ID] = ‘STUDENT’ GO DELETE FROM [dbo].[Loan Type] WHERE [Loan Type ID] = ‘STUDENT’ GO But deleting records from other tables may not be acceptable because these records may still be needed. An alternative to the physical deletion of the record is the implementation of a logical deletion of the record.  This can be done by adding a new column in the table that will determine if the record is still active or not.  A bit column can serve as a status flag wherein a value of 1 means that the record is still active while a value of 0 means that the record is not used anymore. ALTER TABLE [dbo].[Loan Type] ADD [Status] BIT NOT NULL DEFAULT (1) GO UPDATE [dbo].[Loan Type] SET [Status] = 0 WHERE [Loan Type ID] = ‘STUDENT’ GO Loan Type ID Name Status ------------- ------------------ ------ CAR Car Loan 1 HOME Home Loan 1 HOME EQUITY Home Equity Loan 1 PERSONAL Personal Loan 1 STUDENT Student Loan 0

Related Articles :

Frequently Asked Questions — SQL Server Error Messages Frequently Asked Questions — INSERT Statement Frequently Asked Questions — SELECT Statement

Sql error code 547

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

Tool: SQL Server 2008

Environment: Windows Vista

Problem: The code works perfect in staging environment. However, when I executed the code in production, an error message occurred:

Msg 547
The DELETE statement conflicted with the REFERENCE constraint . The conflict occurred in database «Mocha», table
The statement has been terminated.

the error goes away. However, the required info is not deleted from table ppl which i wish to do so.

What would you suggest to over come this issue.

Answers

It sounds like you are violating a reference constraint, which likely means your relationships are not identical in your staging and production environments. The other option is that you are incorrectly assuming that certain data exists in your production environment that is violating the reference constraint. Take a look at your references for the tables listed in the error messages in both databases. You can script out the create table statements, view dependencies, run sp_help, or likely several other ways to check on these.

Thanks,
Sam Lester (MSFT)

This posting is provided «AS IS» with no warranties, and confers no rights. Please remember to click «Mark as Answer» and «Vote as Helpful» on posts that help you. This can be beneficial to other community members reading the thread.

there is foreign key referential integrity enforced in your tables which referring to the key pn_id in the table ABC.

When the referential interity is enforced. If you try to delete the records from the parent table, it will cause error.

There are normally 3 ways to handle this:

1. set up on delete cascade in the constraint definition for the child tables.

2. use a trigger for delete to delete the records on the children tables first.

3. you need to specifically delete the records in the children tables first.

Certainly the option 1 is the best.

| Blog: MSBICOE.com | MCITP — BI, SQL Developer & DBA

Hate to mislead others, if I’m wrong slap me. Thanks!

There are normally 3 ways to handle this:

1. set up on delete cascade in the constraint definition for the child
tables.

2. use a trigger for delete to delete the records on the children tables
first.

3. you need to specifically delete the records in the children tables
first.

Certainly the option 1 is the best.

I would say that it depends. If you are deleting an order you probably want the order details to down the drain as well. Ergo, the FK constraint on OrderDetails to Orders could have ON DELETE CASACDE.

But if you delete a product for which there are orders, you may be barking up the wrong tree. It would be very bad if the FK constraint on in OrderDetails to Products was cascadind.

All we know is that Sandra got an FK error. This could be because constraints are different in staging in production. It could also be because data is different in staging there are no violations.

The correct approach is check these foreign keys and then check against the requirements. Only if you have verified that the data in the referenced tables should be deleted, you should change the constraints. It could just as well be the other way round: these rows should not be deleted.

Источник

Sql error code 547

Answered by:

Question

I’m experiencing odd errors in (and only in) VS 2005 DB Pro. I’m familiar with this error and how to resolve it in Query Analyzer / SQL Server Management Studio, but the same CREATE and ALTER statements don’t reproduce the problem in either of those environments (same server & database).

Even more odd is that this first happened with one key, but after closing & reopening the IDE, the error for that key vanished but new ones appeared for three other keys, and in the two days since I’ve been unable to clear those three errors. Here are the specifics (I’ve changed the object names to protect the guilty, but this should illustrate the behavior):

This statement:

Causes the following build error:

This is unexpected because:

This is deploying to a SQL Server 2000 SP2, so unfortunately I can’t simply add a WITH NOCHECK to the constraint script because of the constraint bug discussed in http://www.microsoft.com/technet/abouttn/flash/tips/tips_122104.mspx.

Does anyone have any insights into how to clear this up? Virtual donuts to anyone who can. 🙂

Answers

The errors occur at build time.

The bad news is that I still haven’t figured out what caused this behavior in the first place. The good news is that I actually found that a workaround for a different error gets rid of this one as well: Close Visual Studio, delete the projectname.dat file, and re-open. Presto. project builds again.

Provided the error doesn’t have a «real» SQL-based cause (like orphaned child rows), this seems to get rid of it.

Should this get marked as an answer?

When does this error occur, while deploying the project, when building the project, or when comparing schema’s and updating a DB?

Like Mark asked, it is important to understand the scenario that you are using when you are running in to this.

I am not clear if we are generating the wrong SQL or if the order of statements is incorrect.

The errors occur at build time.

The bad news is that I still haven’t figured out what caused this behavior in the first place. The good news is that I actually found that a workaround for a different error gets rid of this one as well: Close Visual Studio, delete the projectname.dat file, and re-open. Presto. project builds again.

Provided the error doesn’t have a «real» SQL-based cause (like orphaned child rows), this seems to get rid of it.

Should this get marked as an answer?

I replied to Mark’s message before I saw yours, but in short, these are hard errors at build time which fail the build. I was still able to do some Schema Compare / Data Compare operations, but Build & Deploy were halted.

Like I told Mark, simply whacking the project’s .dat file & restarting the IDE got rid of the error. If it’s still important to know what the compare & deploy behavior were under error conditions, I can try to recreate the problem and find out (emphasis on try since I’m not quite sure what the original cause was).

Did you «sync» in any changes while this happened?

It seems like your cache was not being up to date. The Schema View has a visual indication that the model is out of sync with the files on disc.

Are you running Service Release 1? This should at build time check for files that are invalidated and update the schema model, which is the part that does not seem to occur in your case.

That’s what’s weird; I am running SR1, and I didn’t do any syncs with the project as a target between the last successful build and the time the problem started. In fact, I had only deleted a number of unused stored procedures during that time, which is why I was so surprised at getting new errors in my table constraints.

The only thing I can figure is that perhaps the problem was in the project before I took it over and was simply masked by other dependency warnings at build time? I have no idea if the originating developer had SR1 or not, and whacking those procedures was the first change I made. I can see how an error would block/mask another error, and it doesn’t make sense to me that a warning could block an error, but I don’t really have any more plausible ideas. I’ve made many more substantial changes since deleting the .dat file and had no subsequent occurrences of this behavior.

Maybe it was the gremlins. They’ve certainly done stranger things behind my back in the past.

Источник

Troubleshooting MS SQL Server

This blog is introduced to discuss/share the daily challenges and workarounds of a SQL Server DBA and T-SQL and BI developer.

Pages

Jul 28, 2014

[SQLSTATE 23000] (Error 547) The statement has been terminated. [SQLSTATE 01000] (Error 3621)

SQL Agent job failed with below Error

Message
Executed as user: ArrivalsDepartures. The INSERT statement conflicted with the FOREIGN KEY constraint «FK_DepartureStatusArchive_TechnicianName». The conflict occurred in database «DatabaseName», table «dbo.Users», column ‘UserID’. [SQLSTATE 23000] (Error 547) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

Root Cause: Error is not refering the table which is having Foreign-key to the Users table rather it mentioned the constraint name FK_DepartureStatusArchive_TechnicianName.
Also the procedure is inserting data from Table A to Table B. Table B is having the foreign key to Users Table. This foreign key column is accepting NULLs but the column in Users table is Primary Key.

Fix: Actually the culprit table is Archive table and the table structure was changed recently as part of one change. As the table is Archive and it not required referential key to Users table I dropped the foreign key and now the job executed successfully.

Dropped Foreign Key: Expand the table B->expand the Keys->right click and script the key to be deleted-> run it against the Database and the constraint is deleted.

Источник

Sql error code 547

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

After Upgrade to 1610 (and client upgrade to 1610) We have some errors in the SMS_Inventory_Data_Loader.

Some clients are reporting this error: ID: 620

Microsoft SQL Server reported SQL message 547, severity 16: [23000][547][Microsoft][SQL Server Native Client 11.0][SQL Server]The INSERT statement conflicted with the FOREIGN KEY constraint «OFFICE365PROPLUSCONFIGURATIONS_DATA_FK». The conflict occurred in database «CM_XXX», table «dbo.System_DATA», column ‘Machi Please refer to your Configuration Manager documentation, SQL Server documentation, or the Microsoft Knowledge Base for further troubleshooting information.

If I look at the dataldr.log I see this error:

*** [23000][547][Microsoft][SQL Server Native Client 11.0][SQL Server]The INSERT statement conflicted with the FOREIGN KEY constraint «OFFICE365PROPLUSCONFIGURATIONS_DATA_FK». The conflict occurred in database «CM_XXX», table «dbo.System_DATA», column ‘MachineID’. : pOFFICE365PROPLUSCONFIGURATIONS_DATA SMS_INVENTORY_DATA_LOADER 17-2-2017 08:34:29 1540 (0x0604) ERROR — SQL Error in SMS_INVENTORY_DATA_LOADER 17-2-2017 08:34:29 1540 (0x0604) ERROR — is NOT retyrable. SMS_INVENTORY_DATA_LOADER 17-2-2017 08:34:29 1540 (0x0604) Rollback transaction: Machine=LT150552(GUID:D4873E8B-126F-44C8-8296-DF407B632DE7) SMS_INVENTORY_DATA_LOADER 17-2-2017 08:34:29 1540 (0x0604) Cannot process MIF XHSNUXXVO.MIF, moving it to D:SCCMinboxesauthdataldr.boxBADMIFSErrorCode_41l194liq.MIF SMS_INVENTORY_DATA_LOADER 17-2-2017 08:34:29 1540 (0x0604) STATMSG: SEV=W LEV=M SOURCE=»SMS Server» COMP=»SMS_INVENTORY_DATA_LOADER» SYS=XXX.XX.XXX.NL SITE=XXX PID=6248 TID=1540 GMTDATE=vr feb 17 07:34:29.668 2017 ISTR0=»XHSNUXXVO.MIF» ISTR1=»D:SCCMinboxesauthdataldr.boxBADMIFSErrorCode_41l194liq.MIF» ISTR2=»» ISTR3=»» ISTR4=»» ISTR5=»» ISTR6=»» ISTR7=»» ISTR8=»» ISTR9=»» NUMATTRS=0 SMS_INVENTORY_DATA_LOADER 17-2-2017 08:34:29 1540 (0x0604) Done: Machine=LT150552(GUID:D4873E8B-126F-44C8-8296-DF407B632DE7) code=4 (222 stored procs in XHSNUXXVO.MIF) SMS_INVENTORY_DATA_LOADER 17-2-2017 08:34:29 1540 (0x0604)

So it has something to do with: OFFICE365PROPLUSCONFIGURATIONS_DATA?

When I check the failed .MIF File

Start Group Name = «Office365ProPlusConfigurations» Pragma = «SMS:ADD» Key = 1 Start Attribute Name = «KeyName» Type = String Value = «Office365ProPlusConfigurations» End Attribute End Group

Another failing .MIF file

Start Group Name = «Office365ProPlusConfigurations» Pragma = «SMS:DELETE» Key = 1 Start Attribute Name = «KeyName» Type = String Value = «Office365ProPlusConfigurations» End Attribute End Group

Can anybody help me in the right direction to solve this?

Источник