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 (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – 2005 – Driver for PHP Community Technology Preview (October 2007)
Next Post
SQLAuthority News – Book Review – Backup & Recovery (Paperback)

Related Posts

10 Comments. Leave new

  • Amit Srivastrava
    October 25, 2007 5:19 am

    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

    Reply
  • 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

    Reply
  • 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

    Reply
  • Thanks :-)

    Reply
  • it is very useful to beginers

    Reply
  • 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…………..

    Reply
  • 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!!!!!!!

    Reply
  • @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.

    Reply
  • [email removed]
    Hi Ajay,

    here you go……………

    CREATE TABLE dbo.card_swipe (
    Card_id INT NOT NULL,
    Swipe_dttm DATETIME NOT NULL,

    CONSTRAINT pk_ card_swipe PRIMARY KEY (Card_id, Swipe_dttm)
    )

    An entry is added to this table each time a card is swiped. We don’t know whether it is an entry or an exit. We do know that the first entry for a card will be an entry, the next will be an exit, the one after that will be an entry and so on.

    I want you to formulate 2 queries:

    Query 1:

    Should list card_id, swipe_dttm and an indicator whether the row is an entry or an exit

    So if data is

    Card_id, swipe_dttm
    1, 2015-05-04 3pm
    1, 2015-05-04 4pm
    2, 2015-05-04 4pm
    1, 2015-05-04 5pm
    2, 2015-05-04 6pm
    1, 2015-05-04 5:30pm
    3, 2015-05-04 4pm

    It should return

    Card_id, swipe_dttm,swipe_type
    1, 2015-05-04 3pm,entry
    1, 2015-05-04 4pm,exit
    2, 2015-05-04 4pm,entry
    1, 2015-05-04 5pm,entry
    2, 2015-05-04 6pm,exit
    1, 2015-05-04 5:30pm,exit
    3, 2015-05-04 4pm,entry

    The 2nd query would display only 1 row per entry (& optional) exit pair and display the entry and optional exit time in the same row. So the result for the above example would be

    Card_id, entry_dttm,exit_dttm
    1, 2015-05-04 3pm,2015-05-04 4pm
    2, 2015-05-04 4pm, 2015-05-04 6pm
    1, 2015-05-04 5pm, 2015-05-04 5:30pm
    3, 2015-05-04 4pm,NULL

    Reply
  • Nick Holmes a Court
    October 10, 2017 1:31 pm

    If you want to create Primary Keys on all tables in SQL Server that don’t have PKs, this SQL will generate all the PK create code.

    I use this on databases i inherit that are missing lots of keys…

    Hope this is of use!

    @nickhac

    select * ,
    ‘ALTER TABLE ‘ + SchemaName + ‘.’ + Tablename + ‘ ADD CONSTRAINT PK_’ + Tablename + ‘ PRIMARY KEY CLUSTERED (‘ +Table_Column_FirstWithIDInName + ‘)’ ScriptToCreatePrimaryKey

    from (
    SELECT
    OBJECT_SCHEMA_NAME(OBJECT_ID) SchemaName, OBJECT_NAME(OBJECT_ID) AS Tablename,
    (SELECT top 1 Column_Name FROM INFORMATION_SCHEMA.COLUMNS where table_name = T.name and column_name like ‘%ID%’ order by Ordinal_Position) Table_Column_FirstWithIDInName
    FROM SYS.Tables T
    WHERE OBJECTPROPERTY(object_id,’TableHasPrimaryKey’) = 0 AND type = ‘U’
    ) TblsWithoutPK

    Reply

Leave a Reply

Menu