HowTo check Primary Keys in a table

Posted on 2009-02-24
Last Modified: 2012-05-06
I need to change the primary key of a table in a script in Sybase 11.

Before I change it I need to make sure that it hasn't already been changed. How do I check to see if the primary key has already been changed

If Not Exists (Select 0 IF 'TableName' has primary key of (col1,col2)) Then
    //Perform Change
End If

How would the psuedo Select statement really look?
Question by:rufassa
    1 Comment
    LVL 19

    Accepted Solution

    The best way to do this is to take a stroll through the system tables.

    First, you have to get the object id from the primary key.  You can do this by name using the object_id() function.

    Next, you have to determine what columns are in the primary key.  Since you have the column names, e.g. col1 and col2, you can do something like... (see the snippet)  I have not tested the code below but it should be pretty close.

    Now that does not tell you anything about the order in which the columns appear in the key,  If you are changing just the order, you have a bigger problem.  It is possible but I am not going to deal with that mess unless you really need it.


    IF 2 = (SELECT COUNT(*)
              FROM syscolumns
             WHERE name IN ('col1', 'col2')
               AND id = object_id('PK_NAME_HERE')
         ...... blah blah blah ......

    Open in new window


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    Digital marketing agencies have encountered both the opportunities and difficulties that emerge from working with a wide-ranging organizations.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now