entityMap|blocks|key|8f680|text|这个查询对我非常有用。它显示没有任何匹配的所有值|type|unstyled|depth|inlineStyleRanges|entityRanges|data|9u61l|select+FK_column+from+FK_table
WHERE+FK_column+NOT+IN
(SELECT+PK_column+from+PK_table)|code-block|syntax|javascript^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|H|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|I|9|@]|A|@]|B|$F|G]]]]

This query was very useful for me. It shows all values that don't have any matches

<pre><code>select FK_column from FK_table
WHERE FK_column NOT IN
(SELECT PK_column from PK_table)
</code></pre>

entityMap|blocks|key|4blb8|text|尝试此解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|b7ln5|您的表中有一个数据项，其关联值在您要将其用作主键表的表中不存在。将表设置为空或将关联的值添加到第二个表中。^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|F|9|@]|A|@]|B|$]]]]

Try this solution:

<blockquote>
 There is a data item in your table whose associated value doesn't exist in the table you want to use it as a primary key table.
 Make your table empty or add the associated value to the second table.
</blockquote>

entityMap|blocks|key|20s8l|text|可以使用ALTER+TABLE+TABLE+TABLE+NOCHECK+...创建外键，这将允许违反外键的数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|290g0|"ALTER+TABLE+TABLE+TABLE+NOCHECK...“添加FK的选项--这个解决方案适用于我。^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|F|9|@]|A|@]|B|$]]]]

It is possible to create the foreign key using ALTER TABLE tablename WITH NOCHECK ..., which will allow data that violates the foreign key.

"ALTER TABLE tablename WITH NOCHECK ..." option to add the FK -- This solution worked for me.

entityMap|blocks|key|d73hu|text|我想，外键表中的列值应该与主键表的列值匹配。如果我们试图在两个表之间创建一个外键约束，其中一列中的值(将成为外键)不同于主键表的列值，那么它将抛出消息。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|60pbr|因此，始终建议您只插入外键列中存在于主键表列中的那些值。|8lssu|例如。如果主表列具有值1、2、3，而在外键列中插入的值不同，则不会执行查询，因为它期望这些值介于1和3之间。^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|G|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|H|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|I|9|@]|A|@]|B|$]]]]

I guess, a column value in a foreign key table should match with the column value of the primary key table. If we are trying to create a foreign key constraint between two tables where the value inside one column(going to be the foreign key) is different from the column value of the primary key table then it will throw the message.

So it is always recommended to insert only those values in the Foreign key column which are present in the Primary key table column. 

For ex. If the Primary table column has values 1, 2, 3 and in Foreign key column the values inserted are different, then the query would not be executed as it expects the values to be between 1 &amp; 3.

entityMap|blocks|key|1v1bs|text|在向表中添加外键之前，请执行以下操作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|66kq7|确保表必须为空，否则列数据应匹配。|ordered-list-item|10m0e|确保它不为空。|56c5t|如果表格包含“不转到设计和更改”，请手动执行此操作。|bjbsp|alter+table表1添加外键(列名)引用表2(列名)|7sbgm|更改表表1更改列名称属性不为空^0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|O|9|@]|A|@]|B|$]]|$2|F|4|G|6|E|8|P|9|@]|A|@]|B|$]]|$2|H|4|I|6|E|8|Q|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|R|9|@]|A|@]|B|$]]|$2|L|4|M|6|7|8|S|9|@]|A|@]|B|$]]]]

Before You add Foreign key to the table, do the following

<ol>
<li>Make sure the table must empty or The column data should match.</li>
<li>Make sure it is not null.</li>
<li>If the table contains do not go to design and change, do it manually.

alter table Table 1 add foreign key (Column Name) references Table 2 (Column Name)

alter table Table 1 alter column Column Name attribute not null</li>
</ol>

entityMap|blocks|key|4246r|text|尝试DELETE当前数据来自tblDomare.PersNR。因为tblDomare.PersNR与中的任何值都不匹配tblBana.BanNR。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data^0|2|6|E|G|X|G|1N|D^^$0|$]|1|@$2|3|4|5|6|7|8|G|9|@$A|H|B|I|C|D]|$A|J|B|K|C|D]|$A|L|B|M|C|D]|$A|N|B|O|C|D]]|E|@]|F|$]]]]

