Sql server ошибка 547 - TopOshibok.ru - решение и исправление самых разных ошибок

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

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

Источник

Remove From My Forums

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.
```
DELETE FROM  tbl_ppl WHERE id in (Select id  from temp )
```
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.
- Edited by
  
  Tuesday, October 30, 2012 9:09 PM

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)

My Blog

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.
- Marked as answer by
  Sandra VO
  Tuesday, August 7, 2012 3:41 PM
- Unmarked as answer by
  Sandra VO
  Thursday, August 9, 2012 10:22 PM
- Marked as answer by
  Sandra VO
  Monday, August 13, 2012 8:27 AM
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!
- Edited by
  Steven Wang — Shangzhou
  Friday, August 3, 2012 11:25 PM
- Marked as answer by
  Sandra VO
  Tuesday, August 7, 2012 3:39 PM
- Unmarked as answer by
  Sandra VO
  Thursday, August 9, 2012 10:22 PM
- Proposed as answer by
  Naomi N
  Sunday, August 12, 2012 8:31 PM
- Marked as answer by
  Sandra VO
  Monday, August 13, 2012 8:23 AM
- Unmarked as answer by
  Sandra VO
  Monday, August 13, 2012 8:24 AM
- Marked as answer by
  Sandra VO
  Monday, August 13, 2012 8:24 AM
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.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Proposed as answer by
  Chris Sijtsma
  Saturday, August 4, 2012 6:25 PM
- Marked as answer by
  Sandra VO
  Tuesday, August 7, 2012 3:41 PM
- Unmarked as answer by
  Sandra VO
  Thursday, August 9, 2012 10:22 PM
- Marked as answer by
  Sandra VO
  Monday, August 13, 2012 8:28 AM

Источник

547 is the error code used for any constraint violation, not just foreign keys, e.g.:

create table T (
    ID int not null,
    constraint CK_Not1 CHECK (ID != 1)
)
go
insert into T (ID) values (2)
go
update T set ID = 1


