RE: [Q] what difference between PK and unique index + not NULL??
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <CMarquez@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Jan 2005 15:52:08 -0800
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
Other related posts:
