creating foreign key - error: key not found

[pitbullblonde]

I am creating a projects database. My first table (Projects) has a primary key of Id, an integer. My next table (Tasks) has a primary key of Id & Seq together, and a foreign key of Id, which references the Id in the projects table. This part works fine. The problem is in adding the third table (Items), which has a primary key of Id, Seq, & Iseq - together. I would like to create a foreign key of Id & Seq to refer back to the Tasks table primary key of Id & Seq but keep getting key not found. I've tried using only one field, but still get the error.

I was hoping to create the following relationships, but can't set the foreign key on the items table - I get Key not found.

Projects Id autoinc - primary key

Tasks Id integer - foreign key to Projects(Id)
Id, Seq integers - primary key

Items Id, Seq integers - foreign key to Tasks(Id,Seq)
Id, Seq, Iseq integers - primary key

Any and all input greatly appreciated!

 

[mirtheil]

What version of Pervasive are you using? What're your CREATE TABLE and CREATE INDEX statements?
Also, how are you issueing the statements (PCC, ODBC Test, Access, etc)?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[pitbullblonde]

I'm on Pervasive version 8.60.192.030
I'm using pervasive control center, sql statements to create the tables:

Projects Table:
SET TRUENULLCREATE=OFF;
Create Table projects using 'ProjMain.frb'
(
Id IDENTITY,
Ptype CHAR( 20 ) Collate "UPPER.ALT",
Pdesc CHAR( 40 ) Collate "UPPER.ALT",
Resp CHAR( 20 ) Collate "UPPER.ALT",
Priority CHAR( 20 ) Collate "UPPER.ALT",
Assign DATE,
Opened DATE,
Closed DATE,
Notes LONGVARBINARY
) ;

Tasks Table:
SET TRUENULLCREATE=OFF;
Create Table Tasks using 'ProjTasks.frb'
(
Id integer references projects(id) on delete cascade,
Seq integer,
Tdesc char( 40 ) Collate "Upper.Alt",
Resp char( 20 ) Collate "Upper.Alt",
Istart date,
Compl date,
Tstat char( 20 ) Collate "Upper.Alt",
Summary longvarbinary
) ;
------------------------------
Create INDEX Xtsk ON Tasks (Id, Seq) ;
------------------------------

Items Table:
SET TRUENULLCREATE=OFF;
Create Table Items using 'ProjItems.frb'
(
Id integer,
Seq integer,
Iseq integer,
Client char( 20 ) Collate "Upper.Alt",
Idate date,
Iowner char( 20 ) Collate "Upper.Alt",
Istat char( 20 ) Collate "Upper.Alt",
Notes longvarbinary
) ;
------------------------------
Create INDEX Xitm ON Items (Id,Seq, Iseq) ;
------------------------------

I first tried the following create table for items, which defines the foreign key:
SET TRUENULLCREATE=OFF;
Create Table Items using 'ProjItems.frb'
(
Id integer,
Seq integer,
Iseq integer,
Client char( 20 ) Collate "Upper.Alt",
Idate date,
Iowner char( 20 ) Collate "Upper.Alt",
Istat char( 20 ) Collate "Upper.Alt",
Notes longvarbinary,
FOREIGN KEY(Id, Seq) REFERENCES Tasks(Id, Seq) ON DELETE CASCADE
) ;
------------------------------
Create INDEX Xitm ON Items (Id,Seq, Iseq) ;
------------------------------

Which gave this message:
ODBC Error: SQLSTATE = S1000, Native error code = -1601
The key was not found.
General error.

I created the items table with the first sql (no foreign key) and then tried the following alters to add the key but still got the same error message (key not found).

ALTER TABLE Items ADD FOREIGN KEY (Id, Seq) REFERENCES Tasks(Id, Seq) ON DELETE CASCADE

ALTER TABLE Items ADD constraint xitm FOREIGN KEY (Id, Seq) REFERENCES Tasks(Id, Seq) ON DELETE CASCADE


Any ideas at to what I'm doing wrong?