Foreign key relation with own table

[Radboud Platvoet]

Hi there,

I have a question about defining a foreign key relationship on the same table. I have a table called 'Folder' of which the use is very similar as in any file system: It can hold both additional folders as well as some items. To define the structure of the folder tree, I have added a column 'folder_parent' that refers to a the primary key of different record (row) in table 'Folder'. This works fine. No problems.

The problem is: My 'Folders' exist within a 'Project' and my primary key really exists out of the fields 'proj_id' and 'folder_id'. A parent folder may only be a folder that exists in the same project. Therefor my foreign key would refer to proj_id and folder_id and I would like to use the fields 'proj_id' and 'folder_parent' to refer to them. However, when I want to select 'proj_id' to refer to 'proj_id' it is not in the list...

What's wrong with a field refering to itself as part of a foreign key relationship that exists out of more fields? Now I have added a field 'folder_parent_proj' next to 'folder_parent' to make the foreign key relation. However, this way it is possible to select a folder from a different project and I would like to exclude that option...

Regards,
Radboud