views:

84

answers:

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

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

+1  A: 

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
A: 

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
Is there any reason why you avoid creating the primary key in the create table statement?
Svetlozar Angelov
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