[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Determine Primary/Foriegn Keys, constraints, etc

Posted on 2004-11-17
14
Medium Priority
?
1,345 Views
Last Modified: 2007-12-19
I am connecting to an Oracle DB.  I have conneced in 2 different ways:

1. Through Importing the data into MS SQL
2. Through SQL *Plus

Is there a way in either of these two methods (or an alternate approach) where I can find out all or any of the following about a specific table:

Primary Keys
Foriegn Keys
Constraints
etc....

Thanks.
0
Comment
Question by:mrichmon
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 6

Expert Comment

by:morphman
ID: 12606021
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.
0
 
LVL 1

Assisted Solution

by:MWahba
MWahba earned 800 total points
ID: 12606418
Try this script:

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
      ,vb.Index_Name
  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;
0
 
LVL 3

Expert Comment

by:oratim
ID: 12606544
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 35

Author Comment

by:mrichmon
ID: 12606634
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.
0
 
LVL 3

Expert Comment

by:oratim
ID: 12606970

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>
0
 
LVL 3

Expert Comment

by:oratim
ID: 12606988
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
0
 
LVL 1

Expert Comment

by:MWahba
ID: 12607042
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.
0
 
LVL 35

Author Comment

by:mrichmon
ID: 12607363
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>
0
 
LVL 3

Accepted Solution

by:
oratim earned 1200 total points
ID: 12607555
type the forward slash then hit return/

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>
0
 
LVL 35

Author Comment

by:mrichmon
ID: 12607681
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
0
 
LVL 3

Expert Comment

by:oratim
ID: 12607904
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
 
0
 
LVL 35

Author Comment

by:mrichmon
ID: 12610466
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....
0
 
LVL 1

Expert Comment

by:MWahba
ID: 12610596
in some system the primary key ( and other type of constraints) are maintained on the application level, not on the DB level.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 12614835
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;
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question