Dealing with Circular Reference When Entering Data in SQL

Dealing with circular reference when entering data in SQL

Q: Do I have to disable constraints for the insert to happen?

A: In Oracle, no, not if the foreign key constraints are DEFERRABLE (see example below)

For Oracle:

    SET CONSTRAINTS ALL DEFERRED;
    INSERT INTO Departments values ('foo','dummy');
    INSERT INTO Employees values ('bar','foo');
    UPDATE Departments SET EmployeeID = 'bar' WHERE DepartmentID = 'foo';
    COMMIT;

Let's unpack that:

• (autocommit must be off)
• defer enforcement of the foreign key constraint
• insert a row to Department table with a "dummy" value for the FK column
• insert a row to Employee table with FK reference to Department
• replace "dummy" value in Department FK with real reference
• re-enable enforcement of the constraints

NOTES: disabling a foreign key constraint takes effect for ALL sessions, DEFERRING a constraint is at a transaction level (as in the example), or at the session level (ALTER SESSION SET CONSTRAINTS=DEFERRED;)

Oracle has allowed for foreign key constraints to be defined as DEFERRABLE for at least a decade. I define all foreign key constraints (as a matter of course) to be DEFERRABLE INITIALLY IMMEDIATE. That keeps the default behavior as everyone expects, but allows for manipulation without requiring foreign keys to be disabled.

see AskTom: http://www.oracle.com/technology/oramag/oracle/03-nov/o63asktom.html

see AskTom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10954765239682

see also: http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_12.shtml

[EDIT]

A: In Microsoft SQL Server, you can't defer foreign key constraints like you can in Oracle. Disabling and re-enabling the foreign key constraint is an approach, but I shudder at the prospect of 1) performance impact (the foreign key constraint being checked for the ENTIRE table when the constraint is re-enabled), 2) handling the exception if (when?) the re-enable of the constraint fails. Note that disabling the constraint will affect all sessions, so while the constraint is disabled, other sessions could potentially insert and update rows which will cause the reenable of the constraint to fail.

With SQL Server, a better approach is to remove the NOT NULL constraint, and allow for a NULL as temporary placeholder while rows are being inserted/updated.

For SQL Server:

    -- (with NOT NULL constraint removed from Departments.EmployeeID)
    insert into Departments values ('foo',NULL)
    go
    insert into Employees values ('bar','foo')
    go
    update Departments set EmployeeID = 'bar' where DepartmentID = 'foo'
    go

[/EDIT]

INSERT in TABLES with circular references SQL

To allow cyclic references, you need deferrable constraints:

ALTER TABLE DEPARTAMENTOS 
    ADD CONSTRAINT FK_DEPT_EMP FOREIGN KEY (numDirect) 
    REFERENCES EMPLEADOS(numEmpl)
    DEFERRABLE INITIALLY DEFERRED
    ;
ALTER TABLE EMPLEADOS 
    ADD CONSTRAINT FK_EMP_DEPT FOREIGN KEY (numDept) 
    REFERENCES DEPARTAMENTOS(numDept)
    DEFERRABLE INITIALLY DEFERRED
    ;

Deferrable constraints are checked at transaction end; before commit time a spurious invalid database state is allowed to exist (in the original question: between the two insert statements). But the statements must be inside a transaction, so the statements should be enclosed in BEGIN [WORK]; and COMMIT [WORK];.

SQL: Avoid circular dependencies

Without circular references between tables:

User 
------
userid NOT NULL
PRIMARY KEY (userid)

Picture
---------
pictureid NOT NULL
userid NOT NULL
PRIMARY KEY (pictureid)
UNIQUE KEY (userid, pictureid)
FOREIGN KEY (userid)
  REFERENCES User(userid)

ProfilePicture
---------
userid NOT NULL
pictureid NOT NULL
PRIMARY KEY (userid)
FOREIGN KEY (userid, pictureid)        --- if a user is allowed to use only a
  REFERENCES Picture(userid, picture)  --- picture of his own in his profile

