Determine Primary/Foriegn Keys, constraints, etc

look at the view DBA_CONSTRAINTS and DBA_CONS_COLUMNS you can specify table name, and it will tell you everything you need to know.

You should also check

DBA_INDEXES and DBA_IND_COLUMNS just incase primary keys are not enforced except for a unique index.

TRY THIS, IT USES THE ALL_CONSTRAINTS AND ALL_CONS_COLUMNS VIEWS INSTEAD OF THE USER VIEWS.

SELECT con.table_name,con.constraint_name,con.constraint_type, decode(con.constraint_type,'P','Primary Key','R','Foreign Key','U','Unique Index','C','Check Constraint') type, CON.SEARCH_CONDITION, col.column_name FROM ALL_CONSTRAINTS con, ALL_cons_columns col WHERE con.Table_name = '<your table name>'
and CON.OWNER = '<your schema>'
AND col.CONSTRAINT_NAME = con.CONSTRAINT_NAME

I think  you are going to have to be more specific.

I tried that through SQL* Plus and got nothing.

Looking through the MS SQL import data interface I do not see any of those views.

C:\SQLPLUS <USER>/<PWD>@DB

SQL>SELECT va.table_name
      ,va.constraint_name
      ,va.column_name
      ,decode(vb.constraint_type, 'C','Check',
                                  'P','Primary Key',
                                  'U','Unique',
                                  'R','References - Foreign Key',
                                  'V','View with Check Option',
                                  'O','Read Only on a View', 'UNKNOWN') con_type
      ,vb.delete_rule
  FROM user_cons_columns va
      ,user_constraints  vb
 WHERE va.constraint_name = vb.constraint_name
   AND va.table_name LIKE upper('%&tab_name%')
   AND vb.status = 'ENABLED'
 ORDER BY va.table_name,
          va.constraint_name,
          va.column_name;

SQL> /
Enter value for tab_name: PROGCODE
old  14:    AND va.table_name LIKE upper('%&tab_name%')
new  14:    AND va.table_name LIKE upper('%PROGCODE%')

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMN_NAME
-----------------------------------------------------------------------------------------------------

CON_TYPE                 DELETE_RU
------------------------ ---------
PROGCODE                       FK_PROGCODE_AREACODE
AREACODE
References - Foreign Key NO ACTION

PROGCODE                       SYS_C006364
DESCR
Check

PROGCODE                       SYS_C006365
AREACODE
Check

PROGCODE                       SYS_C006366
BLOCKNAME
Check

PROGCODE                       SYS_C006367
CNOTIF_REQ
Check

PROGCODE                       SYS_C006368
ACK_CODE
Check

PROGCODE                       SYS_C006369
ACTIVE
Check

PROGCODE                       SYS_C006370
PROGCODE
Primary Key


8 rows selected.

SQL>

btw, the views are all in the SYS schema.
but they do have public synonyms

if this does work, post the code you are using, maybe we can figure out whats wrong.

tim

Using the user_xxxxxxx view will return only the objects created by the current user. You can use All_cons_columns and All_constraints for other schemas.

I did that.

What happens is after I type the command and hit enter it goes to the next line and prints the number 2 and then if I hit enter again I get teh prompt

