1

Is it possible to have a table with PK based on Identity columns?

Lets say I have a table of customers, two fields are important for our case Id which is PK nvarchar(20) and SequenceId which is int identity(1,1). I want to have a stored procedure that will have(among others) a parameter @CustomerType and based on this parameter will generate the value for Id and insert a new row into the table. Something like this:

CREATE PROCEDURE createCustomer
@CustomerType int, 
@GivenName nvarchar(20), 
@Surname nvarchar(20), 
@BirthDate date = null
...
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @prefix nvarchar(1) = N'x'
    IF @CustomerType = 1
    BEGIN
        SET @prefix = N'P'
    END
...
    ELSE
    BEGIN
        SET @prefix = N'x'
    END

    INSERT INTO [dbo].[Customers]
           ([Id],
            [GivenName],[Surname],[BirthDate])
    VALUES
           (@prefix + (SequenceId + 1000000),
        @GivenName,@Surname,@BirthDate);

    SELECT INSERTED.Id AS CustomerId;
END
  • you can have ,what is the problem you are facing – TheGameiswar Apr 27 '15 at 10:21
  • @TheGameiswar I'm getting an Invalid column name 'SequenceId' error. – Jyrkka Apr 27 '15 at 10:24
1

SequenceID is invalid,since you are not specifying from where to get the value.I suggest,create one more temp variable and add ,some thing like below

declare @seqid int

select @seqid =next value for from your sequence
then in place of sequence ,keep this variable

  • But I read that such approach(selecting max value prior to inserting a new row) could result in duplicate values, how you suggest to accomplish this? – Jyrkka Apr 27 '15 at 10:39
  • sequence can never be duplicated,you can try yourself creating a sequence and calling that repeatedly in two different sessions – TheGameiswar Apr 27 '15 at 11:04
  • Couldn't it be accomplished without creating a dedicated sequence object? My SequenceId is just a identity auto-increment column. – Jyrkka Apr 27 '15 at 11:05
4

You never set the SequenceId to anything. It is not possible to set the value of a column during an insert using an expression that references the value of an IDENTITY column on that same table that will be set upon that same insert operation. If you think about it this makes sense because the row doesn't exist until you insert it, so there is no IDENTITY value at the time that the insert begins.

If what you want is to create a column that concatenates a prefix and an IDENTITY value then you should use a computed column, for example:

CREATE TABLE dbo.Customers
( Sequence   int  IDENTITY(1,1) NOT NULL
, Prefix     nchar(1) NOT NULL
, CustomerID AS Prefix + CAST(Sequence + 1000000 as nvarchar(8))
, GivenName  nvarchar(50)
... etc ... 
, CONSTRAINT UN_CustomersSequence UNIQUE (Sequence)
, CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
)

You should be able to make CustomerID in the above table your primary key if you want. Since the Sequence value is already unique (by virtue of being IDENTITY) I would question why you want to make a compound primary key. Are you sure that this is the design you want?

  • 2
    The IDENTITY property does not enforce uniqueness (e.g. reseeding, identity insert) you need a unique constraint or index for that. – Paul White Apr 27 '15 at 13:17
  • @PaulWhite - obviously, but if the IDENTITY is being used in a PK, one would assume that such a precaution will be taken. The OP's question didn't include DDL, so one is left to presume. – Joel Brown Apr 27 '15 at 15:00
  • 2
    If may be 'obvious' to me and you, but your current answer wording is open to misinterpretation: "Since the Sequence value is already unique (by virtue of being IDENTITY)". My original comment was to encourage you to edit your answer to clarify. It is a common misconception, after all. – Paul White Apr 27 '15 at 15:08
  • It doesn't make much sense to me to use a dedicated column to store a prefix, that's why I preferred to put all the logic into a stored procedure instead of computed column. I believe I can use a sequence object in order to accomplish this as @TheGameiswar suggests. But I thought that there is a way to do it without one. – Jyrkka Apr 27 '15 at 16:23
  • 1
    It doesn't make much sense to me to embed a customer type code in a pseudo-randomly generated customer identifier. The conventional wisdom is not to build meaning into identifiers. As rules of thumb go that is a good one in my experience. However, your mileage may vary. – Joel Brown Apr 28 '15 at 10:35
