Circular relationship in N tables

Hi,

I need detailed description about the circular relationship between a number of tables, for example TableA's PK is referred by TableB's FK, TableB's PK is referred by TableC's FK ................. TableY's PK is referred by TableZ's FK and TableZ's PK is referred by TableA's FK.

In technical words you can say that there are  n tables, R1, R2, …, Rn, such that the foreign key of table R2 references to the primary key of table R1 (RIC2), the foreign key of table R3 references to the primary key of table R2 (RIC3), …., the foreign key of table Rn references to the primary key of table Rn-1 (RICn), and the foreign key of table R1 references to the primary key of table Rn (RIC1).

--> Please describe how to create tables R1, …, Rn and to enforce all the referential integrity constraints RIC1, RIC2, …, RICn.
--> Please describe how to populate the data into tables R1, …, Rn.


Best Regards,
LVL 13
sonicefuAsked:
Who is Participating?
 
blandyukConnect With a Mentor Commented:
Is this for real or is this course work your doing at College / Uni? Curcular tables can be easily created:

BEGIN TRANSACTION
GO
CREATE TABLE dbo.tblRa
      (Ra int NOT NULL,
      Rb int NOT NULL
      )  ON [PRIMARY]
GO
ALTER TABLE dbo.tblRa ADD CONSTRAINT
      PK_Ra PRIMARY KEY CLUSTERED
      (Ra) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.tblRa SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO

BEGIN TRANSACTION
GO
CREATE TABLE dbo.tblRb
      (Rb int NOT NULL,
      Rc int NOT NULL
      )  ON [PRIMARY]
GO
ALTER TABLE dbo.tblRb ADD CONSTRAINT
      PK_Rb PRIMARY KEY CLUSTERED
      (Rb) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.tblRb SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO

BEGIN TRANSACTION
GO
CREATE TABLE dbo.tblRc
      (Rc int NOT NULL,
      Ra int NOT NULL
      )  ON [PRIMARY]
GO
ALTER TABLE dbo.tblRc ADD CONSTRAINT
      PK_Rc PRIMARY KEY CLUSTERED
      (Rc) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.tblRc SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
GO

INSERT INTO tblRa (Ra, Rb) VALUES (1,1);
INSERT INTO tblRb (Rb, Rc) VALUES (1,1);
INSERT INTO tblRc (Rc, Ra) VALUES (1,1);
GO

INSERT INTO tblRa (Ra, Rb) VALUES (2,4);
INSERT INTO tblRb (Rb, Rc) VALUES (4,7);
INSERT INTO tblRc (Rc, Ra) VALUES (7,2);
GO
0
 
sonicefuAuthor Commented:
blandyuk,

Thanks a lot for your prompt reply on my question.
Actually I'm working as Oracle DBA and Oracle Guru at Experts-Exchange, I'm not a student but guiding to the students as well.

Can you explain it for n tables please ?

Best Regards,

sonicefu
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Ok. In simple words, you need to do the following set of things in order

* Creation of Tables
* Creation of Primary Keys for all tables
* Creation of Foreign keys ( Foreign key creation needs Primary key in place)

>> there are  n tables, R1, R2, …, Rn, such that the foreign key of table R2 references to the primary key of table R1 (RIC2), the foreign key of table R3 references to the primary key of table R2 (RIC3), …., the foreign key of table Rn references to the primary key of table Rn-1 (RICn), and the foreign key of table R1 references to the primary key of table Rn (RIC1).

Follow this order for inserting records into your tables

* First insert into R1 table with foreign key of table R1 referencing to primary key of table Rn as Null since no records ideally exist in that table.. ( Make sure that column is designed as Nullable)
* Insert records into R2 table with Primary key of R1 inserted into Foreign key column of R2 table..
* Similarly proceed with all other tables
* .
* Insert records into Rn table with Primary key of Rn-1 inserted into Foreign key column of Rn table.
* Once you have records present in Rn table, you can update R1 table with Foreign key of table R1 referencing this table Primary key with the required values.

PS: Having circular dependency is a bad design and kindly try to avoid it..
Kindly let me know if I need to explain anything in more detail.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
blandyukCommented:
Agreed, circular tables are not a good idea because every single row is dependant on each other.
0
 
dportasConnect With a Mentor Commented:
rrjegan17: "Having circular dependency is a bad design and kindly try to avoid it.."

It can be hard to implement in SQL but that doesn't mean it's the wrong design from a data modeling point of view. If a circular dependency accurately represents the real world you are trying to model then it ought to be a good thing to use such a structure. Unfortunately SQL DBMSs are actually very poor tools for representing data intgrity constraints generally and multi-table integrity constraints in particular. So it would be better to be clear about where the problem lies and say: "SQL is not much good at this, so try to avoid circular dependencies in SQL".
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Thanks dportas for your detailed explanation and that is what I am trying to say...
Don't bring up Circular dependency in SQL as it can't handle it better..
0
 
sonicefuAuthor Commented:
Thank a lot !

and sorry for delayed acceptance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.