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;

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

pootle flump ur codings are working excelent.

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.