Try <code>DELETE</code> the current datas from <code>tblDomare.PersNR</code> . Because the values in <code>tblDomare.PersNR</code> didn't match with any of the values in <code>tblBana.BanNR</code>.

entityMap|blocks|key|45q45|text|当您在表B中定义引用表A的主键的外键时，这意味着当值在B中时，它必须在A中。这是为了防止对表的不一致修改。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|chblv|在您的示例中，您的表包含：|9h83k|tblDomare与PRIMARY+KEY+(PersNR)：|offset|length|style|CODE|1er06|PersNR+++++%7CfNamn+++++%7CeNamn++++++%7CErfarenhet
-----------%7C----------%7C-----------%7C----------
6811034679+%7C'Bengt'+++%7C'Carlberg'+%7C10
7606091347+%7C'Josefin'+%7C'Backman'++%7C4
8508284163+%7C'Johanna'+%7C'Backman'++%7C1
---------------------------------------------|code-block|syntax|javascript|6381n|tblBana：|8fim9|BanNR
-----
1
2
3
-----|2ddp2|这句话：|3pmsm|ALTER+TABLE+tblDomare
ADD+FOREIGN+KEY+(PersNR)
REFERENCES+tblBana(BanNR);|555g0|中的任何一行tblDomarewith+keyPersNR表中必须有对应关系tblBana在关键点上BanNR。您的错误是因为您在tblDomare中没有通信tblBana。|1lun9|2个解决方案要解决您的问题，请执行以下操作：|9qajs|在中添加行tblBana在(6811034679，7606091347，8508284163)中使用BanNR|fpubg|或删除中的所有行tblDomare中没有通信的tblBana(但您的表会是空的)|8dr8n|一般建议：在填充表之前，您应该具有外键约束。这里使用外键是为了防止表的用户向表中填充不一致的内容。^0|0|0|A|K|0|0|0|0|0|0|6|9|N|6|12|7|1E|5|1T|9|27|7|0|0|5|7|0|8|9|N|7|0^^$0|$]|1|@$2|3|4|5|6|7|8|17|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|18|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|19|9|@$G|1A|H|1B|I|J]]|A|@]|B|$]]|$2|K|4|L|6|M|8|1C|9|@]|A|@]|B|$N|O]]|$2|P|4|Q|6|7|8|1D|9|@]|A|@]|B|$]]|$2|R|4|S|6|M|8|1E|9|@]|A|@]|B|$N|O]]|$2|T|4|U|6|7|8|1F|9|@]|A|@]|B|$]]|$2|V|4|W|6|M|8|1G|9|@]|A|@]|B|$N|O]]|$2|X|4|Y|6|7|8|1H|9|@$G|1I|H|1J|I|J]|$G|1K|H|1L|I|J]|$G|1M|H|1N|I|J]|$G|1O|H|1P|I|J]|$G|1Q|H|1R|I|J]|$G|1S|H|1T|I|J]]|A|@]|B|$]]|$2|Z|4|10|6|7|8|1U|9|@]|A|@]|B|$]]|$2|11|4|12|6|7|8|1V|9|@$G|1W|H|1X|I|J]]|A|@]|B|$]]|$2|13|4|14|6|7|8|1Y|9|@$G|1Z|H|20|I|J]|$G|21|H|22|I|J]]|A|@]|B|$]]|$2|15|4|16|6|7|8|23|9|@]|A|@]|B|$]]]]

