Re: referencing composite key in create table statement

Cameron wrote:

> Hi,
>
> I'm trying to create a table in which a foriegn key of 3 attributes
> reference a composite key of 3 attributes in another by doing this:
>
> create table student (
> student_id CHAR(8) NOT NULL,
> enroll_tutorial_day VARCHAR2(9),
> enroll_tutorial_time CHAR(5),
> enroll_tutorial_room VARCHAR2(7),
> attend_tutorial_day VARCHAR2(9),
> attend_tutorial_time CHAR(5),
> attend_tutorial_room VARCHAR2(7),
> constraint student_attend_fk foriegn key (attend_tutorial_day,
> attend_tutorial_time, attend_tutorial_room) references Tutorial
> (tutorial_day, tutorial_time, tutorial_room));
>
> and it keeps telling me this:
>
> constraint student_attend_fk foriegn key (attend_tutorial_day,
> attend_tutorial
> *
> ERROR at line 9:
> ORA-00907: missing right parenthesis
>
> I don't understand, - there are ten left parentheses and ten right
> parentheses?
>
> Cameron
>
> P.s. in case you are wondering here is the script until that point:
>
> create table Academic
> (employee_id CHAR(8) NOT NULL,
> surname VARCHAR2(20) NOT NULL,
> first_name VARCHAR2(15) NOT NULL,
> constraint academic_employee_id_pk primary key (employee_id));
>
> create table WeeklyQuestions
> (week_number VARCHAR2(2) NOT NULL,
> employee_id CHAR(8),
> due_datetime DATE,
> constraint weeklyquestions_week_number_pk PRIMARY KEY (week_number),
> constraint weeklyquestions_employee_id_fk FOREIGN KEY (employee_id)
> references Academic (employee_id));
>
> create table Tutorial
> (tutorial_day VARCHAR2(9) NOT NULL,
> tutorial_time CHAR(5) NOT NULL,
> tutorial_room VARCHAR2(7) NOT NULL,
> academic_id CHAR(8),
> constraint tutorial_daytimeroom_ck primary key (tutorial_day,
> tutorial_time, tutorial_room),
> constraint tutorial_academic_id_fk foreign key (academic_id)
> references academic (employee_id));

As you are a student you need to figure this one out on your own. But the appropriate direction in which to look is not at the number of right parenthesis but rather where you have them.

Also it is a very bad idea to specify constraints as you are doing it. This method produces constraints named by the system rather than by the developer making later work (enable, disable, drop) very difficult.

Change your create table to a straight create table BTW you should be specifying extent sizes and pct increase etc. And use ALTER TABLE statements to create your constraints.

Daniel Morgan Received on Thu Aug 29 2002 - 12:41:27 CDT