CODEWITHSUNDEEP
sqlsql-servert-sql
Sergio Tapia
Sergio Tapia

Reputation: 41208

Create SQL identity as primary key?

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

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

Upvotes: 41

Views: 128327

Answers (3)

Joe
Joe

Reputation: 51

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.

Upvotes: 5

richardtallent
richardtallent

Reputation: 35404

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).

Upvotes: 79

Mayo
Mayo

Reputation: 10812

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]

Upvotes: 10

Related Questions