Referential Constraint Error in IB6 SQL Script....Please help.

[quintons]

I am a newbee with IB 6. Its the open source version and have come across a
problem.

When I try to execute the script below I get an error, and have not managed
to get passed it for the past couple of days.

this is the SQL error:
Unsuccessful metadata update
STORE RDB$REF_CONSTRAINTS failed
action cancelled by trigger (1) to preserve data integrity
Name of Referential Constraint not defined in constraints table
Statement: ALTER TABLE APPLICATION
ADD FOREIGN KEY (FK_GroupsID)
REFERENCES GROUPS

this is the SQL script:
CREATE TABLE APPLICATION (
PK_ApplicationID INTEGER NOT NULL,
FK_AmendByPersonID INTEGER DEFAULT 0,
FK_GroupsID INTEGER DEFAULT NULL,
Name VARCHAR(50),
EXEName VARCHAR(50),
Description VARCHAR(18),
AmendDateTime DATE,
CreateDateTime DATE,
OrderNo INTEGER DEFAULT 0,
VersionNo VARCHAR(20)
);

CREATE UNIQUE INDEX idxPrimaryKey_Application ON APPLICATION
(
PK_ApplicationID
);

ALTER TABLE APPLICATION
ADD PRIMARY KEY (PK_ApplicationID);

CREATE TABLE GROUPS (
PK_GroupsID INTEGER NOT NULL,
FK_AmendByPersonID INTEGER DEFAULT 0,
Name VARCHAR(50) NOT NULL,
Description VARCHAR(255),
Options VARCHAR(50),
AmendDateTime DATE,
CreateDateTime DATE
);

CREATE UNIQUE INDEX idxPrimaryKey_Groups ON GROUPS
(
PK_GroupsID
);

ALTER TABLE GROUPS
ADD PRIMARY KEY (PK_GroupsID);


ALTER TABLE APPLICATION
ADD FOREIGN KEY (FK_GroupsID)
REFERENCES GROUPS;

ALTER TABLE GROUP_PERSON
ADD FOREIGN KEY (FK_GroupsID)
REFERENCES GROUPS;



this code was generated by ERwin 4.0. can anyone help?
many thanks,
Quinton.

 

[guscottg]

Try the following code below. The only thing I've changed is getting rid of the unique indexes which you were creating. These are needed as by default a primary key is a unique index.

CREATE TABLE APPLICATION (
PK_ApplicationID INTEGER NOT NULL,
FK_AmendByPersonID INTEGER DEFAULT 0,
FK_GroupsID INTEGER DEFAULT NULL,
Name VARCHAR(50),
EXEName VARCHAR(50),
Description VARCHAR(18),
AmendDateTime DATE,
CreateDateTime DATE,
OrderNo INTEGER DEFAULT 0,
VersionNo VARCHAR(20)
);



ALTER TABLE APPLICATION
ADD PRIMARY KEY (PK_ApplicationID);

CREATE TABLE GROUPS (
PK_GroupsID INTEGER NOT NULL,
FK_AmendByPersonID INTEGER DEFAULT 0,
Name VARCHAR(50) NOT NULL,
Description VARCHAR(255),
Options VARCHAR(50),
AmendDateTime DATE,
CreateDateTime DATE
);


ALTER TABLE GROUPS
ADD PRIMARY KEY (PK_GroupsID);


ALTER TABLE APPLICATION
ADD FOREIGN KEY (FK_GroupsID)
REFERENCES GROUPS (PK_GroupsID);

 

[Thanks]

I Have now managed to get the db script working now.

Regards,
Quinton.