Help creating a foreign key

[former_member1021736]

Hi,

I am having problems creating a foreign key. I am probably doing something fundamentally wrong, but can't figure out what 😞

Below is a script that shows what I am trying to do. I want to link the table "e_dx_QueryParameter" to itself so that a "parameter" might have a "parent parameter".

For example a parameter for a query might be the "WarehouseId", but BEFORE providing the "WarehouseId" another "parent" parameter "CompanyId" (for example) needs to be provided.

The table is created fine, but the foreign key constraint fails saying that "Column 'Parameter' not found".

I'm probably misunderstanding something basic here.... Hopefully I can be illustrated about this here.

Thanks!

Edgard

CREATE  TABLE e_dx_QueryParameter (
IdQuery                    VARCHAR(10) NOT NULL,
Ndx                        INTEGER NOT NULL,
Parameter                  VARCHAR(50),
ParameterType              SMALLINT NOT NULL DEFAULT 2,
ParentParameter            VARCHAR(50),
ParameterValue             VARCHAR(300),
PRIMARY KEY (IdQuery,Ndx)
);
ALTER TABLE e_dx_QueryParameter
   ADD FOREIGN KEY e_dx_CP_REF_PARAM_PARENT (IdQuery, ParentParameter)
       REFERENCES e_dx_QueryParameter (IdQuery, Parameter)
       ON DELETE CASCADE
       ON UPDATE CASCADE
;

[chris_keating]

A foreign key restricts the values for a set of columns to match the values in a primary key or a unique constraint of another table (the primary table). That is not the case in your schema.

What version and build are you using? In 17.0 Build 4882, I get an error -1226 Foreign Key columns do not match the primary key or a uniqueness constraint.

[former_member1021736]

Thanks Chris! I don't know how I missed that.

I got it working now!

Thanks again!