none
Auto increment for non-primary key - Is this possible/valid?

    Question

  • I would like to create a table that contains a field whose value should be generated automatically.  That is, I do not want to provide a value but rather let the DB engine (MS SQL Server) in this case generate it for me much like it does w/the Primary Key.

    Here is the table definition (using MS SQL) I would like to use:

    CREATE TABLE [dbo].[VejpInformation] (
      [VejpInfoId]		INT	IDENTITY (1, 1) NOT NULL,
      [Pid]			INT     NOT NULL,
      [Info_ItemNumber]	INT	NOT NULL,
      [Info_Descr]		TEXT    NULL,
      [Info_InitCost]    	TEXT    NULL,
    CONSTRAINT [PK_dbo.VejpInformation] PRIMARY KEY CLUSTERED ([VejpInfoId] ASC),
    CONSTRAINT [VEJPAUDIT_FK_PROJECTS] FOREIGN KEY ([Pid]) REFERENCES [dbo].[Projects] ([ProjID]) ON DELETE CASCADE ON UPDATE CASCADE
    );
    

    The "Info_ItemNumber" Field is one that I would prefer to be auto-generated instead of allowing the User to provide it.  There is no special significance to this field other than it should be auto-generated/incremented.  Each project might have multiple entries in this table (for each different "Info" and I would like for the "Info_ItemNumber" to be unique.  The front-end would not expose the field for the User to edit, rather just provide the value when the Table entries are presented to the User.

    Tuesday, April 01, 2014 8:44 PM

Answers

  • Hello,

    >>Auto increment for non-primary key - Is this possible/valid?

    Yes. There is no requirement that IDENTITY columns be made a primary key.

    However, there is a limit in SQL Server:

    Only one identity column can be created per table.

    It is described here:

    http://msdn.microsoft.com/en-us/library/aa258255%28SQL.80%29.aspx

    Seraching for description for IDENTITY.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 02, 2014 8:35 AM

All replies

  • Hello,

    >>Auto increment for non-primary key - Is this possible/valid?

    Yes. There is no requirement that IDENTITY columns be made a primary key.

    However, there is a limit in SQL Server:

    Only one identity column can be created per table.

    It is described here:

    http://msdn.microsoft.com/en-us/library/aa258255%28SQL.80%29.aspx

    Seraching for description for IDENTITY.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, April 02, 2014 8:35 AM
  • Thanks Fred.  It turns out that I will have to split the field(s) in question to a separate table so I will end up setting the field of interest as the primary key (w/auto-increment).

    However, it would be nice to be able to have multiple auto-increment fields within a table.

    • Marked as answer by Fan Farron Thursday, April 10, 2014 5:04 PM
    • Unmarked as answer by Fan Farron Thursday, April 10, 2014 5:05 PM
    Thursday, April 10, 2014 5:04 PM