locked
2 Primary Keys RRS feed

  • Question

  • I have a table with a number of columns and one of them is an identity column which is also a primary key and I have another column which is not an identity but I do need it to be unique by itself, kind of like a second primary key. Is there a way to do it? 

    Debra has a question

    Friday, March 14, 2014 4:50 PM

Answers

  • You may need to create a unique constraint on the second column.

    Try the below:

    create table Test_table(Col1 int identity(1,1) primary key, col2 int)
    
    ALTER TABLE Test_table 
    ADD CONSTRAINT UQ_Col2 UNIQUE (Col2); 


    EDIT: Ref:http://technet.microsoft.com/en-us/library/ms190024.aspx


    • Edited by SQLZealots Friday, March 14, 2014 4:55 PM
    • Proposed as answer by Ashish Pandey Friday, March 14, 2014 4:57 PM
    • Marked as answer by Fanny Liu Tuesday, March 25, 2014 1:23 AM
    Friday, March 14, 2014 4:54 PM
  • Add a Unique Key Constraint and make it a NON NULLABLE column.. That should do it... Ideally a primary key is a Unique key without a NULL value..

    You can use,

    ALTER TABLE tblnm
    ADD CONSTRAINT UK_columnname UNIQUE (columnname)

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    • Proposed as answer by Ashish Pandey Friday, March 14, 2014 4:57 PM
    • Marked as answer by Fanny Liu Tuesday, March 25, 2014 1:23 AM
    Friday, March 14, 2014 4:54 PM
  • Also this should make you consider strongly dropping the IDENTITY column and just using the other column as the primary key.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Friday, March 14, 2014 10:22 PM
    • Marked as answer by Fanny Liu Tuesday, March 25, 2014 1:23 AM
    Friday, March 14, 2014 6:18 PM

All replies

  • You may need to create a unique constraint on the second column.

    Try the below:

    create table Test_table(Col1 int identity(1,1) primary key, col2 int)
    
    ALTER TABLE Test_table 
    ADD CONSTRAINT UQ_Col2 UNIQUE (Col2); 


    EDIT: Ref:http://technet.microsoft.com/en-us/library/ms190024.aspx


    • Edited by SQLZealots Friday, March 14, 2014 4:55 PM
    • Proposed as answer by Ashish Pandey Friday, March 14, 2014 4:57 PM
    • Marked as answer by Fanny Liu Tuesday, March 25, 2014 1:23 AM
    Friday, March 14, 2014 4:54 PM
  • Add a Unique Key Constraint and make it a NON NULLABLE column.. That should do it... Ideally a primary key is a Unique key without a NULL value..

    You can use,

    ALTER TABLE tblnm
    ADD CONSTRAINT UK_columnname UNIQUE (columnname)

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    • Proposed as answer by Ashish Pandey Friday, March 14, 2014 4:57 PM
    • Marked as answer by Fanny Liu Tuesday, March 25, 2014 1:23 AM
    Friday, March 14, 2014 4:54 PM
  • In addition to Latheesh Code add NOT NULL to col2. You can't create unique key if there any duplicates but you can create a NOT NULL constraints where you've null values for the columns using Check constraint.

    CREATE TABLE TEST(COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 INT  )

    INSERT INTO TEST(COL2) VALUES(2)
    INSERT INTO TEST(COL2) VALUES(NULL)


    ALTER TABLE Test 
    ADD CONSTRAINT Unique_Col2 UNIQUE(Col2) 

    --Add a NOT NULL constraint with the NOCHECK option. The NOCHECK does not enforce on existing values:

    ALTER TABLE test WITH NOCHECK
    ADD CONSTRAINT COL2_NOTNULL CHECK (col2 IS NOT NULL)


    DROP TABLE TEST


    -Prashanth



    Friday, March 14, 2014 4:58 PM
  • If it should be kind of primary key then it should be NOT NULL and should have unique constraint too like what Prashanth suggested.

    so it would be like

    CREATE TABLE TEST(COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 INT  NOT NULL)
    
    ALTER TABLE Test
    ADD CONSTRAINT UNQ_TEST_COl2 UNIQUE (Col2)


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Friday, March 14, 2014 6:16 PM
  • >> I have a table with a number of columns and one of them is an IDENTITY column [sic] which is also a PRIMARY KEY [soc] and I have another column which is not an IDENTITY but I do need it to be unique by itself, kind of like a second PRIMARY KEY. Is there a way to do it? <<

    IDENTITY is a table property and not a column. It is a proprietary, non-relational feature derived from 1970's Sybase/Unix file models. It counts the number of physical hardware attempts to write to one disk on one machine on one instance of a Microsoft product (not even successes). Since it is not an attribute in a data model, it can not be a key, by definition. 

    If you want an analogy, it is the parking space number on a sequential paper ticket in one particular garage. It does not identify the automobile. We have a VIN number for that. It is a pointer to a physical locator. 

    Dr. Codd invented, then regretted, the PRIMARY KEY concept. He was looking at the old mag tape (later, sequential disk) file systems which have to be in sorted order to be used. SQL was built on them and inherited the concept. 

    The mathematical truth is that all keys are UNIQUE and NOT NULL. You can simply add those two constraints to any subset of columns and get a key. 

    Debra, you are using this forum to get a basic education. Forums are not good for this; you need to read books or take a course. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, March 14, 2014 6:17 PM
  • Also this should make you consider strongly dropping the IDENTITY column and just using the other column as the primary key.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Friday, March 14, 2014 10:22 PM
    • Marked as answer by Fanny Liu Tuesday, March 25, 2014 1:23 AM
    Friday, March 14, 2014 6:18 PM
  • Here is the simplified declarative syntax:

    create table Test_table(
    Col1 int identity(1,1) PRIMARY KEY, 
    col2 int NOT NULL UNIQUE);

    You can only have one PRIMARY KEY but more than one UNIQUE KEYs.

    >consider strongly dropping the IDENTITY column

    Why? We don't have the real DDL so we cannot give such an advice.

    >Dr. Codd invented, then regretted, the PRIMARY KEY concept

    Yes. JOINING/LINKING KEY would be a more suitable name.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Friday, March 14, 2014 9:51 PM
  • >>consider strongly dropping the IDENTITY column

    >Why? We don't have the real DDL so we cannot give such an advice.

    Just "consider".  What purpose does the IDENTITY column serve if there is another column that uniquely identifies the row?  Storage is increased, DML cost is increased, query performance may suffer with a second key.  So there has to be at least some reason. 

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, March 14, 2014 11:33 PM
  • Hi David,

    There is nothing to consider because we don't know what is the second column. Albeit the OP may know the second column so he can consider it.

    If the second column is Email varchar(70) I certainly want to keep the IDENTITY SURROGATE.

    4 byte INT SURROGATE KEY is the best way to JOIN tables.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012





    • Edited by Kalman Toth Friday, March 14, 2014 11:59 PM
    Friday, March 14, 2014 11:53 PM
  • Yep.  The other key is very large.  The other key changes.  All my other tables have IDENTITY keys and it's a convention in my application.  The other key comes from a different system. ...  Lots of possible reasons.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, March 15, 2014 12:31 AM