Exact difference between primary key and unique + not nul
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Exact difference between primary key and unique + not nul

  1. #1
    Join Date
    Jul 2006
    Posts
    96

    Exact difference between primary key and unique + not nul

    hi friends,

    what is the difference between primary key column and not null + unique column.
    I know one difference is a table can have only one primary key and any number of not null + unique key columns.
    I think the functionality is same for the both type of columns.
    Any other differences that you know??

    by madhu

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Since you get an index for free, the PK index will be one-to-one for values. For unique, given that nulls are not indexed in this case, you may have fewer indexed values.

  3. #3
    Join Date
    Jul 2006
    Posts
    96

    clustered index for primary key doubt?

    I heard that a clustered index will be created automatically
    for primary key. As per my knowledge cluster means combination of two common columns of two tables. But how does a primary key creates the
    cluster index? for which cluster it creates the index?

    with thanks

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by madhugp View Post
    I heard that a clustered index will be created automatically
    for primary key. As per my knowledge cluster means combination of two common columns of two tables. But how does a primary key creates the
    cluster index? for which cluster it creates the index?
    "Clustered Index" is a SQL Server concept - closest thing in Oracle is an IOT e.g. Index Organized Table.

    A PK constraint requires to have a unique index on a not nul column (or set of columns)
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Apr 2007
    Location
    USA
    Posts
    110
    a PK is a UNIQUE plus NOT NULL (you get a bonus index)...ie no duplicates no NULLS
    a Unique index/column only prevents duplicates but would allow NULLs.
    Looking for the greatest evil in the world? Look in the mirror.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Let me spice this one a little - even when risking to add some confusion.

    PK constraints do not require a unique index, you can create a PK on a non-unique index. Moreover, in some cases it is mandatory to do so - like when you are asking Oracle to defer contraints checking until the end of the transaction.

    After you digest this one start thinking about the crazy (and useful) things you can do manipulating VALIDATE/NOVALIDATE and ENABLE/DISABLE option.

    So, does in general a PK comes alongside a unique index? Yes... but that does not means it has to be unique.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width