FOREIGN KEY (pictureid)                --- if a user is allowed to use any 
  REFERENCES Picture(picture)          --- picture in his profile

The only difference with this design and your needs is that a user may not have a profile picture associated with him.

---

With circular references between tables:

User 
------
userid NOT NULL
profilepictureid NULL                  --- Note the NULL here
PRIMARY KEY (userid)
FOREIGN KEY (userid, profilepictureid)   --- if a user is allowed to use only a
  REFERENCES Picture(userid, pictureid)  --- picture of his own in his profile

FOREIGN KEY (profilepictureid)           --- if a user is allowed to use any 
  REFERENCES Picture(pictureid)          --- picture in his profile

Picture
---------
pictureid NOT NULL
userid NOT NULL
PRIMARY KEY (pictureid)
UNIQUE KEY (userid, pictureid)
FOREIGN KEY (userid)
  REFERENCES User(userid)

The profilepictureid can be set to NOT NULL but then you have to deal with the chicken-and-egg problem when you want to insert into the two tables. This can be solved - in some DBMS, like PostgreSQL and Oracle - using deferred constraints.

Prevent circular reference in MS-SQL table

Finally, I have created the scripts after some failures, its working fine for me.

   -- To hold the Account table data
   Declare @Accounts table (ID INT, ParentAccountID INT) 

   -- To be updated 
   Declare @AccountID       int = 4;
   Declare @ParentAccountID int = 7;

   Declare @NextParentAccountID INT = @ParentAccountID

   Declare @IsCircular int = 0

   INSERT INTO @Accounts values (1, NULL), (2,1), (3,1) ,(4,3), (5,4), (6,5), (7,6), (8,7)

   -- No circular reference value
   --Select * from @Accounts

   -- Request to update ParentAccountID to 7 for the Account ID 4
   update @Accounts  
   set ParentAccountID = @ParentAccountID
   where ID = @AccountID

   Select * from @Accounts

   WHILE(1=1)
   BEGIN            
       -- Take the ParentAccountID for @NextParentAccountID
       SELECT @NextParentAccountID = ParentAccountID from @Accounts WHERE ID = @NextParentAccountID  

       -- If the @NextParentAccountID is NULL, then it reaches the top level account, no circular reference hence break the loop 
       IF (@NextParentAccountID IS NULL) 
       BEGIN
        BREAK;
       END

       -- If the @NextParentAccountID is equal to @AccountID (to which the update was done) then its creating circular reference
       -- Then set the @IsCircular to 1 and break the loop
       IF (@NextParentAccountID = @AccountID ) 
       BEGIN
        SET @IsCircular = 1
        BREAK
       END
   END

IF @IsCircular = 1 
    BEGIN 
        select 'CircularReference' as 'ResponseCode'
    END

How can I avoid a circular reference situation

You can do the solution either way. If you know that the head paragraph and last paragraph are really important, then having references to them in the story is fine.

In either case, there is a bit of a challenge maintaining relational integrity. Presumably, you want the head and last paragraphs to be in the same story. For this, you will want a composite key. And you need to add the key using a separate alter table statement. So:

alter table paragraph add constraint unq_paragraph_story_prid unique (story, prid);

alter table stories add constraint fk_stories_headpara
    foreign key (stid, headpara) references paragraph(story, prid);

alter table stories add constraint fk_stories_lastpara
    foreign key (stid, lastpara) references paragraph(story, prid);

Similarly, if you use the flags, you will need to ensure that there is exactly one flag of each type set. That can be a bit of a pain when updating. That constraint would look like:

create unique index unq_paragraph_headpara paragraph(story) where head = 1;

create unique index unq_paragraph_lastpara paragraph(story) where last = 1;

Notes about naming and other things:

• ids should be numeric, if they can be. This simplifies foreign key references.
• The name of the id should be completely spelled out (paragraphId or paragraph_id) or simply id. If you use prid, that could get confused with another table.
• Not all databases support filtered unique indexes. In those cases, you need to use a trigger or another mechanism.

Are circular references acceptable in database?

Records which point to other records are useful in a database. Sometimes these records form a cycle. This might still be useful. The only real annoyance in practice is avoiding violating the constraints.

