Self-Referential Table Fields in MySQL

Self-Referential Table Fields In MySQL

One option is to make your field NULL-able, and set the root record's parent key to NULL:

CREATE TABLE tb_1 (
   id       int   NOT NULL  PRIMARY KEY,
   value    int   NOT NULL,
   parent   int   NULL,
   FOREIGN KEY (parent) REFERENCES tb_1(id)
) ENGINE=INNODB;
Query OK, 0 rows affected (0.43 sec)

-- This fails:
INSERT INTO tb_1 VALUES (1, 1, 0);
ERROR 1452 (23000): A foreign key constraint fails.

-- This succeeds:
INSERT INTO tb_1 VALUES (1, 1, NULL);
Query OK, 1 row affected (0.08 sec)

Otherwise you could still use a NOT NULL parent key and point it to the root record itself:

CREATE TABLE tb_2 (
   id       int   NOT NULL  PRIMARY KEY,
   value    int   NOT NULL,
   parent   int   NOT NULL,
   FOREIGN KEY (parent) REFERENCES tb_2(id)
) ENGINE=INNODB;
Query OK, 0 rows affected (0.43 sec)

-- This fails:
INSERT INTO tb_2 VALUES (1, 1, 0);
ERROR 1452 (23000): A foreign key constraint fails.

-- This succeeds:
INSERT INTO tb_2 VALUES (1, 1, 1);
Query OK, 1 row affected (0.08 sec)

How do I create a table with self-referencing fields in MySQL?

It's a simple syntax error. Try this:

FOREIGN KEY (parent_station) REFERENCES stops(stop_id) 

Create view from mysql self referential table and another table

It seems, with only SQL select statement it is not possible.

https://stackoverflow.com/a/9056945/721597

But with the help of functions, I managed to do it.

I created a function called IsRelated(id, parent) -- which tells whether id is related to parent (if id = parent they are related):

CREATE FUNCTION `IsRelated`(`GivenID` VARCHAR(10), ParentId VARCHAR(10)) RETURNS boolean
BEGIN
    DECLARE related boolean; 
    DECLARE ch INT;

    SET related = FALSE;  
    IF ParentId = GivenID THEN
        SET related = TRUE;
    ELSE
        SET ch = GivenID; 
        myloop: WHILE ch IS NOT NULL DO
            SELECT IF(parent_org_id = ch, NULL, parent_org_id) INTO ch FROM
            (SELECT parent_org_id FROM organizations WHERE id = ch) A;
            IF ch IS NOT NULL AND ch = ParentId THEN
                SET related = TRUE; 
                LEAVE myloop;
            END IF;
        END WHILE; 
    END IF;
    RETURN related;
END

Then I create a view like this:

CREATE VIEW `v_all_orgs` AS SELECT o1.id AS org, o2.id AS related_to
FROM organizations o1
JOIN organizations o2 ON IsRelated(o1.id, o2.id)

With these two, I created my required view in question:

CREATE VIEW `v_all_user_orgs` AS SELECT DISTINCT user_organizations.user_id, v_all_orgs.org as org_id, user_organizations.created_ts
FROM user_organizations JOIN v_all_orgs ON v_all_orgs.related_to = user_organizations.org_id OR v_all_orgs.org = user_organizations.org_id
ORDER BY user_organizations.user_id, v_all_orgs.org

How to add records in self referencing table?

As MySQL does not support deferrable constraints (which are the "natural solution" to such a problem) you will need to do this in two steps;

INSERT INTO employee (name, `E-ID`) values ('Arthur', '123456');
INSERT INTO employee (name, `E-ID`) values ('Ford', '67890');
UPDATE employee 
    SET `M-ID` = '67890' 
WHERE `E-ID` = '123456';

UPDATE employee 
    SET `M-ID` = '123456' 
WHERE `E-ID` = '67890';

You circular reference does sound strange to me though. An employee being the manager of an employee who is in turn his manager?

Allow me two comments on your table definition:

• avoid column (or table names) with special characters that need quoted identifiers. Using E_ID instead of E-ID will save you some trouble in the long run
• If your employee ID can be shorter than 6 characters than you most probably want to use VARCHAR(6) instead of CHAR(6) due to the padding of the values with the CHAR datatype.

MySQL Self referencing table, display hierarchy in one row with combined fields

This can be done by writing a custom function which retrieves/loops through the ID provided until it reaches the base and returns the concatenated string.

Name:    GetAllTitles
Input:   InputID

Steps:   `While ID <> null
{
    select TABLE_A_ID,title from table where ID='InputID';
    If TABLE_A_ID is not null then,
    {
        // Save title to temp
        // Save TABLE_A_ID to InputID
        return CONCAT(temp,GetAllTitles(InputID));
    }
    else {  return emptystring;
    }
}`

Final SQL Query would be:
select ID,GetAllTitles(ID) from TABLE A

Self-referential relationship table design: one or two tables?

The two designs are quite different. The first requires that each employee have (at most) one boss, although each boss could have many employees.

The second allows for employees to have more than one boss.

From your description of the problem, the second form is the more appropriate data model. From my understanding of boss-employee relationships, I would expect the first to be correct.

---

Related Topics