Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

View contraint 1

Status
Not open for further replies.
jdbolt

jdbolt

Programmer
Aug 10, 2005
89
CA
I have inheritied a database from a previous developer, however they did not document the contraints. I can view the contraints using:

Code: 
select constraint_name, table_name, constraint_type from user_constraints;

and i get the following

Code: 
CONSTRAINT_NAME                TABLE_NAME                     C
------------------------------ ------------------------------ -
SYS_C005920                    ADMIN                          C
SYS_C005921                    ADMIN                          P
SYS_C006876                    ANNOUNCEMENT                   C
SYS_C006877                    ANNOUNCEMENT                   C
SYS_C006878                    ANNOUNCEMENT                   C
PK_ANNOUNCEMENT                ANNOUNCEMENT                   P
FK_ANNOUNCEMENT_MODIFIERID     ANNOUNCEMENT                   R
CHECK_CASCADEANNOUNCEMENTS     ANNOUNCEMENT                   C
SYS_C005922                    CLASS                          P
SYS_C005923                    DOCUMENT                       C
SYS_C005924                    DOCUMENT                       C

CONSTRAINT_NAME                TABLE_NAME                     C
------------------------------ ------------------------------ -
SYS_C005925                    DOCUMENT                       C
SYS_C005926                    DOCUMENT                       C
SYS_C005927                    DOCUMENT                       C
SYS_C005928                    DOCUMENT                       C
SYS_C005929                    DOCUMENT                       P
SYS_C006209                    DOCUMENT                       R
SYS_C005930                    GROUPS                         C
SYS_C005931                    GROUPS                         C
SYS_C005932                    GROUPS                         P
SYS_C006868                    LINK                           C
SYS_C006869                    LINK                           C

CONSTRAINT_NAME                TABLE_NAME                     C
------------------------------ ------------------------------ -
SYS_C006871                    LINK                           C
PK_LINK                        LINK                           P
FK_LINK_MODIFIER               LINK                           R
FK_LINK_CATEGORY               LINK                           R
CHECK_CASCADELINKS             LINK                           C
PK_LINKCATEGORY                LINKCATEGORY                   P
SYS_C005933                    MODIFIER                       C
SYS_C005934                    MODIFIER                       C
SYS_C005935                    MODIFIER                       C
SYS_C005936                    MODIFIER                       P
FK_MODIFIER_PARENTMODIFIER     MODIFIER                       R

CONSTRAINT_NAME                TABLE_NAME                     C
------------------------------ ------------------------------ -
UQ_MODIFIER_MODIFIER           MODIFIER                       U
SYS_C006900                    MODIFIERCLASS                  C
SYS_C006901                    MODIFIERCLASS                  C
SYS_C006902                    MODIFIERCLASS                  C
PK_MODIFIERCLASS               MODIFIERCLASS                  P
FK_MODIFIERCLASS_MODIFIER      MODIFIERCLASS                  R
FK_MODIFIERCLASS_CLASS         MODIFIERCLASS                  R

Form my understanding R = foreign key, P= primary and I guess C = check. Is there anyway to view the SQL that created the constraints? For example how do I know which table and column a foreign key opints to, or understand what the check constraint actually does?
 
Sort by date Sort by votes
Yes, you can write scripts to do all of this, but a simpler option might be to get hold of a free copy of TOAD. That has an option to generate the script used to build a table.
 
Upvote 0 Downvote
In 9i and upward you may use DBMS_METADATA package to restore DDL (not constraints but the whole table). You may also investigate USER_CONS_COLUMNS view.

Regards, Dima
 
Upvote 0 Downvote
Or, (for my 2 cents/pence/drachma's worth), if you don't wish to install TOAD, you can use a script that produces these results:

Section 1 -- Results of this "super-DESCRIBE" script that I call "stru.sql":
Code: 
SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER        [CK:S_EMP_ID_NN:ENABLED] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:ENABLED] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:ENABLED] "LAST_NAME"
                                     IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:ENABLED] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER        [FK:S_EMP_MANAGER_ID_FK:ENABLED] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:ENABLED] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER        [FK:S_EMP_DEPT_ID_FK:ENABLED] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER
 11 COMMISSION_PCT     NUMBER        [CK:S_EMP_COMMISSION_PCT_CK:ENABLED]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Another table (Y/N) ? 
*********************************************************************************
Notice that for each column's constraint, above, it shows the constraint type and give a clear explanation of the enforcement that the constraint implements.

Here, then is the code to achive the above results:

Section 2 -- Before running "stru.sql" (in Section 3, below), execute this one-time-only script ("strumake.sql"), to setup some minor infrastructure upon which "stru.sql" depends:
Code: 
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
drop table temp_cons;
create table temp_cons as
select constraint_name, constraint_type, ' ' search_condition,
r_constraint_name, status
from user_constraints
where 1 = 2;
alter table temp_cons modify search_condition varchar(2000)
/
create or replace procedure prep_user_constraints (table_2_do in varchar)
  is
  cursor cons is
     select constraint_name,constraint_type,
            search_condition,r_constraint_name, status
            from user_constraints
            where table_name = upper(table_2_do);
  my_search_condition varchar(32767);
begin
  delete temp_cons;
  commit;
  for i in cons loop
     my_search_condition := i.search_condition;
     insert into temp_cons values
       (i.constraint_name,i.constraint_type,
        my_search_condition,i.r_constraint_name,i.status);
     commit;
  end loop;
