Oracle question

genderbender · 02-27-2005, 06:07 PM

Hi, this is more of a question about rules (well sort of). I've wrote an oracle program and it's riddled with errors! I was wondering if I'd be allowed to send in the whole program and get some help with the errors... A lot of the errors are the same sort of errors mainly concerning foreign keys from other tables.

If I get a yes I'll post my code, I've only been using oracle for 3 months and have a few books on it so the codes crap is an understatement :S

Let me know, thanks

jtshaw · 02-27-2005, 08:18 PM

Sure, just put the code up somewhere and post the link. Taring and Compressing it would be nice too if it is big.

genderbender · 02-28-2005, 07:48 AM

OK, its less than 100 lines long. I think the majority of problems are syntax related. I'm sure I wont get much fixed but any help at all would be brilliant including tutorials...

Link is: http://www.thedunwells.co.uk/tim/Veh...20Planning.txt

Thanks for any help

drisay · 02-28-2005, 09:07 AM

i think the order of the create table statements is wrong. upon first view, when you create the CarBooking table, you are creating a foreign key to the Drive table which is being created later on. try reordering your table creations to coincide with the foreign key references.

drisay · 02-28-2005, 09:08 AM

or you can also create all of your tables and then create the foreign keys afterwards.

genderbender · 02-28-2005, 09:36 AM

Wow thanks alot, I'll let you know how it goes. Is the syntax for everything ok then? I get a lot of errors in the first few lines the drop table bit and the errors always point to the name of the tables which is unusual.

Anyway thanks for your help, I hope more people reply I need as much help as possible I'm terrible at oracle

drisay · 02-28-2005, 10:11 AM

i didn't study line for line, but the syntax seems to be ok though.

let me know if reordering works... i'll run your script to see what are the exact errors

drisay · 02-28-2005, 10:13 AM

yep, all your errors seem to be pointing to the ordering. also i got errors about the table not existing on the drop tables which is normal for a first run of that script if the schema didn't exist previously.

genderbender · 02-28-2005, 10:26 AM

I havent got ISQL plus working on this computer for some reason but I get errors at the start like you said and I get this sort of error loads even after reordering:

CONSTRAINT LI_FK FOREIGN KEY (LOCATIONID) references PickUpLocation(LOCATIONID),
*
ERROR at line 6:
ORA-00942: table or view does not exist

I put everything that didnt have a foreign key first and then put everything that did have a foreign key second.

drisay · 02-28-2005, 11:55 AM

try this...

Code:

set autocommit on

DROP TABLE Booking			CASCADE CONSTRAINTS;
DROP TABLE BookingEssentials		CASCADE CONSTRAINTS;
DROP TABLE Car				CASCADE CONSTRAINTS;
DROP TABLE CarBooking			CASCADE CONSTRAINTS;
DROP TABLE Customer			CASCADE CONSTRAINTS;
DROP TABLE Driver			CASCADE CONSTRAINTS;
DROP TABLE Essentials			CASCADE CONSTRAINTS;
DROP TABLE PickUpLocation		CASCADE CONSTRAINTS;
DROP TABLE WeddingPartyMember		CASCADE CONSTRAINTS;
DROP TABLE WPMemberPickup		CASCADE CONSTRAINTS;

create table Car (
REGNO VARCHAR2(7) PRIMARY KEY,
MAKE VARCHAR2(20),
MODEL VARCHAR2(20),
COLOUR VARCHAR2(20),
NOOFSEATS NUMBER(1)DEFAULT 4
);

create table Customer (
CUSTOMERID VARCHAR2(6) PRIMARY KEY,
SURNAME VARCHAR2(30),
FIRSTNAMES VARCHAR2(30),
ADDRESSLN1 VARCHAR2(30),
ADDRESSLN2 VARCHAR2(30),
TOWN VARCHAR2(30),
POSTCODE VARCHAR2(6),
PHONE NUMBER(11),
MOBILE NUMBER(11)
);

create table Driver (
DRIVERID VARCHAR2(6) PRIMARY KEY,
SURNAME VARCHAR2(30),
FIRSTNAMES VARCHAR2(30),
MOBILENO NUMBER(11),
REPLACEMENTDRIVER VARCHAR2(30)
);

