Member Avatar for web_developer

hey all

i have a database containing many tables, 2 of the tables are same in attributes numbers and names but differs in tables names.

one is titled livecases and other is titled archivecases.
the two tables have a forgien key "typeid" from a table "ctype" to declare type of the case.

i can insert directly into archivecases but when i am using this command:
insert into archivecases select * from livecases where "condition"
i cant do it. "this sql comand makes u copy data from one table to another"
i got this error:

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_case_archive_ctype1'. The conflict occurred in database 'zzz', table 'ctype', column 'typeid'. The statement has been terminated.

what the reason for this error? wht is the answer?

sam

thanks in advance

  • 2 Contributors
  • 2 Replies
  • 124 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by web_developer

Dani AI

Generated

Short answer: the FK error means a row being inserted would put a value into the foreign-keyed column typeid that does not exist in the referenced ctype table. That can happen either because the incoming typeid values are invalid, or because columns were mapped by position (not name) so the wrong value landed in the typeid column.

Quick checks to find offending values (run these on a test copy or read-only):

-- rows in source with typeid missing from ctype
SELECT DISTINCT typeid
FROM livecases AS s
WHERE NOT EXISTS (
  SELECT 1 FROM ctype AS p WHERE p.typeid = s.typeid
);

-- alternative: values present in source but not in parent
SELECT DISTINCT typeid FROM livecases
EXCEPT
SELECT typeid FROM ctype;

Safe remediation and best practices:

  • Explicitly list target columns in the INSERT and list matching source columns in the SELECT. This avoids positional mis-mapping if schemas differ or change.
  • Ensure the parent (ctype) contains the required keys before inserting children, or correct/mask invalid typeid values in the source.
  • Use a transaction or small batches for large moves so you can roll back on errors and verify results.
  • Avoid disabling or dropping FK constraints unless you fully re-validate and restore integrity afterwards.

Context for this thread: was correct to suggest specifying fields, and confirmed the actual cause was column-order misalignment. For formal behavior read Microsoft documentation on how INSERT works and how foreign keys enforce referential integrity: INSERT (Transact-SQL) and .

Member Avatar for Techscrip

Try providing the field names in your query...

Insert Into archivecases(CompanyName,City)
Select CompanyName, City From livecases
Where Country = 'Germany'

or

check the value entered in livecases table matches with the options available in table 'ctype'

Member Avatar for web_developer

thanks man
ur replay help me to find the answer
the problem was i must put the attributes in the 2 tables in same order.

sam

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.