locked
Autonumber Primary Key RRS feed

  • Question

  • Good afternoon and thanks in advance for your help. 

    How do I assign the autonumber property to my primary key? 

    Tuesday, January 11, 2011 9:23 PM

Answers

  • In SQL Server set the Identity property for the int (or bigint) field.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Chirag Shah Wednesday, January 12, 2011 1:38 AM
    • Edited by Naomi NEditor Wednesday, January 12, 2011 1:53 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, January 18, 2011 10:52 AM
    Wednesday, January 12, 2011 1:27 AM
    Answerer

All replies

  • In what kind of database?

    SQL Server, Access, Oracle, etc?

    I could guess you have an Access database, as you used the term "autonumber", but answers will vary by database type, and in Access, version.

     

    • Edited by pvdg42 Wednesday, January 12, 2011 12:00 AM typo
    Tuesday, January 11, 2011 11:59 PM
  • In SQL Server set the Identity property for the int (or bigint) field.

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Chirag Shah Wednesday, January 12, 2011 1:38 AM
    • Edited by Naomi NEditor Wednesday, January 12, 2011 1:53 AM
    • Marked as answer by Alex Feng (SQL) Tuesday, January 18, 2011 10:52 AM
    Wednesday, January 12, 2011 1:27 AM
    Answerer
  • Since you asked this question in SQL Server forum, I am assuming your question is pertaining to SQL Server

    to add into Naomi's reply

    See IDENTITY prorprety in SQL Server books online

    http://msdn.microsoft.com/en-us/library/ms186775.aspx

    e.g.

    CREATE TABLE Person
    (
     id_num int IDENTITY(1,1) PRIMARY KEY,
     fname varchar (20),
     minit char(1),
     lname varchar(30)
    )
    
    
    
    Wednesday, January 12, 2011 1:44 AM
  • SQL Server 2008 R2 - My experience is with Access... 
    Wednesday, January 12, 2011 7:02 PM
  • Thanks for your feedback. 

     

    Identity Specification = YEs

    (Is Identity) = Yes

    Identity Increment = 1

    Identity Seed = 1

     

    Does the above look correct? 

    Wednesday, January 12, 2011 7:09 PM
  • Yes, that is correct as you are doing in SQL Server management studio table designer.

     

    Wednesday, January 12, 2011 8:01 PM
  • In that case, Naom has answered your question.
    Wednesday, January 12, 2011 10:38 PM
  • Also please check out DBCC CHECKIDENT  command

    BOL says

    A. Reset the current identity value, if needed

    This example resets the current identity value, if needed, of the jobs table.

    USE pubs
    GO
    DBCC CHECKIDENT (jobs)
    GO
    
    B. Report the current identity value

    This example reports the current identity value in the jobs table, and does not correct the identity value, if incorrect.

    USE pubs
    GO
    DBCC CHECKIDENT (jobs, NORESEED)
    GO
    
    C. Force the current identity value to 30

    This example forces the current identity value in the jobs table to a value of 30.

    USE pubs
    GO
    DBCC CHECKIDENT (jobs, RESEED, 30)
    GO
    

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 13, 2011 6:09 AM
  • How ca I reseed automatically an IdField of my table every time a new year begins?
    Tuesday, October 11, 2011 3:52 PM
  • Are you clearing the previous data? If so, it may be part of the SQL Server Job that run to truncate the data and re-set identity.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, October 12, 2011 12:22 AM
    Answerer