Database

Tomas_S

Hi, I'm totally new to databases and trying create something here.
All I'm getting though is ERROR: relation "........" does not exist.
If someone could shed some light would be great :cool:

Here's an example what i have:

CREATE TABLE clinic (
Clinic_no integer NOT NULL,
Address character varying(30) NOT NULL,
Tel numeric(10),
Fax numeric(10),
Manager_pps character(20),
Num_of_staff integer,
PRIMARY KEY (Clinic_no),
FOREIGN KEY (Manager_pps) REFERENCES staff (Staff_no)
);



CREATE TABLE staff (
Staff_no integer NOT NULL,
Clinic_no integer NOT NULL,
Fname character varying (10) NOT NULL,
Lname character varying (10) NOT NULL,
Address character varying (30),
Tel numeric (10),
Dob date,
Sex character (1),
Pps character (8) NOT NULL,
Position character varying (15),
Salary decimal (7,2),
PRIMARY KEY (Staff_no),
FOREIGN KEY (Clinic_no) REFERENCES clinic (Clinic_no)
);

hearny

FOREIGN KEY (Manager_pps) REFERENCES staff (Staff_no)
This is referencing a field that does not exist yet.

What type of database is it (i.e. Mysql / Oracle etc.)

hearny

also post the full error message

Tomas_S

It's postgresql. I'm getting these errors on all tables, where primary and foreign keys match.

Tomas_S

vet_clinic.sql:44: ERROR: relation "clinic" does not exist

Getting these for every table

hearny

Not sure of the syntax on postgreSQL but you should have the table name that the foreign key is in i.e. staff.Staff_no

Make sure the table exists before you try to reference it.

Tomas_S

Supposed to be just simple sql code thats all. And I'm sure nearly all keys and tables should be ok

hearny

yeah sorry I see it now take out the space for the references part i.e. clinic(Clinic_no)

Tomas_S

Not helping

Boskowski

Why would you want to reference the tables each other twice anyway?!?
Look up relational database concepts. Your table design makes no sense really.
On a more practical note if you're going to reference something through an fk relationship it will have to exist first.

hearny

Make sure the tables do not exist already.

Then start pasting the create table commands one at a time.

Post the SQL statement and full error message when you encounter an error.

lollisue

As has already been said it doesn't make sense. How will you insert the first row because you can't insert into the first table unless a row exists in the second and vice versa.

Also you have the foreign key on clinic against the pps column which is a character field but the staff no field on the other table is an integer so that doesn't make sense.

Tomas_S

Boskowski wrote: »

Why would you want to reference the tables each other twice anyway?!?
Look up relational database concepts. Your table design makes no sense really.
On a more practical note if you're going to reference something through an fk relationship it will have to exist first.

Can you please explain a bit more the referencing twice?

nicol

Not sure what is causing the problem but as previously mentioned manager pps should be changed to manager staff number and get rid of number of staff field on the clinic table, you should be able to figure that out in a query against the staff table. Good luck figuring out the problem!

Boskowski

Tomas_S wrote: »

Can you please explain a bit more the referencing twice?

Sure.
You have table clinic referencing staff and also staff referencing clinc. That is obsolete & redundant and may even be impossible.

I havent the time to do a proper analysis of your design but shooting from the hip I'd say remove the manger relationship from clinic, give staff a type code, and enforce only one manager type code can exist per clinic on the staff table. From what I see I assume that's what you want.

Sherfin

Never used postgresql myself (and not being smart) but do you understand the concept of foreign keys ?

You are using
FOREIGN KEY (Manager_pps) REFERENCES staff (Staff_no)
This means the value of Manager_pps must match a valid record (Staff_no) in the staff table.
To match up they must be of the same type which in your case they're not,
try changing your manager_pps to an int which will match

Tomas_S

Ok I'm putting a bit more code, could someone point what's the biggest mistake here? It's my first attempt so please be nice

Oh and them 2 fields difference was a silly mistake after changing things around.

Tomas_S

CREATE TABLE pet_owner (
Owner_no integer NOT NULL,
Clinic_no integer NOT NULL,
Fname character varying (10) NOT NULL,
Lname character varying (10) NOT NULL,
Address character varying (30),
Tel numeric (10),
PRIMARY KEY (Owner_no),
FOREIGN KEY (Clinic_no) REFERENCES clinic(Clinic_no)
);

