Saturday, April 23, 2011

3 Different Methods to Create Primary Keys

Primary key is unique identifier for each record in the database table and creates clustered index on the column on which its defined.

--Cleanup
IF EXISTS ( SELECT OBJECT_ID('constraintTable'))
DROP TABLE constraintTable
GO


CREATE TABLE constraintTable
(ID INT CONSTRAINT ct_ID PRIMARY KEY, Col2 VARCHAR(20))

GO

--Cleanup
IF EXISTS ( SELECT OBJECT_ID('constraintTable'))
DROP TABLE constraintTable
GO


--Primary Key upon table creating Method2
CREATE TABLE constraintTable
(ID INT, Col2 VARCHAR(20) CONSTRAINT ct_ID PRIMARY KEY)

GO

--Cleanup
IF EXISTS(SELECT OBJECT_ID('constraintTable'))
DROP  TABLE constraintTable
GO


--Primary Key upon table creating Method3
CREATE TABLE constraintTable
(ID INT, Col2 VARCHAR(20))

GO

--Primay key doesn't allow NULL values
ALTER TABLE constraintTable
ALTER COLUMN ID INT NOT NULL

GO

ALTER TABLE constraintTable
ADD CONSTRAINT Ct_ID PRIMARY KEY (ID)

GO

Reference: DP (http://dptechnicalblog.blogspot.com/ )

No comments:

Post a Comment