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

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_at_quest.com] 
	Sent: Tuesday, January 18, 2005 6:52 PM
	To: Marquez, Chris; oracle-l_at_freelists.org
	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.
	

--
http://www.freelists.org/webpage/oracle-l