RE: [Q] what difference between PK and unique index + not NULL??

From: "Marquez, Chris" <CMarquez@xxxxxxxx>
To: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 19 Jan 2005 10:33:08 -0500
I stand corrected.
 
But still they are technically different...how they are seen and referenced in 
the database.
Not saying one is wrong and one is right...Oracle has far to many options to 
ever say that;
 
Logically these are the same, technical they are not;
SQL> create table t_p ( id number);
Table created.
 
SQL> ALTER TABLE t_p ADD CONSTRAINT t_p_PK PRIMARY KEY (id);
Table altered.
 

SQL> create table t_u ( id number);
Table created.
 
SQL> ALTER TABLE t_u ADD CONSTRAINT t_u_UQ unique (id);
Table altered.
 
SQL> ALTER TABLE t_u  MODIFY (id NOT NULL);
Table altered.

SQL> col TABLE_NAME format a12
SQL> col CONSTRAINT_NAME format a12
SQL> select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints 
where TABLE_NAME in ('T_P', 'T_U')
 
TABLE_NAME   CONSTRAINT_N CON
------------ ------------ ---
T_P          T_P_PK       P
T_U          T_U_UQ       U
T_U          SYS_C001485  C 

 
Thanks for the correction and example.
 
Chris Marquez
Oracle DBA
HEYMONitor(tm) - heymonitor.com
"Oracle Monitoring & Alerting Solution"
 
 

        -----Original Message-----
        From: Jacques Kilchoer [mailto:Jacques.Kilchoer@xxxxxxxxx] 
        Sent: Tuesday, January 18, 2005 6:52 PM
        To: Marquez, Chris; oracle-l@xxxxxxxxxxxxx
        Subject: RE: [Q] what difference between PK and unique index + not 
NULL??
        
        

        If I understand what you're trying to say, I'm going to have to 
disagree.
        In Oracle, I can have a FK constraint reference a unique constraint on 
columns that allow nulls; I can have a FK constraint referencing a PK 
constraint that's enforced by a non-unique index; I can have a FK constraint 
reference a unique constraint enforced by a non-unique index.
        
        Example (FK constraint referencing a unique constraint on columns that 
allow nulls, unique constraint enforced by a non-unique index)

        SQL> create table parent (id number, name varchar2 (30)) ;
        Table créée.
         
        SQL> create index parent_idx1 on parent (id) ;
        Index créé.
         
        SQL> alter table parent add (constraint parent_uq1 unique (id)) ;
        Table modifiée.
         
        SQL> create table child
          2   (id number, birth_date date,
          3    constraint child_fk1 foreign key (id) references parent (id)) ;
        Table créée.
         
        SQL> select index_name, uniqueness
          2   from user_indexes where table_name = 'PARENT' ;
        INDEX_NAME                     UNIQUENES
        ------------------------------ ---------
        PARENT_IDX1                    NONUNIQUE
         
        SQL> select
          2   a.constraint_type, a.r_constraint_name, b.column_name
          3   from user_constraints a, user_cons_columns b
          4   where a.constraint_name = 'CHILD_FK1'
          5         and b.constraint_name = a.constraint_name ;
        C R_CONSTRAINT_NAME              COLUMN_NAME
        - ------------------------------ --------------------
        R PARENT_UQ1                     ID
         
        SQL> 

        
        -----Original Message-----
        Marquez, Chris
        ...
        You can't have a FK point to a "Unique index +  Not NULL" with out a PK
        constraint!  That s a technical difference.
        ...
        Also like this comment too; "Oracle can from version 8 up use non-unique
        indexes to implement PK constraints."
        
        Tell your consultant he is "technical right,  until the customer asks
        for a child table (FK) on the original table.
        


--
//www.freelists.org/webpage/oracle-l
RE: [Q] what difference between PK and unique index + not NULL??

Other related posts:
