Re: Creating tables, mapping from ER diagrams

In article <D1vFyB.7pp_at_mail.auburn.edu>, moorthy_at_eng.auburn.edu (Moorthy N. Rekapalli) writes:
|> Hello Everyone,
|>
|> When we create tables, we mention primary keys and foreign keys according to Entity - Relationship diagrams. For example,
|>
|> CREATE TABLE ORD (
|> ORDID NUMBER (4) NOT NULL,
|> ORDERDATE DATE,
|> COMMPLAN CHAR (1),
|> CUSTID NUMBER (6) NOT NULL,
|> SHIPDATE DATE,
|> TOTAL NUMBER (8,2) CHECK (TOTAL >= 0) CONSTRAINT TOTAL_ZERO,
|> FOREIGN KEY (CUSTID) REFERENCES CUSTOMER (CUSTID) CONSTRAINT ORD_FOREIGN_KEY,
|> PRIMARY KEY (ORDID) CONSTRAINT ORD_PRIMARY_KEY);
|>
|> After creating the tables, desc table_name will not show the information regarding primary and foreign keys.
|>
|> SQL> desc ord;
|> Name Null? Type
|> ------------------------------- -------- ----
|> ORDID NOT NULL NUMBER(4)
|> ORDERDATE DATE
|> COMMPLAN CHAR(1)
|> CUSTID NOT NULL NUMBER(6)
|> SHIPDATE DATE
|> TOTAL NUMBER(8,2)
|>
|>
|> Is there any way to see the table definitions with primary and foreign keys after creating them ? This will be very helpful when we are following up some other's work.
|>
|> THANKS IN ADVANCE.
|>
|> Moorthy
|> (moorthy_at_eng.auburn.edu)
|>
|> ---
|> :=) Do unto others as you would wish to be done by others. :=)
|>

Look at the two data dictionary views (dba_constraints & dba_cons_columns) these should be queried to see the constraints (primary and foreign keys) on the table(s). Note the recursive relationship on dba_constraints,

  __________ /    __      __/______  |Jack C. Carter,  Systems Architect
    / _  _  /_   / ' _  _  /  _  _   |Cap Gemini America - Consulting
 \_/ (/ (_ /|   /_, (/ /' /, (' /'   |EMAIL jack.carter_at_nt.com
 ----------------------------------- |PHONE: (214) 933-4313