Refers to database tables with a column that is a foreign key to another column within the same table.
Questions tagged [self-referencing-table]
131 questions
2
votes
1 answer
Self-referential relationship table design: one or two tables?
CREATE TABLE Employee
(
id INT,
boss INT REFERENCES Employee(id),
PRIMARY KEY (id)
);
One employee can have many bosses and one boss can have many employees.
Does this table function the same as this two-table design?
…
user697911
- 10,043
- 25
- 95
- 169
2
votes
1 answer
Self Referencing on a separate table - BoM in Laravel
I'm curious if there's an easy way of implementing a bill of materials (assemblies) type recursive system using eloquent? Here are the two table structures I'm working with:
inventory table:
+----+------------+-------------+
| id | name |…
Steve Bauman
- 8,165
- 7
- 40
- 56
2
votes
1 answer
Recursive CTE with alternating tables
I've created a SQL fiddle here.
Basically, I have 3 tables BaseTable, Files, and a LinkingTable.
The Files table has 3 columns: PK, BaseTableId, RecursiveId (ChildId).
What I want to do is find all the children given a BaseTableId (i.e.,…
SOfanatic
- 5,523
- 5
- 36
- 57
2
votes
1 answer
how to import a table with a self relationship
I have the following table:
EntityId - PK
Label
ParentEntityId - FK
ParentEntityId is joined to EntityId of the same table. now I am having problems importing data to this table using SSIS because of instances where the parent entities haven't…
Mel
- 3,058
- 4
- 26
- 40
2
votes
1 answer
Modeling an N-level tree in SQL Server with cascading deletes
I need to model an n-level tree in SQL Server. I originally did something like this to create a Node table:
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[ParentId] [bigint] NULL
It's a self referential table, where…
Eric
- 5,842
- 7
- 42
- 71
2
votes
3 answers
How to add records in self referencing table?
I am a noob in MySql. I want to create the following self-referencing table:
EMPLOYEE
+-----+------+------+
|Name |E-ID |M-ID |
+-----+------+------+
|ABC |12345 |67890 |
|DEF |67890 |12345 |
+-----+------+------+
I use the following…
Nehal J Wani
- 16,071
- 3
- 64
- 89
1
vote
1 answer
How to query a self-referential comments table to find the comments with replies, ordered by the latest replies?
In an app I'm working in, I've got a Comments table in my Postgres DB that which can be simplified to something like this:
+----+-----------+-----------------+---------------------+
| id | parent_id | group_member_id | created_at …
Michael Bester
- 315
- 4
- 11
1
vote
3 answers
laravel self referencing entity
I am having a problem in a laravel project, i have this entity where it is self referncing. I am creating a PPA (program,project,activity) Entity . So this belongs in an Rc_Indicator Entity. The problem is i do not know to create the self…
Ruthyn Ann POTAYRE
- 11
- 2
1
vote
1 answer
How do I construct a self-referential/recursive SQLModel
I want to define a model that has a self-referential (or recursive) foreign key using SQLModel. (This relationship pattern is also sometimes referred to as an adjacency list.) The pure SQLAlchemy implementation is described here in their…
Daniil Fajnberg
- 12,753
- 2
- 10
- 41
1
vote
1 answer
Self-referencing zero or one to many (0-1 -> N) relation in Entity Framework Core 6
I have this simple entity which may have some children of its own type:
public class File {
public long Id { get; set; }
public File? Parent { get; set; }
public long? ParentId { get; set; }
public IList? Children { get;…
amiry jd
- 27,021
- 30
- 116
- 215
1
vote
1 answer
flask: Get all items from parent in self referencing table: sqlalchemy.exc.ArgumentError: Expected mapped entity or selectable/table as join target
I am trying to select all the child posts from the post id selected by the user. Below is my model definition for Posts and the query that gives the error in the title. I have tried a few things including aliasing which looked promising but this and…
catchpolej
- 21
- 7
1
vote
1 answer
Load filtered hierarchy from self-referencing table in EF Core
I have a self-referencing entity Comment in my project:
public class Comment
{
public Guid CommentId { get; set; }
public string Content { get; set; }
public Guid? ParentCommentId { get; set; }
public virtual Comment…
Boris Makhlin
- 240
- 5
- 11
1
vote
1 answer
Query all connected ancestors and descendants in a self referencing table
Suppose I have a table 'prlines' with a self relation on adjacent records:
id | prev_id
------------
1 | NULL
2 | NULL
3 | 1
4 | 2
5 | 3
6 | 5
I want to get all connected IDs (previous/next) of a certain record. For example:
SELECT `prev_id`…
sandrows
- 303
- 2
- 12
1
vote
0 answers
C# & Entity Framework Core creating entity many to many relation with itself as multiple parents and multiple children
I'm a relatively new programmer and I'm in the middle of creating Entities for Entity Framework Core for a project I'm currently on, but I'm having difficulty figuring out how to setup 1 specific entity named "Group".
The "Group" entity can contain…
Docuventurer
- 45
- 4
1
vote
1 answer
Self referencing table where reference is NOT NULL
Is it possible to have a self-referencing entity where the self-reference ID is NOT NULL? See my sample entity where parent.parent_id is configured with nullable=false. When I flush, I get the following error:
SQLSTATE[23000]: Integrity constraint…
user1032531
- 24,767
- 68
- 217
- 387