SQL> select con.table_name, con.constraint_name, con.constraint_type, decode(con.constraint_type, 'P
', 'Primary Key', 'R', 'Foreign Key', 'U', 'Unique Index', 'C', 'Check Constraint') type, con.SEARCH
_CONDITION, col.column_name FROM ALL_CONSTRAINTS con, ALL_cons_columns col WHERE con.table_name = 'M
YTABLE' AND con.OWNER = 'MYSCHEMA' AND col.CONSTRAINT_NAME = con.CONSTRAINT_NAME
   2
SQL>

Okay that works.  Now I get  output like this:

TABLE_NAME                     CONSTRAINT_NAME                C TYPE
------------------------------ ------------------------------ - ----------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
MYTABLE                        SYS_C0030724                   C Check Constraint
"MYTABLE_PID" IS NOT NULL
MYTABLE_PID

MYTABLE                        SYS_C0030725                   C Check Constraint
"MYTABLE_ID" IS NOT NULL
MYTABLE_ID

etc...

6 rows selected.

The only rows selected seem to be those that have the NOT NULL property on them.

And also I am looking for if I could find out more details about these constraints rather than "they exist"

The table I picked I am 99% sure that the MYTABLE_PID is a primary key

I am not sure what you mean. The results are tellung you that SYS_C0030724 is a Check Constraint that enforces that column MYTABLE_PID on MYTABLE is not null.

it is not a Primary Key, or a unique index.   If You Had A Primary Key You Would Have Some Data Like Below:

select con.table_name, con.constraint_name, con.constraint_type,
         decode(con.constraint_type, 'P', 'Primary Key', 'R', 'Foreign Key', 'U', 'Unique Index', 'C', 'Check Constraint') type,
         con.SEARCH_CONDITION, col.column_name
FROM ALL_CONSTRAINTS con, ALL_cons_columns col
WHERE con.table_name = 'PROGCODE'
        AND con.OWNER = 'PAPERLESS'
        AND col.CONSTRAINT_NAME = con.CONSTRAINT_NAME
TABLE_NAME                     CONSTRAINT_NAME    CONSTRAINT_TYPE      TYPE             SEARCH_CONDITION  COLUMN_NAME                                                    
------------------------------ ------------------------------ --------------- ---------------- -------------------------------           ---------------------------------------
PROGCODE             FK_PROGCODE_AREACODE           R               Foreign Key                                                        AREACODE                                                        
PROGCODE                       SYS_C006364                    C               Check Constraint         "DESCR" IS NOT NULL        DESCR  
PROGCODE                       SYS_C006365                    C               Check Constraint       "AREACODE" IS NOT NULL    AREACODE                                                        
PROGCODE                       SYS_C006366                    C               Check Constraint     "BLOCKNAME" IS NOT NULL    BLOCKNAME                                                      
PROGCODE                       SYS_C006367                    C               Check Constraint     "CNOTIF_REQ" IS NOT NULL   CNOTIF_REQ                                                      
PROGCODE                       SYS_C006368                    C               Check Constraint     "ACK_CODE" IS NOT NULL      ACK_CODE                                                        
PROGCODE                       SYS_C006369                    C               Check Constraint     "ACTIVE" IS NOT NULL          ACTIVE                                                          
PROGCODE                       SYS_C006370                    P               Primary Key                                                       PROGCODE                                                        
8 rows selected

this tells you the SYS_C006370 is a primary key for table PROGCODE and the column is PROGCODE

There are other columns on these tables, r_constraint_name tells you the constraint referenced by a constraint that is a foreign key, enabled is obvious, delete_rule (cascade, no action, etc)

to get a list of columns you can access, type DESC all_cons_columns and desc all_constraints.

to get a list of all the views you can access, use this SQL

 select view_name from all_views where owner = 'SYS'      

or to this it down a bit more

select view_name from all_views where owner = 'SYS' and view_name like 'ALL_%'      


HTH
tim
 

What you are saying makes sense, but I am 99% sure that there are primary keys in that table.  I will double check with the systems architect of the table....

in some system the primary key ( and other type of constraints) are maintained on the application level, not on the DB level.

In some systems, the "primary key" for a table is just enforced via a unique index, without having a constraint associated with the index and table.  This is because older versions of Oracle supported indexes, but not constraints, so some older applications may not have been updated to associate a primary or unique constraint with each unique index.  You can query from the views: "all_indexes" and "all_ind_columns" to find information about the indexes on tables.  Every unique or primary key constraint WILL have a corresponding index, but not all unique indexes have a corresponding constraint for the reason I already mentioned.

Also, if you imported some data from MS SQL, it is possible that the constraints, if any, that existed in MS SQL did not get created in Oracle.

Here is a script I use to see the index(es) for a table.  Save this as a *.sql file in your working directory for SQL*Plus, then you can run it by typing "@[file_name]", and provide the name of the table when prompted.  For example, if you save it as: "indexes.sql", then at your SQL> prompt, type:
@indexes

break on schema on table_name skip 2 on "Index name" skip 1 on "Unique" on "T";
column Column_name format a30;
column column_position format 99 heading "P";
column Unique format a1;
select substr(c.table_owner,1,16) "Schema", substr(c.table_name,1,20) "Table_name",
substr(c.index_name,1,30) "Index name", substr(i.index_type,1,1) "T",
substr(i.uniqueness,1,1) "Unique",
decode(substr(c.column_name,1,3),'SYS',
 ind_col_expr(i.owner, i.index_name,i.table_owner, i.table_name,c.column_position),
 c.column_name) "Column_name",  c.column_position
from all_indexes i, all_ind_columns c
where c.table_name like upper('&table_name')
-- and i.table_owner = user
and i.owner = c.index_owner
and i.index_name = c.index_name
and i.table_owner = c.table_owner
and i.table_name = c.table_name
order by c.table_owner, c.table_name, c.index_name, c.column_position;
clear breaks;