create table Essentials (
CODE NUMBER(6) PRIMARY KEY,
DESCRIPTION VARCHAR2(30)
);

create table WeddingPartyMember (
WPMID VARCHAR2(6) PRIMARY KEY,
SURNAME VARCHAR2(20),
FIRSTNAME VARCHAR2(20),
MOBILENO NUMBER(11),
ROLE VARCHAR2(20)
);

create table Booking (
BOOKINGNO VARCHAR2(6) PRIMARY KEY,
CUSTOMERID VARCHAR2(15),
WEDDINGDATE VARCHAR2(10) NOT NULL,
WEDDINGTIME VARCHAR2(7) NOT NULL,
CONSTRAINT fk_booking_customerid FOREIGN KEY(customerid) references Customer(Customerid)
);

create table PickUpLocation (
LOCATIONID VARCHAR2(6) PRIMARY KEY,
PICKUPTIME NUMBER(4) NOT NULL UNIQUE,
BOOKINGNO VARCHAR2(6),
REGNO VARCHAR2(7),
ADDRESSLN1 VARCHAR2(30),
ADDRESSLN2 VARCHAR2(30),
TOWN VARCHAR2(30),
POSTCODE VARCHAR2(6),
PHONE NUMBER(11),
LOCATIONTYPE VARCHAR2(30),
CONSTRAINT fk_pickuplocation_bookingno FOREIGN KEY(bookingno) references Booking(bookingno),
CONSTRAINT fk_pickuplocation_regno FOREIGN KEY(regno) references Car(regno)
);

create table BookingEssentials (
BOOKINGNO VARCHAR2(6) PRIMARY KEY,
CODE NUMBER(6),
CONSTRAINT fk_bookingessentials_bookingno FOREIGN KEY(bookingno) references Booking(bookingno),
CONSTRAINT fk_bookingessentials_code FOREIGN KEY(code) references Essentials(code)
);

create table CarBooking (
BOOKINGNO VARCHAR2(6) PRIMARY KEY,
REGNO VARCHAR2(7),
DRIVERID VARCHAR2(10),
CONSTRAINT fk_carbooking_driverid FOREIGN KEY(driverid) references Driver(driverid),
CONSTRAINT fk_carbooking_regno FOREIGN KEY(regno) references Car(regno),
CONSTRAINT fk_carbooking_bookingno FOREIGN KEY(bookingno) references Booking(bookingno)
);

create table WPMemberPickup (
WPMID VARCHAR2(6) PRIMARY KEY,
LOCATIONID VARCHAR2(6),
PICKUPTIME NUMBER(4),
CONSTRAINT WP_FK FOREIGN KEY (WPMid) references WeddingPartyMember(WPMid),
CONSTRAINT LI_FK FOREIGN KEY (LOCATIONID) references PickUpLocation(LOCATIONID),
CONSTRAINT PU_FK FOREIGN KEY (PICKUPTIME) references PickUpLocation(PICKUPTIME)
);

I ran through the code and there were actually a couple of small syntax errors (too many commas, an extra bracket). Worked fine on my server.

drisay · 02-28-2005, 11:57 AM

on the PickUpLocation table, i modified the PICKUPTIME field to be unique as well... it was creating issues with creating a foreign key to it.

EDIT. looking back on it... i'm not quite sure why you have that foreign key in the WPMember table. cause it doesn't make sense to have the pickuptime unique.

genderbender · 02-28-2005, 06:20 PM

:O Genius! Thats absolutely spot on! I cant be more thankful, thats outstanding

I'm so happy. Thank you so much!

I'm never getting married, not worth the bother...

genderbender · 03-01-2005, 05:36 AM

I messed that up a bit, I havent tried to fix it yet but what I want is the time and location to make one key (I trhink its called a compound key... I forgot). Like you said though missing it out of my wedding party member pick up would be much easier.

drisay · 03-01-2005, 07:19 AM

i think the term is composite key, but i'm not 100% sure.

glad i could help. good luck with the rest of your application.