Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2006
    Posts
    1

    Unanswered: no matching unique or primary key (was "Help")

    Hello,

    I am at university at the moment and one of my modules is application development and databases. I am having some problems with one of the SQL scripts that I have to do and I was wondering, if it's not too much trouble, if someone could show me where I'm going wrong. The script is as follows:

    drop table task;
    drop table stage;
    drop table task_type;
    drop table project;
    drop table employee;
    drop table grade;


    create table grade
    (
    grade_no number(1) primary key,
    description varchar2(50),
    rate_per_hour number(6,2)
    );

    create table employee
    (
    employee_no number(4) primary key,
    surname varchar2(50),
    first_name varchar2(50),
    grade_no number(1),
    foreign key (grade_no) references grade(grade_no)
    );


    create table task_type
    (
    task_type_no number(3) primary key,
    description varchar2(50),
    grade_no number(1),
    foreign key (grade_no) references grade(grade_no)
    );

    create table project
    (
    project_no number(3) primary key,
    project_code varchar(10),
    client char(30),
    description varchar2(50),
    manager number(4),
    foreign key (manager) references employee(employee_no)
    );

    create table stage
    (
    project_no number(3),
    stage_no number(1),
    description varchar2(50),
    start_date date,
    planned_duration_days number(3),
    fees_basis char(2),

    constraint fees_basis check(fees_basis in ('FQ','VS','VN','CS','CN')),

    primary key (project_no, stage_no),
    foreign key (project_no) references project(project_no)
    );

    create table task
    (
    project_no number(3),
    stage_no number(1),
    task_no number(2),
    description varchar2(50),
    estimated_hours number (6,1),
    planned_start_date date,
    maximum_duration number(3),
    task_type_no number(2),
    primary key (project_no,task_no,stage_no),
    foreign key (task_type_no) references task_type(task_type_no)
    );

    create table assignment
    (
    assignment_no number(2) primary key,
    project_no number(2),
    stage_no number(1),
    task_no number(2),
    employee_no number(4),
    estimated_hours number(6,1),

    foreign key (project_no) references project (project_no),
    foreign key (stage_no) references task(stage_no),
    foreign key (task_no) references task (task_no),
    foreign key (employee_no) references employee(employee_no)
    );

    create table vist
    (
    visit_no number(3) primary key,
    visit_date date,
    actual_hours number(6,1),
    chargeable number(2),
    assignment_no number(2),

    foreign key (assignment_no) references assignment(assignment_no)
    );

    create table expense
    (
    expense_no number(4) primary key,
    description varchar2(50),
    quantity number(4),
    unit_cost number(6,2),
    chargeable number(6,2),
    project_no number(2),

    foreign key (project_no) references assignment(project_no))
    );


    And the reply that I am getting from the server is as follows:

    Table dropped.

    Table dropped.

    Table dropped.

    Table dropped.

    Table dropped.

    Table dropped.

    Table created.

    Table created.

    Table created.

    Table created.

    Table created.

    Table created.


    foreign key (stage_no) references task(stage_no),
    *

    ERROR at line 11:
    ORA-02270: no matching unique or primary key for this column-list

    foreign key (assignment_no) references assignment(assignment_no)
    *

    ERROR at line 9:
    ORA-00942: table or view does not exist

    )
    *

    ERROR at line 11:
    ORA-00922: missing or invalid option

    If anyone could help it would be greatly appreciated.

    Thanks a lot

    Dave

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the message is pretty clear about what's wrong

    you cannot create a foriegn key unless it references either a primary key or a unique key

    if you look at the task table, you will see task_no as part of the primary key, but this means that there could be many rows in the task table with the same task_no

    in the assignment table, you should make a composite foreign key that matches the primary key of the task table

    oh, and by the way, this isn't mysql, i'll move this thread to the oracle forum for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •