why does this create tabel script not work ?

[messi]

hello,

im trying to convert an access database to an interbase database.. therefor, i first want to create the tables in ib and then add the contents.. but im not really able to create the db.. here is my script:

CREATE TABLE Problems(

Probleem_nr NUMERIC(10) NOT NULL,

Machine_id VARCHAR(40) NOT NULL,

Reported_by VARCHAR(40) NOT NULL,

Tracker_id VARCHAR(40) NOT NULL,

Category_id VARCHAR(40) NOT NULL,

Openend DATE NOT NULL,

Description VARCHAR(255) NOT NULL,

Status VARCHAR(6) NOT NULL,

Closed DATE,

Actions VARCHAR(255),

Short_Description VARCHAR(255) NOT NULL,

PRIMARY KEY (Probleem_nr)

);



CREATE TABLE Action_regel(

Probleem_nr NUMERIC(10) NOT NULL,

Action_id NUMERIC(10) NOT NULL,

PRIMARY KEY (Probleem_nr, Action_id),

FOREIGN KEY (Probleem_nr) REFERENCES Problems (Probleem_nr)

);



CREATE TABLE Actions(

Action_id NUMERIC(10) NOT NULL,

omschrijving VARCHAR(255) NOT NULL,

PRIMARY KEY (Action_id),

FOREIGN KEY (Action_id) REFERENCES Action_regel (Action_id)

);



CREATE TABLE Machine_id(

Machine_id VARCHAR(40) NOT NULL,

Ethernet_number VARCHAR(40),

Location VARCHAR(40),

PRIMARY KEY (Machine_id),

FOREIGN KEY (Machine_id) REFERENCES Problems (Machine_id)

);



CREATE TABLE Tracker(

Tracker_id VARCHAR(20) NOT NULL,

Full_name VARCHAR(50) NOT NULL,

FUnction VARCHAR(50) NOT NULL,

PRIMARY KEY (Tracker_id),

FOREIGN KEY (Tracker_id) REFERENCES Problems (Tracker_id)

);



CREATE TABLE Category(

Category_id VARCHAR(40) NOT NULL,

Description VARCHAR(255),

upsize_ts VARCHAR(255),

PRIMARY KEY (Category_id),

FOREIGN KEY (Category_id) REFERENCES Problems (Category_id)

)

here is a screenshot of the schema of the access database:

http://u0000315858.user.hosting-agency.de/access.jpg

note: in the table "problems" is "probleem_nr" the primary key !
all other bold printed words are the foreign keys..

thanks in advance

 

[unclejimbob]

Action_regel has a composite primary key and you are trying to create an FK to it from Actions on the non-existent key Action_id. As you can't uniquely identify the associated Action row this will cause an error. Judging by your screen shot you have the FK relationships 'around the wrong way' - all the FK declarations belong in the Problems table.