When using the schema browser and viewing table attributes, the Primary Key for every table I view shows as . I am also trying to create an ER Diagram and I have selected to just show primary and foreign keys for the entities, and the entities are blank. Why isn’t the schema including any key information for me?
Are your primary keys really configured as primary key constraints in the database, or are they just indexes?
Here is the query that we use to find primary key for a table:
Select c1.column_name, c1.position
from sys.user_cons_columns C1, sys.user_constraints A1
where A1.table_name = C1.Table_name
and A1.constraint_name = C1.Constraint_Name
and A1.constraint_type = ‘P’
and A1.table_name =:TabName
ORDER BY 2
Or, if the table is not in your schema:
Select c1.column_name, c1.position
from sys.DBA_CONS_COLUMNS C1, sys.DBA_CONSTRAINTS A1
where A1.table_name = C1.Table_name
and A1.constraint_name = C1.Constraint_Name
and A1.constraint_type = ‘P’
and A1.table_name =:TabName
and C1.owner = A1.Owner
and A1.owner = :own
ORDER BY 2
And for the 2nd query, if you don’t have privileges to use the DBA_ views, Toad will use the ALL_ views instead.
Primary Key is logical attribute-constraint which is relaying on index (mostly unique but not necessary!).
Unique index is physical attribute (object in database).
It looks like when they defined the schema for the replicated database, they just didn’t include defining the constraints in the schema, the primary and foreign key constraints weren’t included. This would probably explain why I’m seeing what I’m seeing. I need to confirm it with the DBAs though.