2

Rather than calculate the value in code, consider a computed column instead.

ALTER TABLE dbo.Customers
    DROP COLUMN Id;

ALTER TABLE dbo.Customers
    ADD Id AS (CASE CustomerType WHEN 1 THEN N'P' ELSE N'x' END + CAST((SequenceId + 1000000) AS nvarchar(10)));

Below is an example of the insert proc using this computed column:

CREATE PROCEDURE createCustomer
    @CustomerType int
  , @GivenName nvarchar(20)
  , @Surname nvarchar(20)
  , @BirthDate date = NULL
AS
SET NOCOUNT ON;

INSERT  INTO [dbo].[Customers]
        ( [GivenName]
        , [Surname]
        , [BirthDate]
        )
OUTPUT  inserted.Id
VALUES  ( @GivenName
        , @Surname
        , @BirthDate
        );
GO

EDIT: removed comment about persisting column.

  • This could be an option, but I thought that it's more appropriate to put logic into a stored procedure. – Jyrkka Apr 27 '15 at 16:27
  • @PaulWhite: Are you sure? On my SQL 2014 machine, I get "...NOT NULL constraints require that computed columns be persisted." when trying to add a PK to a computed column. I believe it does need to be PERSISTED NOT NULL (though MS SQL will make it NOT NULL automatically if you mark it as a PK). – Jon of All Trades Apr 27 '15 at 22:34
  • @JonofAllTrades Yes I am sure, for example DECLARE @T table (c1 int NULL, c2 AS ISNULL(c1 * 2, 0) PRIMARY KEY);. The computed column needs to be not nullable by inspection - as distinct from requiring a NOT NULL constraint, which would require PERSISTED. – Paul White Apr 28 '15 at 5:18
  • @PaulWhite, you are right of course. I've got to stop trusting my recollections as my memory isn't what it used to be. – Dan Guzman Apr 28 '15 at 11:26
0

An identity field is meant to be a surrogate key field. The fact that it is the PK assures uniqueness, not necessarily the identity itself. No meaning should ever be assigned the value of a surrogate key. That means you can assume nothing about the associated row, not even the sequence when it was inserted into the table. That is, you should not assume id 1000 was inserted into the table before id 1001.

Any additional meaning must be placed in other fields, such as the prefix field suggested by @Joel Brown. If management really wants to see the prefix and the ID values joined together, then do so in reports. Don't build it into the design. You'll be designing in major headaches for the future.

Also, you can define identity fields to start at 1000000 rather than adding it in every query.

  • Why can't I assume that id 1000 was inserted into the table before id 1001? – Jyrkka Apr 30 '15 at 21:14
  • Because it may actually be a false assumption. Read the documentation for your particular DBMS. I don't think any system guarantees that auto generated values will track insertion sequence. It would take a lot of development effort to make such a guarantee and such a guarantee is not required because you're not supposed to ever make such an assumption. Also read about surrogate keys. – TommCatt May 1 '15 at 18:48
  • I understand that this is a bad practice, but can you think of a situation when the 1001 will be inserted before 1000? – Jyrkka May 5 '15 at 10:19
  • Many DBMSs will cache a small sequence of values, say 101-110, and assign them to a session. Then another set of values, 111-120 to another session. But if the second session writes first, 111 will be the key value. Then the first session writes and 101 is then inserted. This can usually be turned off but it can really speed up a busy system with lots of sessions logged on. Remember that the only essential feature is that the values be unique, not entered into the db sequentially. Don't sacrifice speed for aesthetics. – TommCatt May 5 '15 at 14:51
  • Another "feature" is that if the system should go down in the middle of all this, when it restarts it doesn't know which of the assigned values were used and which remained in cache. So it will assign the next available value which will be 121, possibly leaving one or more gaps of values that were never used. So if you have a value of 109 and another value of 111, you cannot assume that a record with value 110 ever existed. It may have existed and then subsequently deleted, or it may have never existed at all. – TommCatt May 5 '15 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.