When you define a Foreign Key in table B referencing the Primary Key of table A it means that when a value is in B, it must be in A. This is to prevent unconsistent modifications to the tables.
In your example, your tables contain:
tblDomare with <code>PRIMARY KEY (PersNR)</code>:
<pre><code>PersNR |fNamn |eNamn |Erfarenhet
-----------|----------|-----------|----------
6811034679 |'Bengt' |'Carlberg' |10
7606091347 |'Josefin' |'Backman' |4
8508284163 |'Johanna' |'Backman' |1
---------------------------------------------
</code></pre>
tblBana:
<pre><code>BanNR
-----
1
2
3
-----
</code></pre>
This statement:
<pre><code>ALTER TABLE tblDomare
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);
</code></pre>
says that any line in <code>tblDomare</code> with key <code>PersNR</code> must have a correspondence in table <code>tblBana</code> on key <code>BanNR</code>. Your error is because you have lines inserted in <code>tblDomare</code> with no correspondence in <code>tblBana</code>.
2 solutions to fix your issue:
<ul>
<li>either add lines in <code>tblBana</code> with BanNR in (6811034679, 7606091347, 8508284163)</li>
<li>or remove all lines in <code>tblDomare</code> that have no correspondence in <code>tblBana</code> (but your table would be empty)</li>
</ul>
General advice: you should have the Foreign Key constraint before populating the tables. Foreign keys are here to prevent the user of the table from filling the tables with inconsistencies.

entityMap|blocks|key|2leu1|text|我也遇到了这个错误，因为Smutje提到，请确保您的基外键表的外键列中没有不在您的引用表中的值，即(基外键表中的每个值(作为外键的列的值也必须在您的引用表列中))最好先清空您的基外键表，然后再设置外键|type|unstyled|depth|inlineStyleRanges|entityRanges|data^0^^$0|$]|1|@$2|3|4|5|6|7|8|C|9|@]|A|@]|B|$]]]]

i had this error too
as Smutje reffered make sure that you have not a value in foreign key column of your base foreign key table that is not in your reference table i.e(every value in your base foreign key table(value of a column that is foreign key) must also be in your reference table column)
its good to empty your base foreign key table first then set foreign keys

entityMap|blocks|key|blmh1|text|Smutje是正确的，查德HedgeCock提供了一个很好的门外汉的例子。我想以查德的例子为基础，提供一种查找/删除这些记录的方法。我们将使用Customer作为父对象，Order作为子对象。CustomerId是公共字段。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4oaei|select+*+from+Order+Child+
left+join+Customer+Parent+on+Child.CustomerId+=+Parent.CustomerId
where+Parent.CustomerId+is+null|code-block|syntax|javascript|qojk|如果你正在阅读这篇文章...你会得到结果的。这些都是孤儿院的孩子。选择*from+Order+Child+left+join+Customer+Parent+on+Child.CustomerId+=+Parent.CustomerId，其中Parent.CustomerId为null。请注意右下角的行数。|8o155|去验证你想要删除这些行的人！|5h8c0|begin+tran+
delete+Order
from+Order+Child+
left+join+Customer+Parent+on+Child.CustomerId+=+Parent.CustomerId
where+Parent.CustomerId+is+null|3apvj|运行第一位。检查行数=您所期望的行数|a804v|提交事务|45umj|commit+tran|6v2ah|小心点。有人草率的编程让你陷入了这样的困境。在删除孤立文件之前，请确保您了解原因。也许父级需要恢复。^0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|V|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|W|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|X|9|@]|A|@]|B|$]]|$2|J|4|K|6|7|8|Y|9|@]|A|@]|B|$]]|$2|L|4|M|6|E|8|Z|9|@]|A|@]|B|$F|G]]|$2|N|4|O|6|7|8|10|9|@]|A|@]|B|$]]|$2|P|4|Q|6|7|8|11|9|@]|A|@]|B|$]]|$2|R|4|S|6|E|8|12|9|@]|A|@]|B|$F|G]]|$2|T|4|U|6|7|8|13|9|@]|A|@]|B|$]]]]

Smutje is correct and Chad HedgeCock offered a great layman's example. 
Id like to build on Chad's example by offering a way to find/delete those records. 
We will use Customer as the Parent and Order as the child. CustomerId is the common field. 

<pre><code>select * from Order Child 
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null 
</code></pre>

if you are reading this thread... you will get results. These are orphaned children. select * from Order Child 
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null Note the row count in the bottom right. 

Go verify w/ whomever you need to that you are going to delete these rows!

<pre><code>begin tran 
delete Order
from Order Child 
left join Customer Parent on Child.CustomerId = Parent.CustomerId
where Parent.CustomerId is null 
</code></pre>

Run the first bit. 
Check that row count = what you expected 

commit the tran 

