Error ORA-02291: integrity constraint

Question

I keep getting this error when trying to populate my DB tables. This is the code to create my tables:

CREATE table tbcustomer (
    customerid char(4) not null constraint rg_customerid check (customerid between '1000' and '4999') constraint pk_customer primary key,
    customername varchar2(40) not null,
    customeraddress varchar2(50) null,
    customercity varchar2(30) null,
    customerstate char(2) null,
    customerzip varchar2(10) null,
    customercontact varchar2(30) null,
    customerphone varchar2(12) null,
    customeremail varchar2(50) null);     


CREATE table tborder (
    orderno number(11,0) not null constraint pk_order primary key,
    orderdate date not null,
    customerid char(4) not null constraint fk_customerid_tborder references tbcustomer on delete cascade);


CREATE table tbproduct (
    productid char(3) not null constraint pk_product primary key constraint rg_productid check (productid between '100' and '999'),
    productname varchar2(30) not null,
    budgetsales number(4,0));


CREATE table tbvendor (
    vendorid char(4) not null constraint pk_vendor primary key constraint rg_vendorid check (vendorid between '5000' and '9999'),
    vendorname varchar2(25) not null,
    vendoraddress varchar2(50) null,
    vendorcity varchar2(30) null,
    vendorstate char(2) null,
    vendorzip varchar2(10) null);

CREATE table tbitem (
    productid char(4) not null, 
    vendorid char(4) not null, 
    itemprice number(10,2) null constraint rg_itemprice check (itemprice>=0.00),
    qoh number(8,0) not null,
    constraint fk_productid_tbitem foreign key (productid) references tbproduct (productid),
    constraint fk_vendorid foreign key (vendorid) references tbvendor (vendorid),
    constraint pk_item primary key (productid, vendorid));


CREATE table tborderitem (
    orderno number(11,0) not null constraint fk_orderno_tbborderitem references tborder (orderno) on delete cascade,
    orderitemno char(2) not null,
    productid char(3) not null,
    vendorid char(4) not null,
    quantity number(4,0) not null,
    itemprice number(10,2) null,
    constraint pk_orderitem primary key (orderno, orderitemno));
    constraint fk_productid_vendorid foreign key (productid, vendorid) references tbitem (productid, vendorid) on delete cascade);

And this the code I use to populate my tables:

INSERT  into tbcustomer values ('1123','Z Best','123 Main Street','Cambridge','MA','02139','Carol Jenkins','617-555-2222','jenskinssc@abc.com');
INSERT  into tbcustomer values ('1234','Pop Shop','2233 Spring Street','Boston','MA','02114','Mandy Peters','617-344-1111','mpeters@def.com');
INSERT  into tbcustomer values ('1667','Zoom','4545 Winter Street','Boston','MA','02112','James Hughes','617-433-3333','jhughes@zoomco.com');


INSERT into tbvendor values ('5100','Wesell','233 South Willow Street','Manchester','NH','03102');
INSERT into tbvendor values ('5200','Givin', '33 Harvard Place','Boston','MA','02211');
INSERT into tbvendor values ('5300','Z A List','4500 Summer Street','Quincy','MA','02161');



INSERT into tbproduct values ('100','Microwave','40');
INSERT into tbproduct values ('121','Toaster','30');
INSERT into tbproduct values ('434','Steamer','40');
INSERT into tbproduct values ('677','Coffee Maker','20');



INSERT into tborder values ('1','10-OCT-12','1667');
INSERT into tborder values ('2','12-OCT-12','1234');
INSERT into tborder values ('3','13-OCT-12','1667');



INSERT into tbitem values ('100','5100','55','22');
INSERT into tbitem values ('100','5200','66','20');
INSERT into tbitem values ('100','5300','70','35');
INSERT into tbitem values ('121','5100','12','20');
INSERT into tbitem values ('121','5300','15','15');
INSERT into tbitem values ('434','5100','18','35');
INSERT into tbitem values ('434','5200','25','25');
INSERT into tbitem values ('677','5100','40','20');
INSERT into tbitem values ('677','5200','46','30');
INSERT into tbitem values ('677','5300','48','20');




INSERT into tborderitem values ('1','01','100','5300','5','70');
INSERT into tborderitem values ('1','02','100','5100','5','55');
INSERT into tborderitem values ('1','03','121','5100','5','12');
INSERT into tborderitem values ('2','01','100','5200','10','65');
INSERT into tborderitem values ('2','02','677','5300','5','48');
INSERT into tborderitem values ('3','01','121','5100','5','12');
INSERT into tborderitem values ('3','02','677','5300','3','45');
INSERT into tborderitem values ('3','03','100','5100','2','55');

When I run the script I keep getting this error when inserting values to the tbitem table:

60-SQL>INSERT into tbitem values ('677','5300','48','20');
INSERT into tbitem values ('677','5300','48','20')
*
ERROR at line 1:
ORA-02291: integrity constraint (MQUIJANO.FK_PRODUCTID_TBITEM) violated -parent key not found

I also get the same error when trying to insert values into the tborderitem:

60-SQL>INSERT into tborderitem values ('1','01','100','5300','5','70');
INSERT into tborderitem values ('1','01','100','5300','5','70')
*
ERROR at line 1:
ORA-02291: integrity constraint (MQUIJANO.FK_PRODUCTID_VENDORID) violated -parent key not found

How can I fix this?

Answer 1

The problem may be that you do not have the same field types for your keys in the first example. Notice that in tbproduct you said:

productid char(3)

And in tbitem you said:

productid char(4)

And again in border item you said:

productid char(3)

Try changing the tbitem table to have productid with a length of 3.