MySQL Forums
Forum List  »  MySQL Workbench

Foreign key relation with own table
Posted by: Radboud Platvoet
Date: June 09, 2008 07:59AM

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...


Options: ReplyQuote

Written By
Foreign key relation with own table
June 09, 2008 07:59AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.