--- In firebird-support@yahoogroups.com, "incaguide" <incaguide@...>
wrote:

>
> Is it Possible with firebird to do the following ?
>
> Having the following table :
>
> ===============
> Emplyees
> ===============
> (PK) idEmployee
> idBoss
> ===============
>
> Is this possible to do ?
>
> Alter table Employees add Constraint "FK_Employees" foreign key(idboss)
> references Employees(IdBoss)
>

Presuming your table is actually called Employees and 'Emplyees' is
just a typo.

You can not create a foreign key to a target field that is not
declared unique (explicitly via a unique constraint, or implicitly via
a primary key constraint).

But I am having trouble seeing what you are trying to do. Your
constraint says that values in Employees.idBoss must be found in the
field Employees.idBoss. It would also imply that an employee could not
be the idBoss of multiple employees

Do you mean?

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

As in the idBoss points to the idEmployee of another record? That is
certainly possible.

We have such a structure for our applications file menu, where a menu
item has a parent menu item, a null parent menu item means it is at
the top of the main menu).

Just be aware also that some data pumping tools aren't really clever
enough to understand that the records within the table must be copied
in a particular order.

It may be easier to create a second table like

EmployeesBoss
(
idEmployees (PK / FK),
idBoss (UQ / FK)
)

And declare both fields as foreign keys to the Employees table. Data
pumping tools have no problems with this because they already load
tables in order of dependencies.

Adam