SQL SERVER – Find Primary Key Using SQL Server Management Studio

Imran Mohammed is great SQL Expert and always eager to help community members. He enjoys answering question and solving problems of other community fellows. His answers are always detailed and trustworthy. Today we will see interesting question from Prasant and excellent answer from Imran Mohammed.

Question from Prasant:

Hi,

I want to drop the primary key on one table but i cannot know which constraint is there. Is there a way to drop the primary key without specifying constraint.

The basic idea of doing this is :

I have one table with 4 columns e.g.

1. SrNo
2. NodeID
3. EnrollmentNo
4. FingerNo

Now the two columns already have Primary key NodeID and EnrollmentNo now i want to add another primary key FingerNo as well.

Thanks,
Prashant Hirapara

Answer from Imran Mohammed:

@ Prashant

I am not sure if you want to use GUI to do the same. Running queries is always good… but this can be done very easily using Enterprise Manager (SQL Server 2000 ) or SSMS ( SQL Server 2005)

1. SQL Server 2000

Open Enterprise manager -> expand server -> expand database -> expand tables -> on the right side-> right click table name -> design table ->
now you can see all the columns in that tables also you can see some primary key on one or more than one column, if you want to add one more column in the primary key, just do the following,

1. First remove allow null check on that column.
2. Press the control button and select all the column you want to be in your primary ( include previous primary key columns as well)
3. Now right click the select columns, select Set Primary key.
4. On the top, save the table.

Just to check again, refresh database open table – design and see if you can see the primary key includes your new column.

2. SQL Server 2005

Expand Server- Database – tables – keys – right click key name and click modify, do the same process you did in SQL Server 2000, first you will uncheck allow null on that column, and then you will press control button and you will select all the columns you want to have in primary keys and then right click on those selected column, select set primary key.

at the top, click save and check once again if the changes has taken place.

One more thing, if you are using Varchar(max), text, ntext,nvarchar(max), image datatypes, then I dont think you will be able to set primary key, if you have any varchar(max) columns then try to give a fix values to those columns, like varchar(100).

Now your question remains the same, which one is primary key, what is the name of the primary key?

In SQL Server 2005, you can easily detect what is your primary key (under database – tables – keys), because the range color of primary key is different from foreign keys which is gray color, and there can be only one primary key on one table.

In SQL Server 2000 it is difficult to see as primary key and a foreign key.

Try running these script, which will give you all primary key on all tables in the database including column information.

SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY A.TABLE_NAME

Sometimes you will see the primary constraint name repeats, meaning that primary key is a composite key.

So now you have Primary Key constraint names and I told you procedure , you can do like that or what ever procedure you were trying to do earlier, I guess you were trying to drop primary key and then create a new primary key on the table. You can do this as well.

Make sure no users are connected to that database, because when you create a primary key, by default a clustered index is created, clustered actually physically stores the data, meaning the tables on which you are trying to create primary key will be un available for users.

Hope this helps.
Imran.

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

About these ads

11 thoughts on “SQL SERVER – Find Primary Key Using SQL Server Management Studio

  1. You have been a wonderful teacher. I call myself as Pinal Dave’s Student.

    What I do is not even comparable with what you have been doing to help SQL Community. Again Thanks for appreciating my work, I see many other people helping other members on this website, One among them whom I know ( through website) is “Emeka”

    Excellent work Dear.

    Thanks,
    Imran.

  2. Dear Imran,

    how I could add two primary key in sql 2005 ?

    for example I have this table

    ECE_Activities
    —————=
    Act_id int Not Null
    Act_sec_id int Not Null
    Act_name varchar
    Act_min_balance

    I want to “Act_id” and “Act_sec_id” to make both of them primary key.

    thanks,
    Ali Abo Al-rob

  3. how I could add two primary key in sql 2005 ?

    for example I have this table

    ECE_Activities
    —————=
    Act_id int Not Null
    Act_sec_id int Not Null
    Act_name varchar
    Act_min_balance

    I want to “Act_id” and “Act_sec_id” to make both of them primary key.

    same problem

  4. Great thanks Pinal Dave!! Your writings are always very useful for me.
    I want to ask, can we find the index columns of the primary key? Can somebody help me please…

  5. I was trying to add primary key to existing table. When I right click after selecting the columns, ‘set primary key’ option is not active. Do you know what’s going on. I am a beginner using SQL server management studio.

    Sana

  6. how I can use join query to fetch the unique data from two or more than two table

    if Table A has column a1,a2,a3,a4,a5

    Table B has column a1,a2,a3,b1,b2

    Table C has column a1,a2,a3,c1,c2

    where a1 is primary key for each table

    I want to use * since number of column may be any number
    in the other table so the value a1,a2,a3 get repeated for every table.how I can get only unique value in my query result a1,a2,a3 should not get repeated…….

  7. This might be useful as well.

    SELECT SS.NAME AS [TABLE_SCHEMA], ST.NAME AS [TABLE_NAME]
    , SKC.NAME AS [CONSTRAINT_NAME], SC.NAME AS [CONSTRAINT_COLUMN_NAME],
    CAST(STY.NAME AS VARCHAR(20)) +’(‘+
    CAST(CASE ST.NAME
    WHEN ‘NVARCHAR’ THEN (SELECT SC.MAX_LENGTH/2)
    ELSE (SELECT SC.MAX_LENGTH)
    END AS VARCHAR(20)) +’)’ AS [DATA_TYPE]
    FROM SYS.KEY_CONSTRAINTS AS SKC
    INNER JOIN SYS.TABLES AS ST
    ON ST.OBJECT_ID = SKC.PARENT_OBJECT_ID
    INNER JOIN SYS.SCHEMAS AS SS
    ON SS.SCHEMA_ID = ST.SCHEMA_ID
    INNER JOIN SYS.INDEX_COLUMNS AS SIC
    ON SIC.OBJECT_ID = ST.OBJECT_ID
    AND SIC.INDEX_ID = SKC.UNIQUE_INDEX_ID
    INNER JOIN SYS.COLUMNS AS SC
    ON SC.OBJECT_ID = ST.OBJECT_ID
    AND SC.COLUMN_ID = SIC.COLUMN_ID
    INNER JOIN SYS.TYPES AS STY
    ON SC.USER_TYPE_ID = STY.USER_TYPE_ID
    ORDER BY TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME;

  8. I have a gridview whose columns are generated dynamically in asp
    The database which i am using is Sql Server 2005
    I want to make the primary key column uneditable
    For that purpose i need to determine the primary key column
    Please any help would be appreciated

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