SQL/Oracle question

[Smangalick]

I'm working on a CIS project right now and having trouble with a little bit of SQL.

It's a workout database (don't ask)...In the 'Routine' table, we have a primary key composed of the 'Routine_name' and 'exercise' fields. In the 'Schedule' table, we want to reference the routine.routine_name field (as a foreign key), but the Oracle server doesn't seem to like it.

here's my code for the create tables....

CREATE TABLE SCHEDULE
(WO_DATE DATE,
CLIENT_ID CHAR(4),
ROUTINE_NAME CHAR(15),
PRIMARY KEY (WO_DATE, CLIENT_ID),
FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT,
FOREIGN KEY (ROUTINE_NAME) REFERENCES ROUTINE);

CREATE TABLE ROUTINE
(ROUTINE_NAME CHAR(25),
EXERCISE CHAR(35),
FOREIGN KEY (EXERCISE) REFERENCES EXERCISE,
primary key (routine_name, exercise));

When i run the dump file for create schedule, this is the error message that comes back at me:

FOREIGN KEY (ROUTINE_NAME) REFERENCES ROUTINE)
ERROR at line 7:
ORA-02256: number of referencing columns must match referenced columns

We've trying to accomplish this without having to add an 'exercise' field to the 'schedule' table, because it gets redundant.
Any help would be greatly appreciated....thanks in advance!

 

[GtPrOjEcTX]

problem definately is that your p.key is a combo of the two fields in the schedule table while you're trying to make a foreign key of it just 1 field. f.key and p.key MUST match.

how about having a foreign key in the schedule table to match with a primary key in the routine table?

 

[Haircut]

If adding an exercise column to the schedule table is redundant then how about creating a routine_id that is primary key in the routines table that you can reference as foreign key in the schedule table?

Also, in Oracle I would use Varchar2 as the datatype for text fields, rather than Char as a Char(50) field that contains the text 'A' will still take 50 bytes, whereas a Varchar2 takes up a variable amount of space and would just take the 1 byte for the same text.

 

[Smangalick]

I was going to use a routine_id, but the problem with using that as a primary key is that it's not unique.

The routine table is gonna hold sample info like this:

routine_name-exercies
legs - leg extension
legs - leg press

and so on..so the routine_name (or routine_id) field will be listed multiple times.

but i'm gonna try the varchar suggestion out, and see if it improves efficiency. thanks for the ideas, keep them coming!

 

[Haircut]

Ah, OK I see what the data is like now.

If I am interpreting the data correctly you have a named routine that consists of several exercises and you want the routine to be foreign key in the schedule table.

To get this properly normalised I would have your routine table be:
CREATE TABLE ROUTINE
(ROUTINE_ID NUMBER
,ROUTINE_NAME VARCHAR2(25)
,PRIMARY KEY (ROUTINE_ID)
);

and then create another table routine_exercises

CREATE TABLE ROUTINE_EXERCISES
(ROUTINE_ID NUMBER
,EXERCISE_ID NUMBER
,PRIMARY KEY (ROUTINE_ID,EXERCISE_ID)
,FOREIGN KEY (ROUTINE_ID)
REFERENCES ROUTINE (ROUTINE_ID)
,FOREIGN KEY (EXERCISE_ID)
REFERENCES EXERCISE (EXERCISE_ID)
);

You can then use routine_id in the routine table for your foriegn key in schedule.

Hope this helps

 

[Smangalick]

I think that's it!!!!

Thanks a bunch.

 

[Smangalick]

alright...the answer above worked like a charm..but now i have another question relating to the same databasee.

Here's the table(s) in question...

CREATE TABLE CLIENT
(CLIENT_ID CHAR(4) PRIMARY KEY,
FIRST CHAR(10) NOT NULL,
LAST CHAR(12) NOT NULL,
password char(12));

CREATE TABLE SCHEDULE
(WO_DATE DATE,
CLIENT_ID CHAR(4),
ROUTINE_ID CHAR(4),
PRIMARY KEY (WO_DATE, CLIENT_ID),
FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT,
FOREIGN KEY (ROUTINE_ID) REFERENCES ROUTINE);

CREATE TABLE PERFORMANCE
(WO_DATE DATE,
CLIENT_ID CHAR(4),
ROUTINE_ID CHAR(4),
EXERCISE CHAR(35),
SET_NUMBER INTEGER,
REPS INTEGER NOT NULL,
WEIGHT INTEGER NOT NULL,
PRIMARY KEY (WO_DATE, CLIENT_ID, EXERCISE, SET_NUMBER),
FOREIGN KEY (ROUTINE_ID) REFERENCES ROUTINE,
foreign key (wo_date, client_id) references schedule);

The query needs to be able to access the max weight for each exercise, and the number of reps done at that weight. When I try this query:

select client.last, client.first, schedule.wo_date, performance.exercise,performance.weight,
performance.reps
from client, schedule, performance
where client.last = 'Richard'
and schedule.client_id = performance.client_id
and schedule.wo_date = '16-March-2003'
and performance.weight =
(select max(weight) from performance
where client_id = '0004');

All i get is the max weight done for the entire workout, not for each specific exercise. Is there a function where I can group the sets from each exercise together, and then return a max weight for each exercise?

thanks in advance...

 

[Smangalick]

anybody?

 

[Haircut]

You can use the GROUP BY function to group items by certain criteria, I don't think you need to do this though
The way you have done it, it will always return the maximum weight for the workout as your subquery doesn't specify exercise at all. There will be multiple values in the performance table where client_id = '0004'.
Maybe something like this would work:

SELECT clnt.LAST
,clnt.FIRST
,schd.wo_date
,perf.exercise
,perf.weight
,perf.reps
FROM client clnt
,schedule schd
,performance perf
WHERE clnt.client_id = '0004'
AND schd.client_id = perf.client_id
AND schd.wo_date = '16-March-2003'
AND perf.weight =
(SELECT MAX(weight)
FROM performance perf
WHERE perf.client_id = clnt.client_id
AND perf.exercise = 'Put Exercise in here'
);