How to define relationship for record pointing to itself? RRS feed

  • Question

  • I have a table where each record is a 'note'. A note can be a reply to another note so there is a column called 'ReplyToNoteId' where the id points to another Note's ID. I defined this in relationships by adding the table twice (since it would not let me drag the pointer to itself) and connecting ReplyToNoteId to ID.

    However, now I get very strange results when adding a new record. In all cases I get '1' in ReplyToNoteId even though I placed nothing in that field. Anyone got any ideas? All I can think of trying is to delete the relationship.

    Thursday, October 27, 2016 10:15 PM

All replies

  • Check the default value of ReplyToNoteId in the table, and if you add the record in a form, also check the default value in the control that is bound to this field.

    Matthias Kläy, Kläy Computing AG

    Thursday, October 27, 2016 11:06 PM
  • Hi,

    Does the suggestion offered by Matthias work for you? Or do you resolve your problem?

    If no, according to your description, I created a table with two field [Note]/[ReplyToNote] and then added the table twice to define the relationship, draged ReplyToNoteId to ID. Nothing happened when I added new records.

    What is the different between our steps?

    If your issue is still existing, could you please share the detail steps to help us reproduce your issue?



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 2, 2016 1:01 PM
  • I wouldn't define a relationship for this pairing. This is the same structure as a "ReportsTo" field in an Employee table to indicate a supervisor. Not every note is a reply so you are bound to have a lot of primaries with no children.

    Bill Mosca

    Wednesday, November 2, 2016 4:42 PM
  • I don't think the relationship has anything to do with the insertion of a 1 into the column.  It's more likely to be the DefaultValue property of the column and/or the bound control in a form, as Matthias points out.

    BTW, you can model the relationship type by a separate table with columns such as NoteID and NoteRepliedToID.  This is the approach recommended by Date for avoiding the Null foreign keys to which Bill refers.  If the relationship type is many-to-one then the NoteRepliedToID column should be indexed uniquely; if it's one-to-one then both columns should be indexed uniquely; if its many-to-many then both should be indexed non-uniquely and made the composite primary key of the table.  The use of such a table is more familiar in the last context of course, but there is no reason why a unary relationship type should not be modelled in the same way.

    Ken Sheridan, Stafford, England

    Wednesday, November 2, 2016 11:35 PM