SQL Server Unique Composite Key of Two Field with Second Field Auto-Increment

SQL Server Unique Composite Key of Two Field With Second Field Auto-Increment

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);

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.

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 ;;

Note that:

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');

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:

• The row in Invoice_Sequence maintaining the sequence number
• The row in Invoice for the new invoice.

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...

auto increment on composite primary key

I am agree with Mr. Linoff's answer but if you want to store it phisicaly, you can do it within an insert trigger:

Update Your_Table
set SurfaceID =  ( select max(isnull(SurfaceID,0))+1 as max 
                  from Workspaces t
                  where t.AreaID = INSERTED.AreaID )

---

EDIT:*(as an example wanted for how to implement it)

In the question I have seen two table that's why I have wrote the code as above, but following is a sample for what I meant:

Sample table:

CREATE TABLE testTbl 
(
    AreaID INT,
    SurfaceID INT, --we want this to be auto increment per specific AreaID 
    Dsc VARCHAR(60)NOT NULL
)

Trigger:

CREATE TRIGGER TRG
ON testTbl
INSTEAD OF INSERT

AS

DECLARE @sid INT
DECLARE @iid INT
DECLARE @dsc VARCHAR(60)

SELECT @iid=AreaID FROM INSERTED
SELECT @dsc=DSC FROM INSERTED

--check if inserted AreaID exists in table -for setting SurfaceID
IF NOT EXISTS (SELECT * FROM testTbl WHERE AreaID=@iid)
SET @sid=1
ELSE
SET @sid=(  SELECT MAX(T.SurfaceID)+1 
            FROM testTbl T
            WHERE T.AreaID=@Iid
          )

INSERT INTO testTbl (AreaID,SurfaceID,Dsc)
            VALUES  (@iid,@sid,@dsc)

Insert:

INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V1');
INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V2');
INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V3');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V4');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V5');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V6');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V7');
INSERT INTO testTbl(AreaID,Dsc) VALUES (3,'V8');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V9');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V10');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V11');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V12');

Check the values:

SELECT * FROM testTbl

Output:

AreaID  SurfaceID   Dsc
   1       1        V1
   1       2        V2
   1       3        V3
   2       1        V4
   2       2        V5
   2       3        V6
   2       4        V7
   3       1        V8
   4       1        V9
   4       2        V10
   4       3        V11
   4       4        V12

IMPORTANT NOTICE: this trigger does not handle multi row insertion once and it is needed to insert single record once like the example. for handling multi record insertion it needs to change the body of and use row_number

Composite primary key? Or an auto increment primary key with unique composite index?

2 is the best.

It's better than 1 because it has one less index (so less space/cache is taken and the index maintenance is less expensive). A surrogate key (like Id) is generally useless in a junction table.

It may be better than 3 depending on your DBMS: some DBMSes will not allow you to cluster the table on a unique index (just on a primary key)¹. Even on a DBMS that allows it (like SQL Server), I'd go with 1 for being more direct and having simpler syntax.

---

¹ And some will not allow you to reference a unique index (just PRIMARY KEY or UNIQUE constraint) from a FOREIGN KEY, though that's probably not relevant here. If it was, than you'd consider the surrogate key Id.

Auto Increment Composite Primary Key

You shouldn't have the ProjectRef column at all. This violates basic rules of database normalization. If you want your front end to display the ProjectRef then just calculate it from the columns that you have.

Can I create a composite key with an extra character?

Let your table's primary key be a nonsensical auto-increment number with no "meaning" whatsoever. Then, within that table, define two columns: project_number and lesson_number. If the two need to be unique, define a UNIQUE index encompassing the two fields.

Don't(!) create database keys which embed information into them, even if the business does so. If the business needs to refer to strings like PR12, so be it ... create a column to store the appropriate value, or use a one-to-many table. Use indexes as needed to enforce uniqueness.

Notice(!) that I've now described four columns:

1. The auto-increment based "actual" primary key, which contains no information.
2. The project_number column, probably a foreign key to a projects table.
3. Ditto the lesson_number. (With a UNIQUE composite index if needed.)
4. The column (or table) which contains "the string that the business uses."

Over time, business practices do change. And someday you just might .. no, you will... ... encounter a "business-used string" that was incorrectly assigned by the human-beings who do such things! Your database design needs to gracefully handle this. The schema I've described is so-called third-normal form. Do a Google-search on "normal forms" if you haven't already.

mysql two column primary key with auto-increment

if you are using myisam

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

For MyISAM and BDB tables you can
specify AUTO_INCREMENT on a secondary
column in a multiple-column index. In
this case, the generated value for the
AUTO_INCREMENT column is calculated as
MAX(auto_increment_column) + 1 WHERE
prefix=given-prefix. This is useful
when you want to put data into ordered
groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

For your example:

mysql> CREATE TABLE mytable (
    ->     table_id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->     database_id MEDIUMINT NOT NULL,
    ->     other_column CHAR(30) NOT NULL,
    ->     PRIMARY KEY (database_id,table_id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mytable (database_id, other_column) VALUES
    ->     (1,'Foo'),(1,'Bar'),(2,'Baz'),(1,'Bam'),(2,'Zam'),(3,'Zoo');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable ORDER BY database_id,table_id;
+----------+-------------+--------------+
| table_id | database_id | other_column |
+----------+-------------+--------------+
|        1 |           1 | Foo          |
|        2 |           1 | Bar          |
|        3 |           1 | Bam          |
|        1 |           2 | Baz          |
|        2 |           2 | Zam          |
|        1 |           3 | Zoo          |
+----------+-------------+--------------+
6 rows in set (0.00 sec)

---

Related Topics