{"id":9152,"date":"2019-02-05T07:11:21","date_gmt":"2019-02-05T07:11:21","guid":{"rendered":"https:\/\/dbtut.com\/?p=9152"},"modified":"2019-02-05T07:11:21","modified_gmt":"2019-02-05T07:11:21","slug":"the-insert-statement-conflicted-with-the-foreign-key-constraint","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/","title":{"rendered":"The INSERT statement conflicted with the FOREIGN KEY constraint"},"content":{"rendered":"

 <\/p>\n

You can get this error when you want to inset data into a table that has the Foreing Key. It means that there is no relevant record in the Primary table that Foreign Key is linked to. The record must first be added to the primary table.<\/p>\n

Let’s make an example for a better understanding.<\/p>\n

Example:<\/strong><\/p>\n

We create two tables as follows. In the Person table we set the ID column as Primary Key.<\/p>\n

CREATE TABLE [dbo].[Person](\r\n[ID] [int] IDENTITY(1,1) NOT NULL,\r\n[Name] [varchar](50) NULL,\r\n\u00a0CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED\r\n(\r\n[ID] ASC\r\n)\r\n) ON [PRIMARY]\r\nGO\r\nCREATE TABLE [dbo].[PersonDetails](\r\n[ID] [int] IDENTITY(1,1) NOT NULL,\r\n[PersonID] int NOT NULL,\r\n[SurName] [varchar](50) NULL,\r\n[Age] int\r\n) ON [PRIMARY]\r\nGO<\/pre>\n
Now let’s set the PersonID column in the PersonDetails table as the Foreign Key of the Primary Key in the Person table.<\/p>\n
ALTER TABLE [dbo].[PersonDetails]\u00a0 WITH CHECK ADD\u00a0 CONSTRAINT [FK_PersonDetails_Person] FOREIGN KEY([PersonID])\r\nREFERENCES [dbo].[Person] ([ID])\r\nGO\r\nALTER TABLE [dbo].[PersonDetails] CHECK CONSTRAINT [FK_PersonDetails_Person]\r\nGO<\/pre>\n
Then, when there is no record in the Primary table, try to add a record to the Foreign table.<\/p>\n
USE [TestDB]\r\nGO\r\nINSERT INTO [dbo].[PersonDetails] ([PersonID],[SurName],[Age]) VALUES (1,'CAKIR',34)\r\nGO<\/pre>\n
We’ll get the error as follows. Because the Primary table does not have the corresponding ID value.<\/p>\n
Msg 547, Level 16, State 0, Line 3<\/em><\/span><\/p>\n
The INSERT statement conflicted with the FOREIGN KEY constraint “FK_PersonDetails_Person”.<\/em><\/span><\/p>\n
The conflict occurred in database “TestDB”, table “dbo.Person”, column ‘ID’.<\/em><\/span><\/p>\n
The statement has been terminated.<\/em><\/span><\/p>\n
\"\"<\/p>\n
If we add a record to the primary table first, the process of adding records to the foreign table will also be completed successfully.<\/p>\n
USE [TestDB]\r\nGO\r\nINSERT INTO [dbo].[Person] ([ID],[Name]) VALUES (1,'Nurullah')\r\nGO\r\nINSERT INTO [dbo].[PersonDetails] ([PersonID],[SurName],[Age]) VALUES (1,'CAKIR',34)\r\nGO<\/pre>\n
\"\"<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  You can get this error when you want to inset data into a table that has the Foreing Key. It means that there is no relevant record in the Primary table that Foreign Key is linked to. The record must first be added to the primary table. Let’s make an example for a better …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3,1596],"tags":[1925,1924],"class_list":["post-9152","post","type-post","status-publish","format-standard","","category-mssql","category-tsql","tag-cannot-insert-foreign-table","tag-the-conflict-occurred-in-database"],"aioseo_notices":[],"yoast_head":"\nThe INSERT statement conflicted with the FOREIGN KEY constraint - Database Tutorials<\/title>\n<meta name=\"description\" content=\"The INSERT statement conflicted with the FOREIGN KEY constraint.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The INSERT statement conflicted with the FOREIGN KEY constraint - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"The INSERT statement conflicted with the FOREIGN KEY constraint.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-05T07:11:21+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"The INSERT statement conflicted with the FOREIGN KEY constraint\",\"datePublished\":\"2019-02-05T07:11:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/\"},\"wordCount\":201,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png\",\"keywords\":[\"Cannot insert Foreign Table\",\"The conflict occurred in database\"],\"articleSection\":[\"MSSQL\",\"TSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/\",\"name\":\"The INSERT statement conflicted with the FOREIGN KEY constraint - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png\",\"datePublished\":\"2019-02-05T07:11:21+00:00\",\"description\":\"The INSERT statement conflicted with the FOREIGN KEY constraint.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png\",\"width\":1066,\"height\":330},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The INSERT statement conflicted with the FOREIGN KEY constraint\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The INSERT statement conflicted with the FOREIGN KEY constraint - Database Tutorials","description":"The INSERT statement conflicted with the FOREIGN KEY constraint.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/","og_locale":"en_US","og_type":"article","og_title":"The INSERT statement conflicted with the FOREIGN KEY constraint - Database Tutorials","og_description":"The INSERT statement conflicted with the FOREIGN KEY constraint.","og_url":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/","og_site_name":"Database Tutorials","article_published_time":"2019-02-05T07:11:21+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png","type":"","width":"","height":""}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"The INSERT statement conflicted with the FOREIGN KEY constraint","datePublished":"2019-02-05T07:11:21+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/"},"wordCount":201,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png","keywords":["Cannot insert Foreign Table","The conflict occurred in database"],"articleSection":["MSSQL","TSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/","url":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/","name":"The INSERT statement conflicted with the FOREIGN KEY constraint - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png","datePublished":"2019-02-05T07:11:21+00:00","description":"The INSERT statement conflicted with the FOREIGN KEY constraint.","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c59368f501f9.png","width":1066,"height":330},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/05\/the-insert-statement-conflicted-with-the-foreign-key-constraint\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"The INSERT statement conflicted with the FOREIGN KEY constraint"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=9152"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9152\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=9152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=9152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=9152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}