Re: Insert Value Error

From: Svend Jensen <Svend.S.Jensen_at_it.dk>
Date: Tue, 08 Jul 2003 22:33:21 +0200
Message-ID: <3f0b2a89$0$32516$edfadb0f_at_dread16.news.tele.dk>


Victor M. wrote:

> In article <Q6pOa.120391$MJ5.69016_at_fed1read03>, anacedent_at_hotmail.com 
> says...
> 

>>Victor M. wrote:
>>
>>>Hello everyone.
>>>
>>>I am a newbie to Oracle 8 so please be patient and thanks for your time.
>>>The problem is the above mentioned error. I have two databases
>>>successully created using SQL*Plus 8.03. They are as follows:
>>>
>>>Create Table EMPLOYEES
>>>(
>>> FName VARCHAR(15) NOT NULL,
>>> MINIT CHAR,
>>> LName VARCHAR(15) NOT NULL,
>>> SSN CHAR(9) NOT NULL,
>>> BDATE CHAR(25),
>>> Address VARCHAR(30),
>>> Sex CHAR,
>>> Salary DECIMAL(10,2),
>>> SUPERSSN CHAR(9),
>>> DNO INT,
>>> Primary Key(SSN),
>>> Foreign Key(SUPERSSN) REFERENCES EMPLOYEES(SSN),
>>> Foreign Key(DNO) REFERENCES DEPARTMENT(DNUMBER)
>>>);
>>>
>>>Create Table DEPARTMENT
>>>(
>>> DNAME VARCHAR(15) NOT NULL,
>>> DNUMBER INT NOT NULL,
>>> MGRSSN CHAR(9) NOT NULL,
>>> MGRSTARTDATE CHAR(25),
>>> Primary Key(DNUMBER),
>>> Unique(DNAME),
>>> Foreign Key(MGRSSN) References EMPLOYEES(SSN)
>>>);
>>>
>>>I then try to insert a record into the Employees table and received the
>>>above mentioned error:
>>>
>>>Insert into employees Values
>>> ('John', 'B', 'Smith', '123456789', '1965-01-09', '731 Fondren,
>>>Houston, TX', 'M', 30000, '33344555', 5);
>>>
>>>I then decided to try to insert a record into the Department table and
>>>received the exact same error. I have read on google.com since the
>>>Employees table has a foreign key which references a primary key in the
>>>Department table I should make sure the Foreign key value exist in the
>>>Department table first. But I'm caught in a loop because both tables
>>>have foreign keys which point to the primary key in the other table.
>>>
>>>Any help would be greatly appreciated.
> 
> 
> Thanks for the help. I am just beginning to learn Oracle on my own so 
> I'm bound to make mistakes.
> 
> 1. For the SSN field...do I have to take into consideration the 
> terminator character at the end ? I suppose that is what you mean by the 
> added 0 at the end of the string '1234567890' ?
> 
> 2. Would you tell me if this sounds right on disabling the Foreign Key 
> constraint in the Employees Table.
> 
> ALTER TABLE EMPLOYEES MODIFY CONSTRAINTS DNO DISABLE NOVALIDATE 
> 
> Thanks again
> 
> Victor

Hi Victor

You can define Your constraints as deferrable initially deferred, saying the value-foreign key relationship is only evaluated on commit. You can insert into both tables, and on commit the reference is checked. This works only from version 8.1.7 and better :-) Afair.

Regards

/Svend Jensen

Remove the spam killer [S.] in reply address. Received on Tue Jul 08 2003 - 22:33:21 CEST

Original text of this message