System Generated Primary Key
-
I'm pretty new to SQL and SQL server.
When creating a new table, if I'm using integers as the primary key, is it possible to make the primary key column system generated? In other words, when I insert data into the table, how do I make it so that I don't have to specify the number for the primary key column—so that it automatically assigns the next highest available integer every time I use INSERT INTO?
-
yes...the IDENTITY property auto-increments the enxt value for your int column...technically it doesn't have to be your primary key, but it is very common for them to be the same;
here's a basic example:
CREATE TABLE EXAMPLE (
EXAMPLEID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
EXAMPLETEXT varchar(100) )
the (1,1) is the StartingValue, and the IncrementBy value.
SQL server is smart enough that if you manually insert a value greater than the current Identity value, it calculates the next value above that:
SET IDENTITY_INSERT EXAMPLE ONINSERT INTO EXAMPLE(EXAMPLEID,EXAMPLETEXT) VALUES (99,'ninety-nine')
SET IDENTITY_INSERT EXAMPLE OFF
INSERT INTO EXAMPLE(EXAMPLETEXT) VALUES ('This is one hundred, not one or two')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks! That's exactly what I was looking for.
-
Okay, I have another question. Supposing I already created a table, how do I go back and make the primary key work that way? I tried the following code, and it didn't work. What am I doing wrong?
ALTER TABLE tableNameALTER COLUMN columnName INT IDENTITY(1,1) PRIMARY KEY
It gives me the error message: (Incorrect syntax near the keyword 'Identity').
-
identity property is one of those items that needs to be done at table creation. to add it later, you can use the GUI to add it...it makes a temp table with the new correct structure, migrates all the foreign keys, the data, then finally drops the original table and renames the temp;
here's the change script that was generated on the simple example i showed you:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_EXAMPLE
(
EXAMPLEID int NOT NULL IDENTITY (1, 1),
EXAMPLETEXT varchar(100) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_EXAMPLE ON
GO
IF EXISTS(SELECT * FROM dbo.EXAMPLE)
EXEC('INSERT INTO dbo.Tmp_EXAMPLE (EXAMPLEID, EXAMPLETEXT)
SELECT EXAMPLEID, EXAMPLETEXT FROM dbo.EXAMPLE WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_EXAMPLE OFF
GO
DROP TABLE dbo.EXAMPLE
GO
EXECUTE sp_rename N'dbo.Tmp_EXAMPLE', N'EXAMPLE', 'OBJECT'
GO
ALTER TABLE dbo.EXAMPLE ADD CONSTRAINT
PK__EXAMPLE__19DFD96B PRIMARY KEY CLUSTERED
(
EXAMPLEID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply