circular relationship

Question

I have a question that circulates a lot in my head, I'd like to find a response that can unlock me ... in the field of database (DBMS and Modeling MPD).

Is it possible to find a circular relationship between two (or more)tables

such as:

Create Table TA ( TA_ID   INTEGER  not null, A  SMALLINT, AA   NUMERIC(5,0),TB_ID   INTEGER, constraint PK_TA primary key (TA_ID) );

Create Table TB (TB_ID   INTEGER not null,  B    SMALLINT, BB  FLOAT, TA_ID   INTEGER,  constraint PK_TB primary key (TB_ID) );

alter TABLE TB  add constraint Fk_TB_TA foreign key (TA_ID)  references TA (TA_ID);

alter TABLE TA  add constraint Fk_TA_TB foreign key (TB_ID)  references TB (TB_ID);

http://stackoverflow.com/questions/19031803/how-to-find-circular-dependent-table-in-sql-server — TheGameiswar, Sep 08 '16 at 16:41
https://azure.microsoft.com/en-in/blog/finding-circular-foreign-key-references/ — TheGameiswar, Sep 08 '16 at 16:41
The relationship is not "circular" because you are using two different fields. Ta_Id, and Tb_Id. Not sure what the benefit of doing this relationship is. But you should be able to do this with Sql. You would have to load a starting record into the table before applying the constraints or else if you started with zero records in both tables you could not insert without removing the constraints first. Why are you trying to do this? It does not sound like a good path to go down database design wise? — M T Head, Sep 08 '16 at 17:20
i am just want know if this thing is possible, also i can do that without "not null" constraint on foreign key.. and if there is somebody who used it — Houcine Cine, Sep 08 '16 at 17:59
Why do you want to know if this is possible? What problem are you trying to solve? — TommCatt, Sep 09 '16 at 21:38
there is no problem, but there are a lot of modeling software that allows to do this and another that does not, then I ask myself what is the true logic — Houcine Cine, Sep 10 '16 at 14:07

score 0 · Answer 1 · answered Sep 08 '16 at 17:58

0

If you're looking for a set of tables that refer to each other through FKs, then you could try this code.

SELECT  *
FROM    
    sys.foreign_keys fk
WHERE
    EXISTS 
    (SELECT * FROM sys.foreign_keys fk2 WHERE fk2.referenced_object_id = fk.parent_object_id AND fk2.parent_object_id = fk.referenced_object_id)

answered Sep 08 '16 at 17:58

paulbarbin

382
2
9

thenk's, i am just want know if this thing is possible,and in any case can be – Houcine Cine Sep 08 '16 at 18:06

score 0 · Answer 2 · edited May 23 '17 at 10:29

Ok, is it possible? With the NOT NULL constraints on both - NO. If you allow NULLS in one, then YES.
Is it a good idea, I can't think of a reason why you'd do this but sometimes problems in the real world are complicated and I generally don't say never.

A quick search pulls up this question that shows that this IS happening in the real world, check this out: How to do an INSERT into tables with circular relationships (SQL SERVER)

circular relationship

2 Answers2