CREATE TABLE pet (
Pet_no integer NOT NULL,
Clinic_no integer NOT NULL,
Pet_name character varying (10) NOT NULL,
Description character varying (40),
Dob date NOT NULL,
Reg_date date NOT NULL,
Status character (1) NOT NULL,
Owner_no integer NOT NULL,
Pet_type character varying (20),
PRIMARY KEY (Pet_no),
FOREIGN KEY (Clinic_no) REFERENCES clinic(Clinic_no)
);

CREATE TABLE examinations (
Exam_no numeric (10) NOT NULL,
Exam_date date NOT NULL,
Exam_time time,
Vets_name character varying (20) NOT NULL,
Pet_no integer NOT NULL,
Pet_type character varying (20),
Pet_name character varying (10) NOT NULL,
Results_descript character varying (50),
Treatm_no integer NOT NULL,
PRIMARY KEY (Exam_no),
FOREIGN KEY (Pet_no) REFERENCES pet(Pet_no)
);

Boskowski

I'll have a look some time later tonight. For a full blown table design I'd have to bill you though.

Tomas_S

Boskowski wrote: »

I'll have a look some time later tonight. For a full blown table design I'd have to bill you though.

It all depends on the size of the bill

Sherfin

Looks better, just a couple of points

In the owners table you have
FOREIGN KEY (Clinic_no) REFERENCES clinic(Clinic_no)
Will there be more than one clinic in the clinic table?
If so, this ties the owner to one clinic only, (which may be what you want.)
If the owner has to visit a second clinic then they would need a second record in the owners table or to change their clinic_no which would break older records.
If there's only one clinic then I'd say it's not really necessary to have this fk.

You have also used the same fk in the pets table but surely it would make sense to link the pet to the owner (using a fk) rather than the clinic ?

Tomas_S

Sherfin wrote: »

Looks better, just a couple of points

In the owners table you have
FOREIGN KEY (Clinic_no) REFERENCES clinic(Clinic_no)
Will there be more than one clinic in the clinic table?
If so, this ties the owner to one clinic only, (which may be what you want.)
If the owner has to visit a second clinic then they would need a second record in the owners table or to change their clinic_no which would break older records.
If there's only one clinic then I'd say it's not really necessary to have this fk.

You have also used the same fk in the pets table but surely it would make sense to link the pet to the owner (using a fk) rather than the clinic ?

supposed to be few clinics with different numbers in the clinic table. So yes the owner belongs to a clinic with particular num.
Could I link a pet to owner and clinic?

Sherfin

Tomas_S wrote: »

supposed to be few clinics with different numbers in the clinic table. So yes the owner belongs to a clinic with particular num.
Could I link a pet to owner and clinic?

Yeah, of course you can, but one of the main things about relational db's is not having to duplicate data, and in this case the pet has an owner and the owner is already linked to a clinic so you have the clinic for the pet already.
Another way of looking at it is what if the owner has 3 pets? You will be recording the clinic number 4 times (owner + pets) and if the owner changed clinics it will have to be changed in all 4 records rather than once just for the owner

Boskowski

A simple table design without redundancies would be:

CREATE TABLE clinic (
Clinic_no integer NOT NULL,
...
PRIMARY KEY (Clinic_no),
);

CREATE TABLE staff (
Staff_no integer NOT NULL,
Clinic_no integer NOT NULL,
...
PRIMARY KEY (Staff_no),
FOREIGN KEY (Clinic_no) REFERENCES clinic (Clinic_no)
);

CREATE TABLE pet_owner (
Owner_no integer NOT NULL,
...
PRIMARY KEY (Owner_no),
);

CREATE TABLE pet (
Pet_no integer NOT NULL,
Owner_no integer NOT NULL,
...
PRIMARY KEY (Pet_no),
FOREIGN KEY (Owner_no) REFERENCES pet_owner(Owner_no)
);

CREATE TABLE examinations (
Exam_no integer NOT NULL,
Clinic_no integer NOT NULL,
Pet_no integer NOT NULL,
...
PRIMARY KEY (Exam_no),
FOREIGN KEY (Clinic_no) REFERENCES clinic (Clinic_no)
FOREIGN KEY (Pet_no) REFERENCES pet(Pet_no)
);

Depending on your requirements and how sophisticated you want your design to be you can break out addresses etc.