如何防止在sql server中插入重复的id [英] How to prevent insert duplicate id in sql server

问题描述

我有两个表，第一个是serialmaster，第二个是issmaster。当我在serialmaster中添加项目时，serialid是自动生成的，而serialid是在issmaster中添加的数字。



我们添加了5个项目，因此它可以生成serialid为

I have two table first one is serialmaster and second one is issuemaster. When i added item in serialmaster then serialid is auto-generated and serialid is number added in issuemaster.

Let's i added 5 items so it can generated serialid as

serialid  itemname
1           Test1
2           Test2
3           Test3
4           Test4
5           Test5

issmaster表格如下

issuemaster table as below

issueid      serialid
1              1
2              2
3              3
4              4
5              5

但是当我再次在serialmaster中添加2项时

But when i added again 2 items in serialmaster then

serialid  itemname
1           Test1
2           Test2
3           Test3
4           Test4
5           Test5
6           Test6
7           Test7

但是issmaster表如下所示。 br />

But issuemaster table is like below.

issueid  serialid
1          1
2          2
3          3
4          4
5          5
6          1
7          2
8          3
9          4
10         5
11         6
12         7

因此，issmaster发现了重复的条目。那怎么能预防呢？

我可以在SQL Server 2008 R2的存储过程中工作

So issuemaster have duplicate entry found. SO how can prevent it ?
I can work in store procedure of SQL Server 2008 R2

推荐答案

防止SQL表中重复值的最佳方法是创建唯一的密钥使用正确的列...

http://www.w3schools.com/sql/ sql_unique.asp [ ^ ]

这是SQL应该工作的方式，而不是通过在存储过程中编写任何代码...只记得存储过程可以绕过唯一约束不能 - 永远！

The best way to prevent duplicate values in SQL table is to create unique keys using the proper column...
http://www.w3schools.com/sql/sql_unique.asp[^]
This is the way SQL should work and not by writing any code in stored procedure...Just remember that stored procedure can be bypassed where unique constrains can not - ever!

创建此存储过程...您将获得解决方案

Create this Stored procedure ... You will get your Solution

create procedure sp_by_aarif
@item_id int=0,
@item_name varchar(50)= ''
AS
BEGIN
   if exists (select * from yourTableName where item_id=@item_id)
       begin
            Insert into yourTableName values (@item_id,@item_name)
       end

end

注意：这里给你的表名而不是yourTableName



谢谢

AARIF SHAIKH

Note: Here give your Table name rather than "yourTableName"

Thanks
AARIF SHAIKH

INSERT INTO issuemaster
    SELECT
        serialid
    FROM
        serialmaster S
    WHERE
        NOT EXISTS (
            SELECT
                serialId
            FROM
                issuemaster I
            WHERE
                S.serialid = I.serialid)

这篇关于如何防止在sql server中插入重复的id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！