×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

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

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);

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

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

Regards,
Quinton.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close