SQL table: create 1-to-1 relationship with itself?

Question

I want to create a 1-to-1 relationship on a table with itself.

I have a table MenuItem, but I want the items to be able to have a parent MenuItem. One item can only have one parent, but an item can be parent to multiple items.

I am currently working with a link table, MenuItemParent, but I can't figure out how to get the keys and constraints correctly. It has two columns: MenuItemId and ParentId. Both are foreign keys to the MenuItem table.
If I make the first or both columns Primary key, I seem to end up with a 1-to-many relationship. (I'm generating code from the DB so I can verify it.)

If I only make the first column Primary Key, I end up in a sort of Schrödinger state where a MenuItem can both have a single parent and have multiple parents (i.e. the generated POCO has both a MenuItem property and an EntitySet<MenuItem> property.) I could build my code around this, but then it's not clear from either the model or the generated code what kind of relationship it actually is.

What am I missing?

As to why I'm using a link table, I'm trying to employ vertical segmentation, as this data will not be accessed as often.

A 1-1 relationship effectively partitions the attributes (columns) in a table into two tables. This is called vertical segmentation. This is often done for sub-classing the table entities, or, for another reason, if the usage patterns on the columns in the table indicate that a few of the columns need to be accessed significantly more often than the rest of the columns. (Say one or two columns will be accessed 1000s of times per second and the other 40 columns will be accessed only once a month). Partitioning the table in this way in effect will optimize the storage pattern for those two different queries.

From: https://stackoverflow.com/a/5112498/125938

Edit: premature optimization aside, I now understand I could simply use a ParentId column in the MenuItem table, but is this really better than using a link table?

That might be the answer. I just remember having it seen before using a link table. — Protector one, Oct 19 '18 at 12:43
@Protectorone create Unique Constraint for the tow fields MenuItemId and ParentId and then create the relation. — mohabbati, Oct 19 '18 at 12:50
Your partitioning comment smells of premature optimization. You are talking about a single (presumably int) column. — SMor, Oct 19 '18 at 13:19
What you are wanting to do is not suitable for vertical partitioning - that is about moving off infrequently used (and often large) fields from the main data - since you are talking about a single ID column there is little justification for such a technique at all - it is driving you to a bad solution. Vertical partitioning is about performance of the frequent with a penalty for the infrequent. — Andrew, Oct 19 '18 at 13:20
@Andrew: OK, I get your first point, but why would this solution be bad? — Protector one, Oct 19 '18 at 13:21
@Protectorone - you take a performance hit when using the column in vertical partitioning, yet you are gaining little to no performance for when you are not using it. (Join's cost disk and cpu time, so when you do need to use it and join, you add to your query cost, potentially quite significantly) — Andrew, Oct 19 '18 at 13:23
@Andrew: it all seems a bit premature optimization to me now—I take a hit here, I might gain something there (be it performance or storage)… But is it actually a _bad_ solution? — Protector one, Oct 19 '18 at 13:26
With SQL-Server the developers introduced a rarley used concept exactly to build this: [Read about HIERARCHYID](https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-2017). — Shnugo, Oct 19 '18 at 13:33
"One item can only have one parent, but an item can be parent to multiple items." This, by definition, is NOT a 1-to-1 relationship. — Tab Alleman, Oct 19 '18 at 13:33
@TabAlleman: Yes, I realized this too after posting the question. Would it be "many-to-one"? That doesn't quite sound right. — Protector one, Oct 19 '18 at 13:35
@Protectorone - adding unnecessary complexity is always 'bad' from my perspective. — Andrew, Oct 19 '18 at 15:00

GuidoG · Accepted Answer · 2018-10-21T22:32:35.107

You should add a ParentID column to your table MenuItem with a foreign key. This is an example on how to do that.

alter table MenuItem
add ParentID int null;

alter table MenuItem
add constraint FK_MenuItemParent foreign key (ParentID) references MenuItem (ID);

Now you have an hierarchical table, which means that a menuitem can have only one parent, but many other menuitems can have the same menuitem as parent

A Link Table is only needed when you need a many to many relationship, which is not the case for this

Also you can create an unique index on both columns, as suggested, but beware that the ParentID can be null often so add a clause to fix that

create unique nonclustered index idx_MenuParentID
on MenuItem(ID, ParentID)
where ParentID is not null;

score 0 · Answer 2 · answered Oct 19 '18 at 12:41

Get rid of the "link" table. Just setup your MenuItem table with an ID (PK) column and a ParentID (FK) column. Setup the foreign key relationship (I'll assume you can figure that out). Then setup a "Unique Key" constraint on the ParentID and ID columns.

score -1 · Answer 3 · answered Oct 19 '18 at 12:55

I think you should try to have 1 column is PRIMARY KEY, and the other is FOREIGN KEY REFERENCES from MenuItem. Because the 1-1 relationship with itself in database called self-reference(you can search google for more info), it can't have two FOREIGN KEY.

SQL table: create 1-to-1 relationship with itself?

3 Answers3