(1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Not1". The conflict occurred in database "Flange", table "dbo.T", column 'ID'.
The statement has been terminated.

That being said, I can’t think of any other type of constraint, other than foreign key, that could be violated by a DELETE statement. (Hat tip to @onedaywhen)

If you look in sys.messages, you’ll see that 547 has to be for a constraint violation:

select text from sys.messages where message_id=547 and language_id=1033

The %ls statement conflicted with the %ls constraint «%.*ls». The conflict occurred in database «%.*ls», table «%.*ls»%ls%.*ls%ls.

Источник

This is the error I am getting right now:

Msg 547, Level 16, State 0, Line 28
The INSERT statement conflicted with the FOREIGN KEY constraint «FK__Registrat__Cours__32E0915F». The conflict occurred in database «Test2», table «dbo.Course», column ‘CourseCode’.

Currently, I have this:

CREATE TABLE Student
(
    StudentID int PRIMARY KEY, 
    StudentFName varchar(30) NOT NULL,
    StudentLName varchar(40) NOT NULL,
    GPA float CHECK(GPA >= 0 AND GPA <= 4) 
)

CREATE TABLE Course
(
    CourseCode varchar(10) PRIMARY KEY,
    CourseName varchar(50) UNIQUE,
    Duration int CHECK(Duration > 0)
)

CREATE TABLE Registration
(
    CourseCode varchar(10) REFERENCES Course(CourseCode), 
    StudentID int REFERENCES Student(StudentID), 
    CourseMark float CHECK(CourseMark >= 0 AND CourseMark <= 100)
    PRIMARY KEY(CourseCode, StudentID)
)

INSERT INTO Course
VALUES('ICT710', 'C#', 30)

SELECT * FROM Registration

INSERT INTO Registration
VALUES('ICT128', 123, 78.5),
      ('ICT128', 345, 68),
      ('ICT710', 123, 80)

How do I solve this? I am trying to add those 3 rows of data to my Registration table as stated in the last lines of code.

Источник

Вопрос:

Я получаю следующую ошибку. Не могли бы вы мне помочь?

Сообщение 547, уровень 16, состояние 0, строка 1
Оператор INSERT конфликтует с ограничением FOREIGN KEY “FK_Sup_Item_Sup_Item_Cat”. Конфликт произошел в базе данных “dev_bo”, таблица “dbo.Sup_Item_Cat”. Заявление было прекращено.

Код:

insert into sup_item (supplier_id, sup_item_id, name, sup_item_cat_id,
status_code, last_modified_user_id, last_modified_timestamp, client_id)
values (10162425, 10, 'jaiso', '123123',
'a', '12', '2010-12-12', '1062425')

Последний столбец client_id вызывает ошибку. Я попытался поместить значение, которое уже существует в dbo.Sup_Item_Cat в столбец, соответствующий sup_item.. но без радости 🙁

Лучший ответ:

В вашей таблице dbo.Sup_Item_Cat он имеет ссылку внешнего ключа на другую таблицу. Способ работы FK заключается в том, что он не может иметь значение в этом столбце, которое также не находится в столбце первичного ключа ссылочной таблицы.

Если у вас есть SQL Server Management Studio, откройте его и sp_help ‘dbo.Sup_Item_Cat. Посмотрите, в каком столбце включен FK, и в каком столбце которого он ссылается. Вы вставляете некоторые плохие данные.

Сообщите мне, если вам что-то объяснено лучше!

Ответ №1

Я сам столкнулся с этой проблемой, касающейся сообщения об ошибке, полученного при заполнении поля внешнего ключа. Я попал на эту страницу в надежде найти ответ. Проверенный ответ на этой странице действительно правильный, к сожалению, я чувствую, что ответ немного неполон для людей, не знакомых с SQL. Я довольно хорошо умею писать код, но запросы SQL для меня новы, как и создание таблиц базы данных.

Несмотря на то, что проверенный ответ является правильным:

Майк М wrote-

“Способ работы FK заключается в том, что он не может иметь значение в этом столбце, которого нет также в столбце первичного ключа в указанной таблице”.

Чего не хватает в этом ответе просто;

Сначала вы должны создать таблицу, содержащую первичный ключ.

Еще один способ сказать это;

Вы должны вставить данные в родительскую таблицу, содержащую первичный ключ, прежде чем пытаться вставить данные в дочернюю таблицу, содержащую внешний ключ.

Короче говоря, многие учебные пособия, похоже, не соответствуют этому факту, так что если вы попробуете самостоятельно и не поймете, что существует порядок операций, вы получите эту ошибку. Естественно, после добавления данных первичного ключа ваши данные внешнего ключа в дочерней таблице должны соответствовать полю первичного ключа в родительской таблице, в противном случае вы все равно получите эту ошибку.

Если кто-то прочитал это далеко. Я надеюсь, что это помогло сделать проверенный ответ более ясным. Я знаю, что некоторые из вас могут почувствовать, что подобные вещи довольно просты и что открытие книги ответило бы на этот вопрос до того, как она была опубликована, но правда в том, что не все учатся одинаково.

Ответ №2

Вы пытаетесь вставить запись со значением в столбце внешнего ключа, которого нет во внешней таблице.

Например: если у вас есть таблицы “Книги” и “Авторы”, в которых “Книги” имеют ограничение внешнего ключа для таблицы “Авторы”, и вы пытаетесь вставить запись книги, для которой нет записи об авторе.

Ответ №3

Вам нужно будет опубликовать свое выражение для получения дополнительных разъяснений. Но…

Эта ошибка означает, что таблица, в которую вы вставляете данные, имеет отношение внешнего ключа к другой таблице. Прежде чем данные могут быть вставлены, сначала значение в поле внешнего ключа должно находиться в другой таблице.

Ответ №4

Проблема не в client_id из того, что я вижу. Это больше похоже на проблему с 4-м столбцом, sup_item_cat_id

Я бы запустил

sp_helpconstraint sup_item

и обратите внимание на столбец constraint_keys, возвращенный для внешнего ключа FK_Sup_Item_Sup_Item_Cat, чтобы подтвердить, какой столбец является реальной проблемой, но я уверен, что это не тот, который вы пытаетесь исправить. Кроме того, “123123” также выглядит подозрительным.

Ответ №5

Я обнаружил, что все поля должны соответствовать ТОЧНО.

Например, отправка “cat dog” не совпадает с отправкой “catdog”.

То, что я сделал для устранения этого неполадки, заключалось в script из кода FK из таблицы, в которую я вставлял данные, обратите внимание на “Foreign Key” с ограничениями (в моем случае было 2) и убедитесь, что эти 2 значения полей соответствовали ТОЧНО, поскольку они были в таблице, которая бросала ошибку ограничения FK.

Как только я исправил 2 поля, дающие мои проблемы, жизнь была хорошей!

Если вам нужно лучшее объяснение, сообщите мне.

Ответ №6

Это означает именно то, что он говорит. Вы пытаетесь вставить значение в столбец с ограничениями FK на нем, который не соответствует никаким значениям в таблице поиска.

Ответ №7

Данные родительской таблицы не существуют, поэтому она вызывает проблему. Для вышеуказанной проблемы данные недоступны в таблице dbo.Sup_Item_Cat “

Ответ №8

Дважды проверьте поля в отношении, для которого задан внешний ключ. SQL Server Management Studio, возможно, не выбрали нужные вам поля при определении отношения. Это сожгло меня в прошлом.

Ответ №9

запустить sp_helpconstraint
заплатите ВНИМАНИЕ столбцу constraint_keys, возвращенному для внешнего ключа.

Ответ №10

Отсутствие данных родительской таблицы вызывает проблему.
В вашей проблеме не доступность данных в “dbo.Sup_Item_Cat” вызывает проблему

Ответ №11

У меня была такая же проблема, когда я использовал первые преобразования кода для создания моей базы данных для приложения MVC 5. В конце концов я нашел метод seed в моем файле configuration.cs, чтобы вызвать проблему. Мой метод seed создавал запись таблицы для таблицы, содержащей внешний ключ, перед созданием записи с соответствующим первичным ключом.

Ответ №12

Я столкнулся с этой проблемой, когда мои поля значений вставки содержали вкладки и пробелы, которые не были очевидны для невооруженного глаза. Я создал свой список значений в Excel, скопировал и вставил его в SQL, и запустил запросы, чтобы найти несоответствия в моих FK-полях.

В запросах совпадения не было обнаружено, что в моем поле FK есть вкладки и пробелы, но INSERT распознал их и продолжал генерировать ошибку.

Я снова тестировал, копируя содержимое поля FK в одной записи и вставляя его в запрос вставки. Когда эта запись также потерпела неудачу, я подошел ближе к данным и, наконец, обнаружил вкладки/пробелы.

Как только я очистил удаленные вкладки/пробелы, моя проблема была решена. Надеюсь, это поможет кому-то!

Ответ №13

Я также получил ту же ошибку в моем коде SQL, это решение работает для меня,

Проверьте данные в первичной таблице. Возможно, вы вводите значение столбца, которого нет в столбце первичного ключа.

Ответ №14

Иногда это происходит, когда вы пытаетесь Insert/Update объект, в то время как foreign key который вы пытаетесь Insert/Update самом деле не существует. Поэтому убедитесь, что foreign key существует, и повторите попытку.

Источник

Question

Answers

Интересное по теме: