May 4, 2009 at 6:17 pm
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?
May 4, 2009 at 6:50 pm
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 ON
INSERT 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
May 4, 2009 at 7:29 pm
Thanks! That's exactly what I was looking for.
May 4, 2009 at 8:06 pm
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 tableName
ALTER COLUMN columnName INT IDENTITY(1,1) PRIMARY KEY
It gives me the error message: (Incorrect syntax near the keyword 'Identity').
May 5, 2009 at 5:24 am
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy