Generate PK and FK - Script - error

All,
 

I was creating a script for PK and FK
generation and looks like I am stuck at this point ..
 

Problem:
If the table has a primary key with combinaiton of colums , my script is not taking all , it's just taking the last position .

In one case it shows all the column, but in another it just takes the last one and I need to take all of them and seperate them with ','
 

this is how my script is
 

first script (Shows all )
 

SELECT   a.owner, a.table_name,
a.constraint_name, b.column_name    FROM dba_constraints a, dba_cons_columns b   WHERE a.constraint_name = b.constraint_name     AND a.owner =
b.owner     AND a.constraint_type =
'P'     AND a.owner LIKE UPPER ('USER_NAME%')ORDER BY a.owner, a.table_name, b.position;
 
 

second script (shows only last column)

 

SELECT   ' Alter table ' || a.owner ||
'.' || a.table_name ||  ' ADD CONSTRAINT ' || a.constraint_name || ' PRIMARY KEY  ( ' || b.column_name || ' )   ;'
    FROM dba_constraints a,

dba_cons_columns b   WHERE a.constraint_name = b.constraint_name     AND a.owner =
b.owner     AND a.constraint_type =
'P'     AND a.owner LIKE UPPER ('USER_NAME%')ORDER BY a.owner, a.table_name, b.position/
 
 

TIA
Cheers
RK Received on Thu Dec 13 2001 - 10:45:44 CST