Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Posts
    20

    ERROR: The join order does not match the key column order.

    A simple database but get above error when check model in PowerDesigner12 for FK_Reference_1. don't know why, need your experts explain. thanks,


    drop table if exists Customer;

    drop table if exists OrderItems;

    drop table if exists PartsOrder;

    /*================================================= =============*/
    /* Table: Customer */
    /*================================================= =============*/
    create table Customer
    (
    custId int not null,
    firstName varchar(20),
    lastName varchar(20),
    primary key (custId)
    );

    /*================================================= =============*/
    /* Table: OrderItems */
    /*================================================= =============*/
    create table OrderItems
    (
    orderId int not null,
    partNo varchar(20) not null,
    itemNo int,
    description varchar(50),
    unitPrice float,
    quantity float,
    itemTotalPrice float,
    brand varchar(20),
    primary key (orderId, partNo)
    );

    /*================================================= =============*/
    /* Table: PartsOrder */
    /*================================================= =============*/
    create table PartsOrder
    (
    orderId int not null,
    custId int not null,
    receiveDate date,
    orderDate date,
    dueDate date,
    totalPrice float,
    totalItems float,
    totalUnits int,
    status varchar(10),
    primary key (orderId, custId)
    );

    alter table OrderItems add constraint FK_Reference_1 foreign key (orderId)
    references PartsOrder (orderId) on delete restrict on update restrict;

    alter table PartsOrder add constraint FK_Reference_2 foreign key (custId)
    references Customer (custId) on delete restrict on update restrict;

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well the error message doesn't seem to relate to it but your PK for PartsOrder looks wrong. Can multiple customers really have the same orderid?

    That PK issue would then cause a problem when you define the foreign key FK_Reference_1
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You have:

    create table PartsOrder
    (
    ...
    primary key (orderId, custId)
    );

    alter table OrderItems add constraint FK_Reference_1 foreign key (orderId)
    references PartsOrder (orderId) ...

    A foreign key has to reference the whole of a primary or unique key on the parent table.

Posting Permissions

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