<pre><code>commit tran 
</code></pre>

Be careful. Someone's sloppy programming got you into this mess. Make sure you understand the why before you delete the orphans. Maybe the parent needs to be restored.

entityMap|blocks|key|5ucbp|text|您输入的表(Tbldomare)数据与分配给主键表的数据不匹配。在tbldomare之间编写并添加此单词(使用nocheck)，然后执行您的代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2g3a5|例如，您输入了一个表tbldomar此数据|66eqr|INSERT+INTO+tblDomare+(PersNR,fNamn,eNamn,Erfarenhet)
Values+(6811034679,'Bengt','Carlberg',10);|code-block|syntax|javascript|3ss8s|然后你给它分配了一个foreign+key仅接受的表1,2,3。|offset|length|style|CODE|3hte0|您有两种解决方案，一种是删除输入的数据，然后执行代码。另一种方法是写下这个单词(用nocheck)，把它放在你的表名中间，然后像这样添加|4tes6|ALTER+TABLE++tblDomare+with+nocheck
ADD+FOREIGN+KEY+(PersNR)
REFERENCES+tblBana(BanNR);^0|0|0|0|A|B|Q|5|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|T|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|U|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|V|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|W|9|@$L|X|M|Y|N|O]|$L|Z|M|10|N|O]]|A|@]|B|$]]|$2|P|4|Q|6|7|8|11|9|@]|A|@]|B|$]]|$2|R|4|S|6|G|8|12|9|@]|A|@]|B|$H|I]]]]

the data you have entered a table(tbldomare) aren't match a data you have assigned primary key table. write between tbldomare and add this word (with nocheck) then execute your code.

for example you entered a table tbldomar this data

<pre><code>INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);
</code></pre>

and you assigned a <code>foreign key</code> table to accept only <code>1,2,3</code>.

you have two solutions one is delete the data you have entered a table then execute the code. another is write this word (with nocheck) put it between your table name and add
like this

<pre><code>ALTER TABLE tblDomare with nocheck
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);
</code></pre>

entityMap|blocks|key|cdvce|text|这发生在我身上，因为我正在设计我的数据库，我注意到我在主表上更改了我的种子，现在关系表在主表上没有外键。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|84u7u|所以我需要截断这两个表，现在它可以工作了！^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|F|9|@]|A|@]|B|$]]]]

This happens to me, since I am designing my database, I notice that I change my seed on my main table, now the relational table has no foreign key on the main table. 

So I need to truncate both tables, and it now works!

entityMap|blocks|key|b9flq|text|您应该查看表中的行是否包含任何数据。如果为"yes“，则应截断表，否则可以使它们在以下位置具有相同数量的数据tblDomare.PersNR至tblBana.BanNR反之亦然。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data^0|1I|G|1Z|D^^$0|$]|1|@$2|3|4|5|6|7|8|G|9|@$A|H|B|I|C|D]|$A|J|B|K|C|D]]|E|@]|F|$]]]]

You should see if your tables has any data on the rows. If "yes" then you should truncate the table(s) or else you can make them to have the same number of data at <code>tblDomare.PersNR</code> to <code>tblBana.BanNR</code> and vise-verse.

entityMap|blocks|key|2847i|text|在我的场景中，使用EF，在尝试在现有数据上创建这个新的外键时，我错误地试图在创建外键之后填充数据(建立链接)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|7m9se|修复方法是在创建外键之前填充数据，因为它会检查所有数据以查看链接是否确实有效。所以如果你还没有填充它，它就不可能工作。^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|E|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|F|9|@]|A|@]|B|$]]]]

In my scenario, using EF, upon trying to create this new Foreign Key on existing data, I was wrongly trying to populate the data (make the links) AFTER creating the foreign key.

The fix is to populate your data before creating the foreign key since it checks all of them to see if the links are indeed valid. So it couldn't possibly work if you haven't populated it yet.

