System Generated Primary Key

  • I'm pretty new to SQL and SQL server.

    When creating a new table, if I'm using integers as the primary key, is it possible to make the primary key column system generated? In other words, when I insert data into the table, how do I make it so that I don't have to specify the number for the primary key column—so that it automatically assigns the next highest available integer every time I use INSERT INTO?

  • yes...the IDENTITY property auto-increments the enxt value for your int column...technically it doesn't have to be your primary key, but it is very common for them to be the same;

    here's a basic example:

    CREATE TABLE EXAMPLE (

    EXAMPLEID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    EXAMPLETEXT varchar(100) )

    the (1,1) is the StartingValue, and the IncrementBy value.

    SQL server is smart enough that if you manually insert a value greater than the current Identity value, it calculates the next value above that:

    SET IDENTITY_INSERT EXAMPLE ON

    INSERT INTO EXAMPLE(EXAMPLEID,EXAMPLETEXT) VALUES (99,'ninety-nine')

    SET IDENTITY_INSERT EXAMPLE OFF

    INSERT INTO EXAMPLE(EXAMPLETEXT) VALUES ('This is one hundred, not one or two')

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks! That's exactly what I was looking for.

  • Okay, I have another question. Supposing I already created a table, how do I go back and make the primary key work that way? I tried the following code, and it didn't work. What am I doing wrong?

    ALTER TABLE tableName

    ALTER COLUMN columnName INT IDENTITY(1,1) PRIMARY KEY

    It gives me the error message: (Incorrect syntax near the keyword 'Identity').

  • identity property is one of those items that needs to be done at table creation. to add it later, you can use the GUI to add it...it makes a temp table with the new correct structure, migrates all the foreign keys, the data, then finally drops the original table and renames the temp;

    here's the change script that was generated on the simple example i showed you:

    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

    BEGIN TRANSACTION

    SET QUOTED_IDENTIFIER ON

    SET ARITHABORT ON

    SET NUMERIC_ROUNDABORT OFF

    SET CONCAT_NULL_YIELDS_NULL ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    COMMIT

    BEGIN TRANSACTION

    GO

    CREATE TABLE dbo.Tmp_EXAMPLE

    (

    EXAMPLEID int NOT NULL IDENTITY (1, 1),

    EXAMPLETEXT varchar(100) NULL

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_EXAMPLE ON

    GO

    IF EXISTS(SELECT * FROM dbo.EXAMPLE)

    EXEC('INSERT INTO dbo.Tmp_EXAMPLE (EXAMPLEID, EXAMPLETEXT)

    SELECT EXAMPLEID, EXAMPLETEXT FROM dbo.EXAMPLE WITH (HOLDLOCK TABLOCKX)')

    GO

    SET IDENTITY_INSERT dbo.Tmp_EXAMPLE OFF

    GO

    DROP TABLE dbo.EXAMPLE

    GO

    EXECUTE sp_rename N'dbo.Tmp_EXAMPLE', N'EXAMPLE', 'OBJECT'

    GO

    ALTER TABLE dbo.EXAMPLE ADD CONSTRAINT

    PK__EXAMPLE__19DFD96B PRIMARY KEY CLUSTERED

    (

    EXAMPLEID

    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    COMMIT

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply