表因外键而发生冲突; [英] table conflicted due to foreign key;

问题描述

在我的第一个tbl_uni



Uni_id，University_Name是列





in tbl_course



C_id，University_name，Course，Major，Uni_id（FK refer tbl_uni）





in tbl_major



Mid，University_name，Course，Major，Uni_id（FK refer tbl_uni），C_id（FK refer tbl_course）





textfield = University_name

valuefield = Uni_id



错误将是



INSERT语句与FOREIGN KEY约束FK__tbl_cours__Uni_i__36B12243冲突。冲突发生在数据库大学，表dbo.tbl_university，列'Uni_id'。

in my first tbl_uni

Uni_id,University_Name are the columns


in tbl_course

C_id,University_name,Course,Major,Uni_id(FK refer tbl_uni)


in tbl_major

Mid,University_name,Course,Major,Uni_id(FK refer tbl_uni),C_id(FK refer tbl_course)

while inserting the vale by dropdownlist in tbl_course

textfield=University_name
valuefield=Uni_id

the error will be

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__tbl_cours__Uni_i__36B12243". The conflict occurred in database "University", table "dbo.tbl_university", column 'Uni_id'.

推荐答案

首先，数据库设计是完全不可接受的：

1.为什么university_name出现在tbl_course和tbl_major？

2.为什么课程出现在tbl_major？

3.为什么major出现在tbl_course？

您正在复制数据并显示您的表格未正确规范化。 数据库规范化 [ ^ ]

接下来，错误说明您尝试将新的uni_id插入tbl_course（child）并且在tbl_uni表（父）中不存在uni_id。阅读更多 SQL FOREIGN KEY Constraint [ ^ ]

了解详情： http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html [ ^ ]

First thing first, the database design is totally unacceptable:
1. Why is university_name appears in tbl_course and tbl_major?
2. Why is course appears in tbl_major?
3. Why is major appears in tbl_course?
You are duplicating data and that shows your tables are not properly normalized. Normalization of Database[^]
Next, the error stated that you attempted to insert a new uni_id into the tbl_course (child) and that uni_id did not exist in the tbl_uni table (parent). Read more SQL FOREIGN KEY Constraint[^]
Learn more: http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html[^]

下面查询将有助于找到数据库的PK和FK的链接。

为了更容易，你也可以使用数据库图。

Below query will be helpful in finding the linkage of PK and FK of your Database.
For more easier you can use Database diagram also.

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

这篇关于表因外键而发生冲突;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！