entityMap|0|type|IMAGE|mutability|IMMUTABLE|data|imageUrl|https://ask.qcloudimg.com/http-save/actteam/d3f54b27e5c1450be3ca846f4f1aaac1.png|imageAlt|1|https://ask.qcloudimg.com/http-save/actteam/64b1d6ee255250c0ec8615453cf77064.png|blocks|key|b3d64|text|我在我的项目中遇到了一些问题。|unstyled|depth|inlineStyleRanges|entityRanges|3btt3|?​|atomic|offset|length|837t6|子表中没有记录Id等于1和11|9e1gh|?|5m4tn|我插入了交易_项目_第三_聚会_Id等于1和11的PO表，然后我可以创建FK^0|0|0|1|0|0|0|0|1|1|0^^$0|$1|$2|3|4|5|6|$7|8|9|-4]]|A|$2|3|4|5|6|$7|B|9|-4]]]|C|@$D|E|F|G|2|H|I|W|J|@]|K|@]|6|$]]|$D|L|F|M|2|N|I|X|J|@]|K|@$O|Y|P|Z|D|10]]|6|$]]|$D|Q|F|R|2|H|I|11|J|@]|K|@]|6|$]]|$D|S|F|T|2|N|I|12|J|@]|K|@$O|13|P|14|D|15]]|6|$]]|$D|U|F|V|2|H|I|16|J|@]|K|@]|6|$]]]]

I encounter some issue in my project.

<a href="https://i.stack.imgur.com/gN6US.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gN6US.png" alt="enter image description here"></a>

In child table, there isn't any record Id equals 1 and 11

<a href="https://i.stack.imgur.com/GVrU3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GVrU3.png" alt="mage "></a>

I inserted DEAL_ITEM_THIRD_PARTY_PO table which Id equals 1 and 11 then I can create FK

entityMap|blocks|key|brobk|text|请先从该表中删除数据，然后重新运行迁移。你会成功的|type|unstyled|depth|inlineStyleRanges|entityRanges|data^0^^$0|$]|1|@$2|3|4|5|6|7|8|C|9|@]|A|@]|B|$]]]]

Please first delete data from that table and then run the migration again. You will get success

entityMap|blocks|key|4r5aa|text|仅供参考，如果您做了所有的数据引用检查，并且没有发现错误的data...apparently，那么在两个表和字段之间创建外键约束是不可能的，因为这些字段是两个表中的主键！不要问我是怎么知道的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data^0^^$0|$]|1|@$2|3|4|5|6|7|8|C|9|@]|A|@]|B|$]]]]

and just FYI, in case you do all of your data reference checks and find no bad data...apparently it is not possible to create a foreign key constraint between two tables and fields where those fields are the primary key in both tables! Do not ask me how I know this.

Why does add a foreign key to the <code>tblDomare</code> table result in this error?
<blockquote>
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint &quot;FK__tblDomare__PersN__5F7E2DAC&quot;. The conflict occurred in database &quot;almu0004&quot;, table &quot;dbo.tblBana&quot;, column 'BanNR'.
</blockquote>
Code
<pre><code>CREATE TABLE tblDomare
(PersNR VARCHAR (15) NOT NULL,
fNamn VARCHAR (15) NOT NULL,
eNamn VARCHAR (20) NOT NULL,
Erfarenhet VARCHAR (5),
PRIMARY KEY (PersNR));

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (7606091347,'Josefin','Backman',4);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (8508284163,'Johanna','Backman',1);

CREATE TABLE tblBana
(BanNR VARCHAR (15) NOT NULL,
PRIMARY KEY (BanNR));

INSERT INTO tblBana (BanNR)
Values (1);

INSERT INTO tblBana (BanNR)
Values (2);

INSERT INTO tblBana (BanNR)
Values (3);

ALTER TABLE tblDomare
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);
</code></pre>

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

数据库

 在尝试将外键添加到我的tblDomare表；我在这里做错了什么？ CREATE TABLE tblDomare(PersNR VARCHAR (15) NOT NULL,fNamn VARCHAR (15) NOT NULL,eNamn VARCHAR (20) NOT NULL,Erfarenhet VARCHAR (5),PRIMARY KEY (PersNR));INSERT INTO t

问ALTER TABLE语句与FOREIGN KEY约束冲突
EN

回答 16

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ALTER TABLE语句与FOREIGN KEY约束冲突EN

回答 16

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ALTER TABLE语句与FOREIGN KEY约束冲突
EN