oracle and foreign keys

[lipaika]

Hello,

I have a database in which no foreign key has been created.
I'd like to know if it is possible to specify it after the creation of the tables thanks to oracle 10g.

I have a text file which describes all links between tables, and I'd like to create foreign keys from a parser of this file.

if you have any idea to do this, it could really help me.
Thanks

 

[SantaMufasa]

I'd like to know if it is possible to specify (PK) after the creation of the tables thanks to oracle 10g.

Actually, you can specify a PK after the creation of the table all the way back to very early versions of Oracle (e.g., Oracle V5, et cetera).


Here is a proof of concept of a retro-fit PK:

SQL> create table lipaika (x number, y varchar2(10));

Table created.

SQL> alter table lipaika add primary key (x);

Table altered.

SQL> @stru
Enter table name: lipaika

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -----------------------------------------
  1 X                  NUMBER        [PK:SYS_C0013346:ENABLED] NOT NULL/UNIQUE
  2 Y                  VARCHAR2(10)
Another table (Y/N) ?
******************************************************************************

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[lipaika]

thank you for this exemple, but I'd also like to know if it could work for foreign keys.
In fact, I have a text file which describes all links between tables, and all updates between tables occurs by the side of the client software. But now, there are too many software that use the database, so it is really necessary to specify all the links between tables without changing the first version of the database.

Could you also advise me a book for administration of oracle10g.

Thank you for your help,
and sorry for my bad english.

See you

 

[stefanhei]

You can add foreign keys after creation of the tables using the following syntax:

CREATE TABLE t1(a NUMBER);
CREATE TABLE t2(b NUMBER);
ALTER TABLE t2 
 ADD CONSTRAINT fk_t1_a 
 FOREIGN KEY (b) REFERENCES t1(a);

Stefan

 

[SantaMufasa]

Stefan,

Actually, (for clarity and completeness) for your code, above, to work, you must declare t1.a as either a "primary key" or "unique" prior to running the "ALTER TABLE t2..." statement.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]