40
create table ImagenesUsuario
{
    idImagen int primary key not null IDENTITY
}

This doesn't work. How can I do this?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Sergio Tapia
  • 40,006
  • 76
  • 183
  • 254
  • 10
    For the future, when you say "This doesn't work" consider explain what you mean. Does it silently fail in some way? Do you get an error, and if so what is the error? Does your computer spontaneously combust? Does it fail when you create the table or insert rows into the table? – Adam Batkin Aug 31 '09 at 00:54
  • 1
    you need to replace the `{` and `}` with `(` and `)` –  Mar 22 '18 at 07:26
  • @a_horse_with_no_name : I think I saw you in the desert a while back; not sure, though ;). – MSIS Mar 13 '22 at 00:20

3 Answers3

77

Simple change to syntax is all that is needed:

 create table ImagenesUsuario (
   idImagen int not null identity(1,1) primary key
 )

By explicitly using the "constraint" keyword, you can give the primary key constraint a particular name rather than depending on SQL Server to auto-assign a name:

 create table ImagenesUsuario (
   idImagen int not null identity(1,1) constraint pk_ImagenesUsario primary key
 )

Add the "CLUSTERED" keyword if that makes the most sense based on your use of the table (i.e., the balance of searches for a particular idImagen and amount of writing outweighs the benefits of clustering the table by some other index).

richardtallent
  • 34,724
  • 14
  • 83
  • 123
  • https://www.eversql.com/sql-syntax-check-validator report wrong syntax. This is a wrong answer. – ABCD Jun 26 '18 at 10:36
  • Hi @SmallChess -- that tool does indicate an error, but it is validating against the MySQL "dialect" of SQL, and the question posed here is for Microsoft SQL Server. Just in case, I checked my answer again in an Azure-hosted MSSQL database, and it is working properly. – richardtallent Jul 31 '18 at 00:54
  • How do you add the `IDENTITY(1,1)` to an existing table? – Pathros Nov 17 '21 at 04:58
  • @Pathros, that's a whole other question, unfortunately. The quick answer is you can't. The slightly longer answer is that you can by creating a *new* identity column, and through a combination of IDENTITY INSERT settings, renaming, yada yada, you can work through replacing the id column. I recommend searching for that separately, it's a common question and comments here don't provide the space to give an adequate response. – richardtallent Dec 07 '21 at 05:00
9

This is similar to the scripts we generate on our team. Create the table first, then apply pk/fk and other constraints.

CREATE TABLE [dbo].[ImagenesUsuario] (
    [idImagen] [int] IDENTITY (1, 1) NOT NULL
)

ALTER TABLE [dbo].[ImagenesUsuario] ADD 
    CONSTRAINT [PK_ImagenesUsuario] PRIMARY KEY  CLUSTERED 
    (
        [idImagen]
    )  ON [PRIMARY]
Mayo
  • 10,544
  • 6
  • 45
  • 90
  • 2
    Is there any reason why you avoid creating the primary key in the create table statement? – Svetlozar Angelov Aug 31 '09 at 06:15
  • 3
    I'm assuming we originally mimicked scripts produced by tools like Enterprise Manager. It does convey that you are creating a primary key constraint (as opposed to a column property). It also allows you to name the constraint. In the end, both ways work so I guess it's a matter of preference on which one is easier to read/maintain. – Mayo Aug 31 '09 at 13:26
5

If you're using T-SQL, the only thing wrong with your code is that you used braces {} instead of parentheses ().

PS: Both IDENTITY and PRIMARY KEY imply NOT NULL, so you can omit that if you wish.

woot
  • 7,406
  • 2
  • 36
  • 55
Joe
  • 51
  • 1
  • 1