alter indentity primary key

-- Create products table.

CREATE
TABLE
products

(
id
int
IDENTITY
PRIMARY
KEY
,
product
varchar
(
40))

GO

-- Inserting values into
products table.

INSERT
INTO
products

(
product
)
VALUES
(
'screwdriver'
)

INSERT
INTO
products

(
product
)
VALUES
(
'hammer'
)

INSERT
INTO
products

(
product
)
VALUES
(
'saw'
)

INSERT
INTO
products

(
product
)
VALUES
(
'shovel'
)

GO

 

 

-- Create a gap in the identity
values.

DELETE
products

WHERE
product
=
'saw'

GO

 

SELECT
*

FROM
products

GO

 

-- Attempt to insert an
explicit ID value of 3;

-- should return a warning.

INSERT
INTO
products

(
id
,
product
)
VALUES
(
3,
'garden shovel'
)

GO

-- SET IDENTITY_INSERT to ON.

SET
IDENTITY_INSERT
products
ON

GO

 

-- Attempt to insert an
explicit ID value of 3

INSERT
INTO
products

(
id
,
product
)
VALUES
(
3,
'garden shovel'
)

GO

 

SELECT
*

FROM
products

GO

-- Drop products table.

DROP
TABLE
products

GO