SQL*Loader or PLSQL (Referential Integrity) 1

[StickyBit]

Hi folks,

Need advice on relating a child table to a parent table after populating the child table with data. Below are the two table structures and a brief description of the problem.

Parent Table (Networks)

CREATE TABLE NETWORKS(
network_id NUMBER(4),
name VARCHAR2(50),
description VARCHAR2(512),
contract_id NUMBER(4),
drawing_url VARCHAR2(256),
CONSTRAINT network_id_pk PRIMARY KEY (network_id));

Note: Primary key network_id is a sequence

Child Table (Sites)

CREATE TABLE SITES(
site_id NUMBER(4),
name VARCHAR2(128) NOT NULL,
type VARCHAR2(50) NOT NULL,
address_1 VARCHAR2(128),
address_2 VARCHAR2(128),
city VARCHAR2(20),
province VARCHAR2(20),
country VARCHAR2(20),
postal_code VARCHAR2(20),
office_phone1 VARCHAR2(20),
office_phone2 VARCHAR2(20),
fax VARCHAR2(20),
description VARCHAR2(26),

The parent table (networks) is already populated with data, I will be using SQL*Loader to populate the sites table from a CSV file. Once I load the sites data into the sites table, how will I establish referential integrity between the Networks and the Sites table? In other words, how can I insert the network_id from the Networks table into the network_id of the sites table?

I’m thinking about creating a Stored Procedure to relate the two tables, is this correct, or should I truncate the networks table and load the data into the tables at the same time using SQL*Loader?

I have disabled all referential integrity constraints for the bulk load.

Any help on this subject is appreciated.

Stickybit

 

[StickyBit]

Missing info from sites table:

CREATE TABLE SITES(
site_id NUMBER(4),
name VARCHAR2(128) NOT NULL,
type VARCHAR2(50) NOT NULL,
address_1 VARCHAR2(128),
address_2 VARCHAR2(128),
city VARCHAR2(20),
province VARCHAR2(20),
country VARCHAR2(20),
postal_code VARCHAR2(20),
office_phone1 VARCHAR2(20),
office_phone2 VARCHAR2(20),
fax VARCHAR2(20),
description VARCHAR2(26),
longitude VARCHAR2(20),
latitude VARCHAR2(20),
drawing_url VARCHAR2(20),
network_id NUMBER(4),
CONSTRAINT site_id_pk PRIMARY KEY (site_id));
CONSTRAINT network_id_fk FOREIGN KEY (network_id) REFERENCES networks(network_id));

 

[did02]

Hi,

First, the network_id_fk FK means that to insert a site with a given network_id, this network_id must exist in NETWORKS table (except if your foreign key is disabled).
In other words, all network_id you'll find in SITE table exist in NETWORK table.

This means that before populate your SITE table, you have to populate the NETWORK table. And when populating your SITE table, you have to set the network_id (the FK constraint just check that it exists into NETWORK table).

I don't know your input layout so it's difficult to advice you how to populate these tables.

Hope it helps u
Rgds,
Did02

 

[comgrit]

What I normally do with this situation is to create the tmp_site in which it has no referencial integrity. Once loaded, then I assign the network_id, and insert from tmp_site to site.

You can also do it via SQL*Loader - but I found that working with sql scripts is more easier (at least for me) than create the complex control file for SQL*Loader.

Hope this helps.
Comgrit

 

[StickyBit]

Thanks Comgrit I did just that and it worked flawlessly!

Stickybit.