Home » RDBMS Server » Server Utilities » sql loader and foreign keys
sql loader and foreign keys [message #223440] Thu, 08 March 2007 22:39 Go to next message
jeffreyeas
Messages: 3
Registered: March 2007
Junior Member
hello all,

i have the following tables created in my database;

CREATE TABLE APARTMENT
(APTID NUMBER(5) NOT NULL,
TYPE CHAR(10) NOT NULL,
CONSTRAINT TYPE_CONSTRAINT CHECK (TYPE IN ('1 bedroom','2 bedroom','3 bedroom')),
RENT NUMBER(7) NOT NULL,
DEPOSIT NUMBER(10) NOT NULL,
STATUS CHAR(20) NOT NULL,
CONSTRAINT STATUS_CONSTRAINT CHECK (STATUS IN('vacant','unavailable')),
SPACE_LOC CHAR(7) NOT NULL,
CONSTRAINT APARTMENT_PRIMARY_KEY PRIMARY KEY (APTID)
);


CREATE TABLE LEASE
(LEASE_ID VARCHAR (7) NOT NULL,
NUM_RESIDENTS NUMBER(5) NOT NULL,
LEASE_TYPE VARCHAR(20) NOT NULL,
STANDING CHAR(20) NOT NULL,
EM_CON_NAME CHAR(20) NOT NULL,
EM_CON_NUM VARCHAR(20) NOT NULL,
APTID NUMBER(5) NOT NULL,
CONSTRAINT LEASE_FOREIGN_KEY FOREIGN KEY (APTID) REFERENCES APARTMENT (APTID),
CONSTRAINT LEASE_PRIMARY_KEY PRIMARY KEY (LEASE_ID)
);

what is the command to reference APTID in sqlldr so i dont have to type the numbers out again?

in my apartment.ctl file i've got

load data
infile *
append
into table apartment
fields terminated by ","
(aptid,type,rent,deposit,status,space_loc)
BEGINDATA
39828,2 bedroom,800,400,vacant,East
38440,2 bedroom,600,800,vacant,North

etc..

then in my lease.ctl file i've got

LOAD DATA
INFILE *
INFILE apartment.ctl
INTO TABLE LEASE
fields terminated by ","
(LEASE_ID,
NUM_RESIDENTS,
LEASE_TYPE,
STANDING,
EM_CON_NAME,
EM_CON_NUM,
APTID POSITION(1:200) INTEGER EXTERNAL)
BEGINDATA
9605312,5,2yr,current,Gates, Olga N.,(223) 403-7973


and it doesn't seem to accept the aptid entities. i know i'm missing something but can't figure it out. Any help is appreciated

Report message to a moderator

Re: sql loader and foreign keys [message #223524 is a reply to message #223440] Fri, 09 March 2007 06:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> what is the command to reference APTID in sqlldr so i dont have to type the numbers out again?
Even if you do a regular insert, you need to fill in the numbers in child table. The constraint will just make sure that the inserted records are already available in parent table.
Use rows=1 for loading parent/child tables.
Search the board for more examples. One such (with use of sequences)
http://www.orafaq.com/forum/m/146758/42800/?srch=primary+key+sqlldr#msg_146737

Report message to a moderator

Re: sql loader and foreign keys [message #223571 is a reply to message #223524] Fri, 09 March 2007 10:57 Go to previous messageGo to next message
jeffreyeas
Messages: 3
Registered: March 2007
Junior Member
can you elaborate on the use rows = 1 thing? My objective is to load APTID into the table lease in sql loader. They are already generated in table apartment and APTID is a foreign key.

Report message to a moderator

Re: sql loader and foreign keys [message #223572 is a reply to message #223571] Fri, 09 March 2007 11:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
For now forget ROWS=1.
In this clause
>>CONSTRAINT LEASE_FOREIGN_KEY FOREIGN KEY (APTID) REFERENCES APARTMENT (APTID),

You are asking to check the record inserted into lease.aptid is already existing in apartment.aptid. Else, the insert will fails.
That is it.
>> My objective is to load APTID into the table lease in sql loader. They are already generated in table apartment and APTID is a foreign key
So you want the LEASE.APTID to be automatically inserted from APARTMENT.APTID?
Not sure i am following your business need.

Report message to a moderator

Re: sql loader and foreign keys [message #223599 is a reply to message #223572] Fri, 09 March 2007 13:35 Go to previous message
jeffreyeas
Messages: 3
Registered: March 2007
Junior Member
Mahesh Rajendran wrote on Fri, 09 March 2007 11:13
For now forget ROWS=1.
In this clause
>>CONSTRAINT LEASE_FOREIGN_KEY FOREIGN KEY (APTID) REFERENCES APARTMENT (APTID),

You are asking to check the record inserted into lease.aptid is already existing in apartment.aptid. Else, the insert will fails.
That is it.
>> My objective is to load APTID into the table lease in sql loader. They are already generated in table apartment and APTID is a foreign key
So you want the LEASE.APTID to be automatically inserted from APARTMENT.APTID?
Not sure i am following your business need.



Yes the APARTMENT.APTID is already populated into my database. i'm just not sure on how to add those same values to LEASE.APTID

Report message to a moderator

Previous Topic: Data import
Next Topic: Export Backup
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jul 02 15:45:04 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.