Insert Value Error

[Victor M.]

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 again

Victor

[Anna C. Dent]

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 again

Victor

1) DNUMBER is a "INTEGER" & therefore does NOT require signle quote
marks around it.
2) SSN is defined as CHAR(9), but count the characters!
1965-01-09
1234567890
Exactly how do you proposed to jam 10 characters into
a field that holds only NINE?
3) What prevents you from disabling one or both PK/FK pairs
while loading the data?
4) Ask the application/table designer how to load the tables!

[Victor M.]

In article <Q6pOa.120391$MJ5.69016@fed1read03>, an*******@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