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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

blandyukCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 RSQL Server DBA & Architect, EE Solution GuideCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

blandyukCommented:
Agreed, circular tables are not a good idea because every single row is dependant on each other.
0
dportasCommented:
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 & Architect, EE Solution GuideCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.