<bdo id='l41tR'></bdo><ul id='l41tR'></ul>
        <tfoot id='l41tR'></tfoot>
        <legend id='l41tR'><style id='l41tR'><dir id='l41tR'><q id='l41tR'></q></dir></style></legend>
      1. <small id='l41tR'></small><noframes id='l41tR'>

        <i id='l41tR'><tr id='l41tR'><dt id='l41tR'><q id='l41tR'><span id='l41tR'><b id='l41tR'><form id='l41tR'><ins id='l41tR'></ins><ul id='l41tR'></ul><sub id='l41tR'></sub></form><legend id='l41tR'></legend><bdo id='l41tR'><pre id='l41tR'><center id='l41tR'></center></pre></bdo></b><th id='l41tR'></th></span></q></dt></tr></i><div id='l41tR'><tfoot id='l41tR'></tfoot><dl id='l41tR'><fieldset id='l41tR'></fieldset></dl></div>

      2. SQL Server 两字段唯一复合键,第二字段自增

        SQL Server Unique Composite Key of Two Field With Second Field Auto-Increment(SQL Server 两字段唯一复合键,第二字段自增)

          1. <legend id='MAjxb'><style id='MAjxb'><dir id='MAjxb'><q id='MAjxb'></q></dir></style></legend>
          2. <small id='MAjxb'></small><noframes id='MAjxb'>

                <tbody id='MAjxb'></tbody>
            1. <tfoot id='MAjxb'></tfoot>
                <i id='MAjxb'><tr id='MAjxb'><dt id='MAjxb'><q id='MAjxb'><span id='MAjxb'><b id='MAjxb'><form id='MAjxb'><ins id='MAjxb'></ins><ul id='MAjxb'></ul><sub id='MAjxb'></sub></form><legend id='MAjxb'></legend><bdo id='MAjxb'><pre id='MAjxb'><center id='MAjxb'></center></pre></bdo></b><th id='MAjxb'></th></span></q></dt></tr></i><div id='MAjxb'><tfoot id='MAjxb'></tfoot><dl id='MAjxb'><fieldset id='MAjxb'></fieldset></dl></div>
                  <bdo id='MAjxb'></bdo><ul id='MAjxb'></ul>
                  本文介绍了SQL Server 两字段唯一复合键,第二字段自增的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我有以下问题,我想要复合主键,例如:

                  I have the following problem, I want to have Composite Primary Key like:

                  PRIMARY KEY (`base`, `id`);
                  

                  当我插入一个 base 时,id 会根据同一个 base 的前一个 id 自动递增

                  for which when I insert a base the id to be auto-incremented based on the previous id for the same base

                  示例:

                  base   id
                  A      1
                  A      2
                  B      1
                  C      1
                  

                  有没有办法当我说:INSERT INTO table(base) VALUES ('A')插入一个 id 3 的新记录,因为这是 base 'A' 的下一个 id?

                  Is there a way when I say: INSERT INTO table(base) VALUES ('A') to insert a new record with id 3 because that is the next id for base 'A'?

                  结果表应该是:

                  base   id
                  A      1
                  A      2
                  B      1
                  C      1
                  A      3
                  

                  是否可以完全在 DB 上执行此操作,因为如果以编程方式执行它可能会导致赛车状况.

                  Is it possible to do it on the DB exactly since if done programmatically it could cause racing conditions.

                  编辑

                  base 当前代表一家公司,id 代表发票编号.每个公司都应该有自动递增的发票编号,但可能存在两家公司的发票编号相同的情况.登录公司的用户应该能够按这些发票编号进行排序、过滤和搜索.

                  The base currently represents a company, the id represents invoice number. There should be auto-incrementing invoice numbers for each company but there could be cases where two companies have invoices with the same number. Users logged with a company should be able to sort, filter and search by those invoice numbers.

                  推荐答案

                  自从有人发布类似的问题以来,我一直在思考这个问题.第一个问题是 DB 不提供可分区"序列(这将根据不同的键重新启动/记住).第二个是 提供的 SEQUENCE 对象面向快速访问,并且无法回滚(即,您获得间隙).这基本上排除了使用内置实用程序的可能性......这意味着我们必须自己推出.

                  Ever since someone posted a similar question, I've been pondering this. The first problem is that DBs don't provide "partitionable" sequences (that would restart/remember based on different keys). The second is that the SEQUENCE objects that are provided are geared around fast access, and can't be rolled back (ie, you will get gaps). This essentially this rules out using a built-in utility... meaning we have to roll our own.

                  我们首先需要的是一个表来存储我们的序列号.这可以相当简单:

                  The first thing we're going to need is a table to store our sequence numbers. This can be fairly simple:

                  CREATE TABLE Invoice_Sequence (base CHAR(1) PRIMARY KEY CLUSTERED,
                                                 invoiceNumber INTEGER);
                  

                  实际上,base 列应该是对定义您为其开具发票的业务/实体的任何表/ID 的外键引用.在此表中,您希望每个发布实体的条目都是唯一的.

                  In reality the base column should be a foreign-key reference to whatever table/id defines the business(es)/entities you're issuing invoices for. In this table, you want entries to be unique per issued-entity.

                  接下来,您需要一个存储过程,它将接受一个键 (base) 并吐出序列中的下一个数字 (invoiceNumber).所需的密钥集会有所不同(即,某些发票编号必须包含签发年份或完整日期),但这种情况的基本格式如下:

                  Next, you want a stored proc that will take a key (base) and spit out the next number in the sequence (invoiceNumber). The set of keys necessary will vary (ie, some invoice numbers must contain the year or full date of issue), but the base form for this situation is as follows:

                  CREATE PROCEDURE Next_Invoice_Number @baseKey CHAR(1), 
                                                       @invoiceNumber INTEGER OUTPUT 
                  AS MERGE INTO Invoice_Sequence Stored
                                USING (VALUES (@baseKey)) Incoming(base)
                                   ON Incoming.base = Stored.base
                     WHEN MATCHED THEN UPDATE SET Stored.invoiceNumber = Stored.invoiceNumber + 1
                     WHEN NOT MATCHED BY TARGET THEN INSERT (base) VALUES(@baseKey)
                     OUTPUT INSERTED.invoiceNumber ;;
                  

                  注意:

                  1. 必须在序列化事务中运行它
                  2. 事务必须与插入目标(发票)表的事务相同.
                  1. You must run this in a serialized transaction
                  2. The transaction must be the same one that's inserting into the destination (invoice) table.

                  没错,您在开具发票编号时仍会按企业阻止.如果发票编号必须是连续的且没有间隙,则您无法避免这种情况 - 在该行实际提交之前,它可能会回滚,这意味着发票编号不会被发出.

                  That's right, you'll still get blocking per-business when issuing invoice numbers. You can't avoid this if invoice numbers must be sequential, with no gaps - until the row is actually committed, it might be rolled back, meaning that the invoice number wouldn't have been issued.

                  现在,由于您不想记住为条目调用过程,请将其包装在触发器中:

                  Now, since you don't want to have to remember to call the procedure for the entry, wrap it up in a trigger:

                  CREATE TRIGGER Populate_Invoice_Number ON Invoice INSTEAD OF INSERT
                  AS 
                    DECLARE @invoiceNumber INTEGER
                    BEGIN
                      EXEC Next_Invoice_Number Inserted.base, @invoiceNumber OUTPUT
                      INSERT INTO Invoice (base, invoiceNumber) 
                                  VALUES (Inserted.base, @invoiceNumber)
                    END
                  

                  (显然,您有更多列,包括其他应自动填充的列 - 您需要填写它们)
                  ...然后你可以简单地说:

                  (obviously, you have more columns, including others that should be auto-populated - you'll need to fill them in)
                  ...which you can then use by simply saying:

                  INSERT INTO Invoice (base) VALUES('A');
                  

                  那我们做了什么?大多数情况下,所有这些工作都是为了减少事务锁定的行数.在提交这个 INSERT 之前,只有两行被锁定:

                  So what have we done? Mostly, all this work was about shrinking the number of rows locked by a transaction. Until this INSERT is committed, there are only two rows locked:

                  • Invoice_Sequence中维护序号的行
                  • 新发票的 Invoice 行.
                  • The row in Invoice_Sequence maintaining the sequence number
                  • The row in Invoice for the new invoice.

                  特定base 的所有其他行都是免费的——它们可以随意更新或查询(从这种系统中删除信息往往会使会计师感到紧张).您可能需要决定当查询通常包括待处理发票时应该发生什么...

                  All other rows for a particular base are free - they can be updated or queried at will (deleting information out of this kind of system tends to make accountants nervous). You probably need to decide what should happen when queries would normally include the pending invoice...

                  这篇关于SQL Server 两字段唯一复合键,第二字段自增的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  Select n random rows from SQL Server table(从 SQL Server 表中随机选择 n 行)
                  SQL query to select dates between two dates(用于选择两个日期之间的日期的 SQL 查询)
                  How can I delete using INNER JOIN with SQL Server?(如何在 SQL Server 中使用 INNER JOIN 进行删除?)
                  Table Naming Dilemma: Singular vs. Plural Names(表命名困境:单数与复数名称)
                  INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server(INSERT 语句与 FOREIGN KEY 约束冲突 - SQL Server)
                  How ROWNUM works in pagination query?(ROWNUM 如何在分页查询中工作?)
                • <tfoot id='wuGsc'></tfoot>

                  <small id='wuGsc'></small><noframes id='wuGsc'>

                      <legend id='wuGsc'><style id='wuGsc'><dir id='wuGsc'><q id='wuGsc'></q></dir></style></legend>

                        <tbody id='wuGsc'></tbody>
                          <bdo id='wuGsc'></bdo><ul id='wuGsc'></ul>
                          1. <i id='wuGsc'><tr id='wuGsc'><dt id='wuGsc'><q id='wuGsc'><span id='wuGsc'><b id='wuGsc'><form id='wuGsc'><ins id='wuGsc'></ins><ul id='wuGsc'></ul><sub id='wuGsc'></sub></form><legend id='wuGsc'></legend><bdo id='wuGsc'><pre id='wuGsc'><center id='wuGsc'></center></pre></bdo></b><th id='wuGsc'></th></span></q></dt></tr></i><div id='wuGsc'><tfoot id='wuGsc'></tfoot><dl id='wuGsc'><fieldset id='wuGsc'></fieldset></dl></div>