MS Access是否抑制插入上的主键冲突? [英] Does MS Access suppress primary key violations on Inserts?

问题描述

我正在将MS Access数据库重写为SQL Server，并且在Access中发现了一个奇怪的问题，希望有人可以提供帮助.

I am in the process of re-writing an MS Access database to SQL server and have found an strange issue in Access that I am hoping someone can help with.

我有一个表，我们称它为主"，在被索引且不允许重复的帐户上使用主键.看起来很简单，但是在插入数据时出现了我的问题.

I have a table let's call it 'Main' with a Primary Key on the Account that is indexed and doesn't allow for duplicates. Seems simple enough but my issue is occurring when data is getting Inserted.

我的INSERT查询是(为了简洁起见，字段数受到限制)

My INSERT query is (the number of fields have been limited for brevity)

INSERT INTO Main (Account, SentDate, Amount)
SELECT C.Account, C.SentDate, C.Amount
FROM 
    (CALLS C LEFT JOIN Bals B ON C.Account = B.ACCT_ID) 
LEFT JOIN AggAnt A ON C.Account = A.Account

问题是这样的，如果我运行查询的SELECT部分，我将获得2365条记录，但是当我运行INSERT时，我将获得2364条记录.因此，我进行了一些检查，发现一个帐户重复，记录之间的区别是SentDate和Amount.但是Access只插入一条记录，而不抛出任何类型的错误消息或任何东西.查询中没有任何内容显示选择最近的日期，等等.

The issue is this, if I run the SELECT portion of my query I get 2365 records but when I run the INSERT I get 2364 records. So I did some checking and I found one Account is duplicated the difference between the records is the SentDate and the Amount. But Access is inserting only one of the records and not throwing any kind of error message or anything. There is nothing in the query that says select the most recent date, etc.

样本数据:

Account    SentDate   Amount
12345678   8/1/2011   123.00
23456789   8/1/2011   45678.00
34567890   8/1/2011   7850.00
45678912   8/1/2011   635.00
45678912   5/1/2011   982.00
56789123   8/1/2011   2639.00

在示例中，我有一个在运行INSERT时重复的45678912帐户，没有错误，并且获得了2011年8月1日的记录.

In the sample I have one account that is duplicated 45678912 when I run my INSERT, I get no errors and I get the record from 8/1/2011.

为什么Access违反表上的PK时不会抛出错误?在Access中是否有一些怪异选择一个记录而仅跳过另一个记录?

Why is Access not throwing an error when this violates the PK on the table? Is there some quirk in Access to select one record and just skip the other?

这个问题让我很沮丧，所以任何帮助都会很大.

I am totally stumped by this issue so any help would be great.

推荐答案

在HansUp将我指向检查SetWarnings = false的方向之后.我发现它埋藏在我的代码中，这就是为什么没有警告消息表示由于主键冲突而导致未插入记录的原因.

After HansUp pointing me in the direction of checking for SetWarnings = false. I found it buried in my code which is why there was no warning message about the records not being inserted due to primary key violations.

请谨慎注意，以确保您希望禁止显示这些消息.

A word of caution would be to make sure you want these messages suppressed.

这篇关于MS Access是否抑制插入上的主键冲突?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！