SQL SERVER – Three T-SQL Script to Create Primary Keys on Table

I have always enjoyed writing about three topics Constraint and Keys, Backup and Restore and Datetime Functions.

Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.
-- Primary Key Constraint upon Table Created Method 1
USE AdventureWorks
GO
CREATE TABLE ConstraintTable
(ID INT CONSTRAINT Ct_ID PRIMARY KEY, ColSecond INT)
GO
--Clean Up
DROP TABLE ConstraintTable
GO
-- Primary Key Constraint upon Table Created Method 2
USE AdventureWorks
GO
CREATE TABLE ConstraintTable
(ID INT, ColSecond INT,
CONSTRAINT Ct_ID PRIMARY KEY (ID))
GO
--Clean Up
DROP TABLE ConstraintTable
GO
-- Primary Key Constraint after Table Created Method 3
USE AdventureWorks
GO
CREATE TABLE ConstraintTable
(ID INT, ColSecond INT)
GO
-- Primary Key Column must not allow NULL
ALTER TABLE ConstraintTable
ALTER COLUMN ID INT NOT NULL
GO
ALTER TABLE ConstraintTable
ADD CONSTRAINT Ct_ID PRIMARY KEY (ID)
--Clean Up
DROP TABLE ConstraintTable
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

10 thoughts on “SQL SERVER – Three T-SQL Script to Create Primary Keys on Table

  1. Hello Sir,

    I am an aspiring DBA can u pls suggest me a book from where i can learn to administer both SQL Server 2005 and Oracle.

    Thanking you in advance.

    Amit

  2. Hello Sir,

    Wish U Very Very Happy Diwali

    I Am Ashutosh Here Sir I Also Become a MCTS.Net & DBA so please can U Suggest Me Which books i should be read

    Thank You Sir
    Ashutosh Bagul

  3. ur Advice is Valuable…DBA
    ur Advice is Valuable…DBA
    Do let me know presently m working in a bank as a Datavbase Administrator for Sql server 2005 though i dont have very much to do even nothing to explore my skills here …/…. i m trying to move on to devlopement company as soon as possible here i feel like standstill ……meanwhile do u think that i shud carry on with this job look though i m learning ———–indexing,Physical and logical data modeling—–Performance tunning of Queris and Database ,clustring knowledge.Troubleshooting of Queris and Procedures. at my own skills…..m only doing some backup statgies automating backup’s and maintainance plan and Using Dbcc commands replication and mirroring etc…

    Do you think that it ll count as a experience for switching off to next company….

    Please Plea advice me ……

    Vaibhav mathur

  4. hello sir
    I have plan to do some project in inventary model for this I have created my database in sql 2005 as by back-end and c#.net is my front end for this i would like to know difference between the sql scripting language and ordinary database language plz help me…………..

  5. hello pinal sir,

    i want to add a column of NOT NULL type using ALTER TABLE statement
    how can i do it.
    also i want to add this column without default constraint
    waiting for ur reply
    thanks in advance!!!!!!!

  6. @Zubair.

    There are only few situations where you can add a column
    1. With not null contraint
    2. With no default constraint.

    First of all this does not even make sense to do something like this, it will definitely not have any business value.

    Anyways here are these two ways to do it.

    1. Definitely you can add a column to a table , with not null and not default constraint, but the table has to be empty. There has to be no data in the table, this can be done with T-SQL Script.

    2. If you have data in the table and you still want to add a column with not null constraint and with out default constraint. You can add an identity column, unfortunately you cannot add an identity column with T-SQL script , you have to use SSMS.

    If you say, No, I have data already in the table, and still want to add a column to the table with not null, not default constraint, then you decide what values should go into previous records for this new column ???

    Think logically

    Hope this helps,
    IM.

  7. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s