SQL and Oracle

[woodpecker 0]

Any SQL junkies on here?

I'm getting hit with a couple errors and have no idea why. Coding mimics another project I did with no errors.

Did I mention I hate code?

SONIC WOODY #146

There is a fine line between cockiness and confidence -- which side of the line are you on?

[Bertt 0]

You didn't say what errors, but in general, you can get SQL errors from incorrect data even if the code itself is fine. A very small test database is helpful if you have one or can whip one up quickly.

You don't have to outrun the bear.

[woodpecker 0]

I'm getting parent key not found and integrity constraint mostly. Here is the coding if your able to run it through oracle or another system.

set echo on;
-- spool tablecreation;
/*
Project DataProject Data
PKs and FKs Added
*/
/*------------------------------------------------------------------*/
set echo on;
/*-----------------------------------------------------------------------*/
DROP TABLE Supplier;
DROP TABLE Item;
DROP TABLE Employee;
DROP TABLE Department;
/*-----------------------------------------*/
CREATE TABLE Department
(
Dept_No VARCHAR2 (4),
Dept_Name VARCHAR2 (20),
Manager VARCHAR2 (30),
primary key (dept_No)
);
INSERT INTO Department VALUES('1', 'Produce', 'Crystal Smith');
INSERT INTO Department VALUES('2', 'Meat', 'Amanda Peets');
INSERT INTO Department VALUES('3', 'Dairy', 'John Alans');
INSERT INTO Department VALUES('4', 'Cleaning', 'Dan Ulrich');
INSERT INTO Department VALUES('5', 'Bakery', 'Missy Romero');
INSERT INTO Department VALUES('6', 'Deli', 'Barry Stuart');
INSERT INTO Department VALUES('7', 'Pasta', 'Sam Sheppard');
INSERT INTO Department VALUES('8', 'Ethnic', 'Alison Gunn');
INSERT INTO Department VALUES('9', 'Paper Goods', 'Tom Bolling');
INSERT INTO Department VALUES('10', 'Frozen', 'Erik Montague');
/*------------------------------------------------------------------*/
CREATE TABLE Employee
(
Name VARCHAR2 (30),
Salary NUMBER (10,2),
Dept VARCHAR2 (20),
primary key (name),
foreign key (dept) references department
);
INSERT INTO EMPLOYEE VALUES('Crystal Smith', '42000.00', 'Produce');
INSERT INTO EMPLOYEE VALUES('Amanda Peets', '38000.00', 'Meat');
INSERT INTO EMPLOYEE VALUES('John Alans', '34000.00', 'Dairy');
INSERT INTO EMPLOYEE VALUES('Erik Montague', '34000.00', 'Frozen');
INSERT INTO EMPLOYEE VALUES('Dan Ulrich', '32000.00', 'Cleaning');
INSERT INTO EMPLOYEE VALUES('Alison Gunn', '31500.00', 'Ethnic');
INSERT INTO EMPLOYEE VALUES('Tom Bolling', '36400.00', 'Paper Goods');
INSERT INTO EMPLOYEE VALUES('Sam Sheppard', '29700.00', 'Pasta');
INSERT INTO EMPLOYEE VALUES('Barry Stuart', '39600.00', 'Deli');
INSERT INTO EMPLOYEE VALUES('Missy Romero', '42000.00', 'Bakery');
/*------------------------------------------------------------------*/
CREATE TABLE Item
(
Supplier VARCHAR2 (20),
Item_No VARCHAR2 (10),
Price NUMBER (7,2),
Name VARCHAR2 (20),
Dept VARCHAR2 (20),
primary key (supplier, item_no),
foreign key (supplier) references supplier,
foreign key (dept) references department
);
INSERT INTO ITEM VALUES('Apple Mist', '7584937', '1.62', 'Rome Beuty', 'Produce');
INSERT INTO ITEM VALUES('Cowboys', '205763', '7.92', 'Ribeye', 'Meat');
INSERT INTO ITEM VALUES('Utters', '2359', '4.98', 'Whole Milk', 'Dairy');
INSERT INTO ITEM VALUES('Snowmans', '1860385', '10.29','Pizza', 'Frozen');
INSERT INTO ITEM VALUES('Smiths', '7593', '6.99', 'Floor Gel', 'Cleaning');
INSERT INTO ITEM VALUES('LeeWong', '49026', '2.47', 'Rice', 'Ethnic');
INSERT INTO ITEM VALUES('Talley', '333876', '17.45','Toilet Paper', 'Paper Goods');
INSERT INTO ITEM VALUES('Rosas', '9842369', '11.72','Bowtie', 'Pasta');
INSERT INTO ITEM VALUES('Fine Cut', '3927568', '14.03','Cajun Turkey', 'Deli');
INSERT INTO ITEM VALUES('Mrs. Bairds', '52075', '2.87', 'White Split', 'Bakery');
/*------------------------------------------------------------------*/
CREATE TABLE Supplier
(
Name VARCHAR2 (20),
Phone_Number VARCHAR2 (12),
primary key (name)
);
INSERT INTO SUPPLIER('Apple Mist', '509-298-5309');
INSERT INTO SUPPLIER('Cowboys', '714-700-4309');
INSERT INTO SUPPLIER('Fine Cut', '505-398-5629');
INSERT INTO SUPPLIER('LeeWong',' '594-921-5583');
INSERT INTO SUPPLIER('Mrs. Bairds', '555-252-0921');
INSERT INTO SUPPLIER('Rosas', '210-234-7329');
INSERT INTO SUPPLIER('Smiths', '246-723-7792');
INSERT INTO SUPPLIER('Snowmans', '679-094-1798');
INSERT INTO SUPPLIER('Talley', '555-555-2989');
INSERT INTO SUPPLIER('Utters', '932-443-6412');
/*------------------------------------------------------------------*/

describe department;
select * from department;

describe employee;
select * from employee;

describe item;
select * from item;

describe supplier;
select * from supplier;

-- spool off;

SONIC WOODY #146

There is a fine line between cockiness and confidence -- which side of the line are you on?

[zen_mtn_climber 0]

Looks to me like this line below

Quote

foreign key (supplier) references supplier,

is your problem because it come before the creation of the the supplier table which is done with this snippet

Quote

CREATE TABLE Supplier
(
Name VARCHAR2 (20),
Phone_Number VARCHAR2 (12),
primary key (name)
);
INSERT INTO SUPPLIER('Apple Mist', '509-298-5309');
INSERT INTO SUPPLIER('Cowboys', '714-700-4309');
INSERT INTO SUPPLIER('Fine Cut', '505-398-5629');
INSERT INTO SUPPLIER('LeeWong',' '594-921-5583');
INSERT INTO SUPPLIER('Mrs. Bairds', '555-252-0921');
INSERT INTO SUPPLIER('Rosas', '210-234-7329');
INSERT INTO SUPPLIER('Smiths', '246-723-7792');
INSERT INTO SUPPLIER('Snowmans', '679-094-1798');
INSERT INTO SUPPLIER('Talley', '555-555-2989');
INSERT INTO SUPPLIER('Utters', '932-443-6412');
/*------------------------------------------------------------------*/

Try moving the creation of the supplier table above the creation of the item table and see if your problems go away . .

Cheers,
K

"Whatever the future holds down the road, being true to yourself is something you won't ever regret doing. " - airtwardo

[woodpecker 0]

still getting parent key and missing select keyword errors.

The first table it fine, its the three after that are giving me the problems.

SONIC WOODY #146

There is a fine line between cockiness and confidence -- which side of the line are you on?

[zen_mtn_climber 0]

What happens if you try just create the first two tables (department and employee)?

"Whatever the future holds down the road, being true to yourself is something you won't ever regret doing. " - airtwardo

[woodpecker 0]

same thing:

parent key not found.

SONIC WOODY #146

There is a fine line between cockiness and confidence -- which side of the line are you on?

[zen_mtn_climber 0]

Ahh . . think I see it . . in the department table, dept_no is the primary key but your foreign key constraint is with department name in the employee table. I dont play in Orcale . . MS SQL server and MySQL myself but IIRC, a foreign key contraint will try to cross-check the foreign key in the table you are creating (In this case employee table) with the primary key of the referenced table (department table). Try making the department name the key in department table and see if the first two tables will create without error.

"Whatever the future holds down the road, being true to yourself is something you won't ever regret doing. " - airtwardo

[woodpecker 0]

Okay, so now the only error I'm getting is "Table doesnt exist" for the last 3 tables.

SONIC WOODY #146

There is a fine line between cockiness and confidence -- which side of the line are you on?

[zen_mtn_climber 0]

Do you still have the code to create the supplier table after the code which creates the item table?

The create statement for the item table contains a foreign key constraint against the supplier table. So the supplier table has to be created before the the item table.

I'm off to bed . . really early morning of sys admin work for me. If you still have problems getting things running PM me in the morning and if I get some time tomorrow I'll drop your SQL code into the SQL Server in my test environment and see if I can pick out the last prob.

Hope it works out
K

"Whatever the future holds down the road, being true to yourself is something you won't ever regret doing. " - airtwardo

[marcandalysse 0]

Here's the bible!
http://www.oracle.com/pls/db112/homepage

And here is a create table example from that:

Example 2-1 CREATE TABLE employees

CREATE TABLE employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
) ;
------------------------------------------------------
Add your referential integrity constraints AFTER creating the table, example:

ALTER TABLE employees
ADD ( CONSTRAINT emp_emp_id_pk
PRIMARY KEY (employee_id)
, CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
, CONSTRAINT emp_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
, CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees
) ;

"The reason angels can fly is that they take themselves so lightly." --GK Chesterton