end;
/
Do not be alarmed if you see the error:
"ERROR at line 1:
ORA-00942: table or view does not exist"

Then, once you have run the above script (only once per schema in which you want the "Super-DESCRIBE" to run, you can, on an as-needed basis run the "stru.sql" script:
Code: 
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any
REM industrial application or purpose nor is there any claim that
REM this or any similarly-distributed scripts are error free or
REM should be used for any purpose other than illustration.
REM **************************************************************
set echo off
set verify off
set feedback off
set linesize 100
set pagesize 500
break on "#" on a on b
accept x prompt "Enter table name: "
col a format a18 heading "Column|Name" word_wrapped
col b format a13 heading "Data Type|and Length"
col c format a43 heading "[Constraint Type: Name: En-/Dis-abled]|and Enforcement" word_wrapped
col "#" format 99 heading "Col| # "
exec prep_user_constraints('&x')
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
       '[' ||
           decode(con.constraint_type,'P','PK','R','FK','C','CK','U','UK')
           || ':' ||con.constraint_name
           || ':' ||con.status
           || '] ' ||
           decode(con.constraint_type,
              'P','NOT NULL/UNIQUE',
              'U','UNIQUE',
              'C',con.search_condition,
              'R','Match : '||r_constraint_name) c
from   temp_cons con,
       user_cons_columns con_col,
       user_tab_columns col
where  col.table_name      = upper('&x')
  and  con.constraint_name = con_col.constraint_name
  and  col.table_name      = con_col.table_name
  and  col.column_name     = con_col.column_name
  )
union
((select column_id "#",
         col.column_name a,
         col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col
 where col.table_name = upper('&x'))
minus
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col, user_cons_columns con_col
 where col.table_name  = upper('&x')
   and col.table_name  = con_col.table_name
   and col.column_name = con_col.column_name)
)
order by 1
/
accept again prompt "Another table (Y/N) ? "
set heading off
set pagesize 0
spool temp.sql
set termout off
select decode(upper('&again'),'Y','@stru') from dual;
set termout on
spool off
set heading on
set pagesize 23
set feedback on
@temp
Let us know if this provides you with help that you want.



[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
That worked great, not only can I see all the contraints but you have now confrimed that the previous developer was an idiot :)

Thanks, this was great!
 
Upvote 0 Downvote
Three Cheers for Santa Mufasa!

That is quite a powerful tool.

I find myself coming here to see how you have helped the next guy.

I would be curious to hear some of your background. Is there a Santa Mufasa bio anywhere?
 
Upvote 0 Downvote
You are very gracious, DJ. Interestingly enough, in the "Squaring the Circle" Tek-Tips forum, there is a wonderful thread named, "Introductions Please!" (thread1229-767978), in which posters can share their bios with others. I posted my bio in that thread on "29 Jun 05 13:56" (The ensuing discussions about personal specifics where rather fun/entertaining, as well.)

The only major personal update to that posting is that I discovered an amazing way to safely, effectively, comfortably, painlessly, and rapidly lose weight: I've dropped from 260 lbs. to 155 lbs. (105 lbs.) in 112 days of using this strategy...all under the monthly, watchful eyes of two physicians (a cardiologist and a GP) who have confirmed the safety and effectivity of my strategy. (Both physicians are actually following my strategy, as well.)

y cholesterol has plummeted during that time from 280 to 139 (more than half), my blood pressure has also dropped from 180/100 to 116/67 (an 18-year-old's level), my snoring and sleep apnea have disappeared, my belt size has shrunk from 47 to 32, and best of all: I went from Type II Diabetic to non-diabetic in 90 days. Woo-Hoo!!! Kinda fun, huh?

If anyone wants to know the "secret", I'm writing a downloadable eBook about my discoveries in between posts to Tek-Tips threads. As soon as it's available, I'll let folks that want to know, where the link is.

Well, way too much about me. Cheers,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
Santa,
Awesome script - I will be using that. Secondly, good job on losing that weight! A healty Santa is a happy Santa [smile]

All hail the INTERWEB!
 
Upvote 0 Downvote
please keep us in the loop of where that ebook will be...im starting to look like a bad impression of Jabba the Hut myself.
 
Upvote 0 Downvote
DJ,

Funny you should mention, "Jabba the Hut"...My working title for the eBook is, "I Banished Jabba the Hunt," since Mufasa's full, real name is "Dave Hunt". If I were able to get releases from George Lucas, the cover would be a chubby "Before" faceshot of me morphed onto Jabba's bulbous body, with an "After" photo of me in the lower corner.

I'll post the link as soon as there is an "Opus Magnum" available.[wink] Thanks, DJ (and others), for your kind encouragement...very gratifying and uplifting!

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[ Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tekpr00
  • Locked
  • Question
Getting PL/SQL: ORA-00942: table or view does not exist
Replies
8
Views
281
tekpr00
tekpr00
AlStl
  • Locked
  • Question
Update via a VIEW or Direct Update
Replies
3
Views
100
Beilstwh
Beilstwh
cmmrfrds
  • Locked
  • Question
Materialized View (Fast Refresh) How to?
Replies
0
Views
69
cmmrfrds
cmmrfrds
billo102
  • Locked
  • Question
"Select * from view" takes a very long time,on second attempt 1
Replies
2
Views
138
billo102
billo102
OraclerUser
  • Locked
  • Question
Materialized View: expression not supported for query rewrite error
Replies
2
Views
119
Beilstwh
Beilstwh

Part and Inventory Search

Sponsor

Back
Top