basic sql PK issue

[Acidburn]

Hi guys,

I've started learning SQL after spending several years in the industry. I've got a table defined ...

CREATE TABLE [dbo].[Computer](
	[Id] [int] NOT NULL,
	[Name] [nchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now I don't want anyone having to insert a PK when inserting, I want the DB internally to do this. Is it possible?

[darkagn]

Yes, you need to create the primary key as an identity field. Note that this is only possible for numeric key fields (which you have in this case so that's ok). Simply add the word IDENTITY with the first value and increment values in brackets into your field definition, like so:

CREATE TABLE [dbo].[Computer](
-- this identity will start at 1 and increment by 1 each time...
[Id] [int] NOT NULL IDENTITY(1,1),
[Name] [nchar](10) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

When you do an insert with an identity field, don't specify the field in the statement. The database will automatically determine the next value to be inserted. One problem that this does present is when rows are deleted the value isn't replaced next time. For example, imagine we insert 10 rows into your table, then delete row 7. When we insert the next row, number 11 is inserted, not 7 to fill the gap. You can use the SCOPE_IDENTITY function to determine the last inserted identity in the database, so if done immediately after an insert you can determine which number was inserted.

Hope this helps,
darkagn