For example, if you have a user and transaction table, the user might have a pointer to his last transaction. You need to insert the transaction first, then update the last_transaction_id to the correct value. While both these records exist you can't erase them, because the user.last_transaction_id points to transaction.id and transaction.user_id points to user.id. This implies that a user with no transactions has a null last_transaction_id. It also means that you have to null that field before you can delete the transaction.

Managing these foreign key constraints is a pain but it certainly is possible. There may be problems that arise if you add constraints to the database later which introduce new circular dependencies. You have to be careful in this situation. However, as long as one of the records in the cycle has a nullable foreign-key field, the cycle can be broken and the records can be deleted. Updates are not usually a problem as long as you insert the records in the right order.

How to prevent insertion of cyclic reference in SQL

Note first that it is preferable to detect cycles in another environment as recursive CTEs aren't known for their good performance and neither is a trigger that would run for each insert statement. For large graphs, a solution based on the solution below will likely be inefficient.

---

Suppose you create the table as follows:

CREATE TABLE dbo.lnk (
    node_from INT NOT NULL,
    node_to INT NOT NULL,
    CONSTRAINT CHK_self_link CHECK (node_from<>node_to),
    CONSTRAINT PK_lnk_node_from_node_to PRIMARY KEY(node_from,node_to)
);

That would block inserts with node_from equal to node_to, and for rows that already exist.

The following trigger should detect cyclic references by throwing an exception if a cyclic reference is detected:

CREATE TRIGGER TRG_no_circulars_on_lnk ON dbo.lnk AFTER INSERT
AS
BEGIN
    DECLARE @cd INT;
    WITH det_path AS (
        SELECT
            anchor=i.node_from,
            node_to=l.node_to,
            is_cycle=CASE WHEN i.node_from/*anchor*/=l.node_to THEN 1 ELSE 0 END
        FROM
            inserted AS i
            INNER JOIN dbo.lnk AS l ON
                l.node_from=i.node_to
        UNION ALL
        SELECT
            dp.anchor,
            node_to=l.node_to,
            is_cycle=CASE WHEN dp.anchor=l.node_to THEN 1 ELSE 0 END
        FROM
            det_path AS dp
            INNER JOIN dbo.lnk AS l ON
                l.node_from=dp.node_to
        WHERE
            dp.is_cycle=0
    )
    SELECT TOP 1
        @cd=is_cycle 
    FROM 
        det_path
    WHERE
        is_cycle=1
    OPTION 
        (MAXRECURSION 0);

    IF @cd IS NOT NULL 
        THROW 67890, 'Insert would cause cyclic reference', 1;
END

I tested this for a limited number of inserts.

INSERT INTO dbo.lnk(node_from,node_to)VALUES(1,2); -- OK
INSERT INTO dbo.lnk(node_from,node_to)VALUES(2,3); -- OK
INSERT INTO dbo.lnk(node_from,node_to)VALUES(3,4); -- OK

And

INSERT INTO dbo.lnk(node_from,node_to)VALUES(2,3); -- PK violation
INSERT INTO dbo.lnk(node_from,node_to)VALUES(1,1); -- Check constraint violation
INSERT INTO dbo.lnk(node_from,node_to)VALUES(3,2); -- Exception: Insert would cause cyclic reference
INSERT INTO dbo.lnk(node_from,node_to)VALUES(3,1); -- Exception: Insert would cause cyclic reference
INSERT INTO dbo.lnk(node_from,node_to)VALUES(4,1); -- Exception: Insert would cause cyclic reference

It also detects cyclic references already present in the inserted rows if inserting more than one row at once, or if a path longer than one edge would be introduced in the graph. Going off on the same initial inserts:

INSERT INTO dbo.lnk(node_from,node_to)VALUES(8,9),(9,8);       -- Exception: Insert would cause cyclic reference
INSERT INTO dbo.lnk(node_from,node_to)VALUES(4,5),(5,6),(6,1); -- Exception: Insert would cause cyclic reference

---

Related Topics