如何设置/配置表以确保不输入重复记录。(How do I setup/configure a Table to ensure I don't enter duplicate records.

如何设置/配置表以确保不输入重复记录。(How do I setup/configure a Table to ensure I don't enter duplicate records.)

我使用Azure SQL数据库（Microsoft SQL Azure（RTM） - 12.0.2000.8 2016年12月5日21:15:30），我正在尝试配置一个表（CHART），以便我不会得到任何重复的记录。 表结构如下：

Chart_ID Primary Key (Identity Key) Chart_Date Date Chart_Code VarChar (This is a unique value) Chart_Value Int

我正在使用更新或插入新查询。 目前该表有以下记录：

1, 2016-12-10, 12Dec10-00TAM00, 10

如果我Upsert以下记录：

0, 2016-12-10, 12Dec10-00TAM00, 10 (Note I use a zero for the primary key so the database will assign the correct primary key)

数据库将插入一个Chart_Id = 2的新记录，但我不希望它。 对我来说，这是一个重复的记录。

我也做了一些研究，发现了一些关于Unique Constraints的信息。

从我发现我相信我可以做的事情

Use TestDB; Go Alter Table CHART ADD CONSTRAINT AK_UNIQUECHART_CODE UNIQUE (CHART_CODE);

实现无重复记录目标的最佳实践是什么？

感谢您的输入。

I working with an Azure SQL Database ( Microsoft SQL Azure (RTM) - 12.0.2000.8 Dec 5 2016 21:15:30) and I'm trying to configure a table (CHART) so that I will not get any duplicate records. The table structure is as follows:

Chart_ID Primary Key (Identity Key) Chart_Date Date Chart_Code VarChar (This is a unique value) Chart_Value Int

I'm using an Update or Insert if New query. Currently the table has the following record:

1, 2016-12-10, 12Dec10-00TAM00, 10

If I Upsert the following record:

0, 2016-12-10, 12Dec10-00TAM00, 10 (Note I use a zero for the primary key so the database will assign the correct primary key)

The data base will insert an new record with a Chart_Id = 2, but I don't want it to. To me this is a duplicate record.

I also did a little research and found some information on Unique Constraints.

From what I found I believe I could do something like

Use TestDB; Go Alter Table CHART ADD CONSTRAINT AK_UNIQUECHART_CODE UNIQUE (CHART_CODE);

What's the best practice for accomplishing the goal of no duplicate records.

Thanks for your input.

最满意答案

您可以使用某些版本的Sam Saffron的upsert方法 。

粗略草案看起来像这样：

create procedure dbo.Chart_upsert ( @Chart_ID int /* not used in this draft */ , @Chart_Date date , @Chart_Code varchar(32) , @Chart_Value int ) as begin set nocount on; set xact_abort on; begin tran update dbo.Chart with (serializable) set Chart_Value = @Chart_Value where Chart_Code = @Chart_Code if @@rowcount = 0 begin; insert dbo.Chart (Chart_Date, Chart_Code, Chart_Value) values (@Chart_Date, @Chart_Code, @Chart_Value); end; commit tran end;

You could use some version of Sam Saffron's upsert method.

A rough draft would look something like this:

create procedure dbo.Chart_upsert ( @Chart_ID int /* not used in this draft */ , @Chart_Date date , @Chart_Code varchar(32) , @Chart_Value int ) as begin set nocount on; set xact_abort on; begin tran update dbo.Chart with (serializable) set Chart_Value = @Chart_Value where Chart_Code = @Chart_Code if @@rowcount = 0 begin; insert dbo.Chart (Chart_Date, Chart_Code, Chart_Value) values (@Chart_Date, @Chart_Code, @Chart_Value); end; commit tran end;