--- In firebird-support@yahoogroups.com, Carlos Andres Callo Lazo
<incaguide@...> wrote:

>
> > Having the following table :
> >
> > ============ ===
> > Employees
> > ============ ===
> > (PK) idEmployee
> > idBoss
> > ============ ===
> >
> > Is this possible to do ?
> >
> > Alter table Employees add Constraint "FK_Employees" foreign

key(idboss)

> > references Employees(IdEmployee)
> >
>
> >>Presuming your table is actually called Employees and 'Emplyees' is
> >>just a typo.
>
> Ok You're right, the table name is Employees.
> .....
>
> >>Do you mean?
>
> >>Alter table Employees add Constraint "FK_Employees" foreign
> >>key(idboss) references Employees(IdEmploye e)
>
>
> Yes that's rigth. I wantto create a self referencing foreignkey.
> I tried it working with FireBird 1.5 but the server responses that

it must

> exist an explicitly created unique index over field IdEmployees
> But i have already a PK constraint declared for IdEmployees and
> of course it is under a unique constraint.
>
> What can I do ?

This does not add up.

In order to declare a foreign key constraint, the field the key points
to must be declared unique. That means it must be either a declared
primary key or a declared unique field. Firebird will not simply take
your word for it.

So do you have a unique constraint on that field? You claim to have a
primary key on that field alone, so the following query should return
exactly one record like:

PK_EMPLOYEE, idEmployee

Query:

SELECT RI.RDB$INDEX_NAME, RIS.RDB$FIELD_NAME
FROM RDB$INDICES RI
JOIN RDB$INDEX_SEGMENTS RIS ON (RI.RDB$INDEX_NAME = RIS.RDB$INDEX_NAME)
JOIN RDB$RELATION_CONSTRAINTS RC ON (RI.RDB$INDEX_NAME =
RC.RDB$INDEX_NAME)
WHERE UPPER(RI.RDB$RELATION_NAME) = 'EMPLOYEES'
AND RI.RDB$UNIQUE_FLAG = 1
AND RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY 1,RIS.RDB$FIELD_POSITION

If you get no records, that primary key is not declared. If you get
multiple records, that primary key is actually a composite key, so the
idEmployee component does not uniquely identify the row and obviously
can not be used.

Secondly, your original question had a bug in your foreign key
declaration, where you pointed the field to the same field (which is
absolutely pointless; a fields value must exist in the same field in
that table).

The DDL to add a reflexive foreign key should be:

Alter table Employees add Constraint FK_Employees foreign
key (idboss) references Employees(IdEmployee);

Make sure you are trying this one, not the one you initially asked about.

Adam