Forum Stats

  • 3,781,149 Users
  • 2,254,484 Discussions
  • 7,879,596 Comments

Discussions

What's the difference between primary key and unique index with not null constraint?

royalwzy
royalwzy Member Posts: 104
edited Jan 27, 2016 11:43AM in General Database Discussions

Does primary key = unique index + not null?

Tagged:
royalwzyCloudDBFranck Pachot

Best Answer

«1

Answers

  • CloudDB
    CloudDB Member Posts: 1,059 Gold Badge
    edited Jan 27, 2016 4:29AM

    Yes

    Primary Key Internal create Unique index for enforce the Uniqueness of data residing in primary column.

    royalwzy
  • Pini Dibask
    Pini Dibask Member Posts: 521 Gold Badge
    edited Jan 27, 2016 4:34AM Accepted Answer

    The short answer is yes.

    However, although primary key enforce both uniquness and not null, it has its "special" meaning.

    You can only have one primary key in a tables, but you can have many unique indexes and not null constraints.

    See: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8743855576462

    royalwzy
  • Oratig-Oracle
    Oratig-Oracle Member Posts: 1,433 Silver Trophy
    edited Jan 27, 2016 4:37AM

    Hi,

    Having single rule to check both condition will be primary key.

    But, only one primary key can be kept for single table.

    But when we keep, not null and unique constraint, it will be acting like primary key, but we are going with two different rule to check integrity .

    Thanks

    royalwzyroyalwzy
  • vit.spinka
    vit.spinka Member Posts: 62
    edited Jan 27, 2016 6:15AM

    The short answer is yes.

    The longer one:

    Primary key is kind of 'the best of unique keys you have on a table'. For example, if you configure supplemental logging (for LogMiner, Streams or Golden Gate), Oracle will pick the primary key as the first candidate. Other examples are in the AskTom link, too.

    DBAs and developers expect a (good behaving) table to have a primary key. Some tools do, too.

    It's actually painful to develop these tools: they have to go for PK, then if it's not there, check for unique keys, but only if it at least one column is NOT NULL, so it still guarantees uniqueness.

    Franck Pachot
  • Richard Foote-Oracle
    Richard Foote-Oracle Member Posts: 62
    edited Jan 27, 2016 6:48AM

    Note that Oracle can use a non-unique index to police either a PK or UK since Oracle 8.

    Cheers

    Richard Foote

    https://richardfoote.wordpress.com/

    CloudDB
  • CloudDB
    CloudDB Member Posts: 1,059 Gold Badge
    edited Jan 27, 2016 7:46AM
  • JuanM
    JuanM Member Posts: 2,155 Gold Trophy
    edited Jan 27, 2016 9:24AM
    royalwzy wrote:
    
    Does primary key = unique index + not null?
    

    No.

    As you can see in the Doc, (http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm)

    Primary key = Not Null CONSTRAINT + Unique CONSTRAINT.

    Constraints != Indexes

    Regards,

    Juan M

  • ddf_dba
    ddf_dba Member Posts: 1,398 Bronze Trophy
    edited Jan 27, 2016 9:58AM

    And creating a UNIQUE index and setting the columns to NOT NULL create constraints.  Let's look at creating a primary key versus a unique index/not null combination.  First add the primary key:

    SQL> alter table pk_nnui_test add constraint pk_nnui_test_pk primary key(p_id, scatt);

    Table altered.

    SQL>

    SQL> desc  pk_nnui_test

    Name                                                                    Null?    Type

    ------------------------------------------------------------------------ -------- --------------------------------

    P_ID                                                                     NOT NULL NUMBER

    CLUST                                                                             NUMBER

    SCATT                                                                    NOT NULL NUMBER

    STRG_SMALL                                                                        VARCHAR2(40)

    STRG_PADDED                                                                       VARCHAR2(100)

    SQL>

    SQL> select owner, table_name, constraint_name, constraint_type

      2  from user_constraints;

    OWNER                          TABLE_NAME                    CONSTRAINT_NAME                C

    ------------------------------ ------------------------------ ------------------------------ -

    BING                           PK_NNUI_TEST                   PK_NNUI_TEST_PK                P

    BING                           EMP                            SYS_C004197                    C

    BING                           EMP2                           SYS_C004211                    C

    SQL>

    Drop the primary key and replace it with a unique index/not null constraint combination:

    SQL> alter table pk_nnui_test drop constraint pk_nnui_test_pk;

    Table altered.

    SQL>

    SQL> desc pk_nnui_test

    Name                                                                     Null?    Type

    ------------------------------------------------------------------------ -------- ----------------------------------------

    P_ID                                                                              NUMBER

    CLUST                                                                             NUMBER

    SCATT                                                                             NUMBER

    STRG_SMALL                                                                        VARCHAR2(40)

    STRG_PADDED                                                                       VARCHAR2(100)

    SQL>

    SQL> create unique index pk_nnui_test_unq on pk_nnui_test(p_id, scatt);

    Index created.

    SQL>

    SQL> alter table pk_nnui_test modify p_id not null;

    Table altered.

    SQL> alter table pk_nnui_test modify scatt not null;

    Table altered.

    SQL>

    SQL> desc pk_nnui_test

    Name                                                                     Null?    Type

    ------------------------------------------------------------------------ -------- ----------------------------------------

    P_ID                                                                     NOT NULL NUMBER

    CLUST                                                                             NUMBER

    SCATT                                                                    NOT NULL NUMBER

    STRG_SMALL                                                                        VARCHAR2(40)

    STRG_PADDED                                                                       VARCHAR2(100)

    SQL>

    SQL> select owner, table_name, constraint_name, constraint_type

      2  from user_constraints;

    OWNER                          TABLE_NAME                     CONSTRAINT_NAME                C

    ------------------------------ ------------------------------ ------------------------------ -

    BING                           EMP                            SYS_C004197                    C

    BING                           EMP2                           SYS_C004211                    C

    BING                           PK_NNUI_TEST                   SYS_C004411                    C

    BING                           PK_NNUI_TEST                   SYS_C004410                    C

    SQL>

    Both actions result in constraints being created; the major difference is the primary key enforces one constraint for all specified columns in addition to creating the unique index while the second creates the NOT NULL constraints on both columns.  In either case constraints are enforced.

    David Fitzjarrell

  • JohnWatson2
    JohnWatson2 Member Posts: 4,358 Silver Crown
    edited Jan 27, 2016 10:47AM

    Well, not quite. In your example, you aren't declaring a unique constraint - you are only creating a unique index. OK, that means you can't have duplicates - but it isn't the same:

    orclz> alter table uktest add constraint c1uk unique(c1);

    Table altered.

    orclz> alter table uktest modify(c1 not null);

    Table altered.

    orclz> select constraint_name, constraint_type from user_constraints where table_name='UKTEST';

    CONSTRAINT_NAME                C

    ------------------------------ -

    C1UK                           U

    SYS_C0014594                   C

    orclz>

    The concept of using a unique index without declaring a constraint really dates back to release 5 or 6 (I think) before Oracle had proper declarative constraints. Unless you declare unique keys, you may mess up any tools that rely on querying dba_contraints to navigate around the relational model.

  • ddf_dba
    ddf_dba Member Posts: 1,398 Bronze Trophy
    edited Jan 27, 2016 10:57AM

    I didn't create a unique constraint, and the original question was regarding primary key constraints and the combination of a unique index and NOT NULL constraints. My example was never meant to be comprehensive, simply illustrative of the original question.

    David Fitzjarrell

This discussion has been closed.