Foreign key to other table, with foreign key back to first one: ok?

Is it ok for two tables to have foreign keys to each other based on the same
columns? If so, are there rules about the cascade settings in such a case? I
am thinking of tables that are virtually one-to-one with each other and can
tend to be viewed or deleted equally "from either end".

--
Steve
http://www.geocities.com/SiliconValley/Sector/6376



0
Steve
9/20/1999 4:44:42 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

8 Replies
592 Views

Similar Articles

[PageSpeed] 39

The first problem I see with this is that it is a "which came first, the 
chicken or the egg" type of a problem.

When you insert into one table the FK is going to want to check to see if 
the value is in the other table.  And vice-versa.

There is a way to get around this.  When you create the FK use the CHECK 
ON COMMIT option.  That way the FK validation doesn't occur until a 
COMMIT.  

I'm not a big fan of this.  It isn't all that intuitive.  Joe Blow 
programmer isn't going to know that the CHECK ON COMMIT option has been 
set.  He isn't going to understand what's going on.  To him it's all 
magic.

My preference would be to just assume that one of the tables is the 
parent and the other is the child.  Also, if they are so tied together 
why are they two separate tables?
-- 
Jim Egan [TeamSybase]
Dynamic Data Solutions, Inc. http://www.dyn-data.com
Houston, TX
0
jegan
9/20/1999 6:08:52 PM
Steve,
We have a pattern like this in our model.  From A to B, if A is deleted, B
is also deleted.  From B to A, if B is deleted, the FK on A that points to B
is set to NULL.

We did this with declarative RI pointing from B to A, on Delete Set Null.
Then we wrote a delete trigger on A that deletes the corresponding row in B.

Paul Horan
VCI
Springfield, MA

Steve Moran <n.o.s.p.a.m.steve@silverlink.co.uk> wrote in message
news:GKtfjv4A$GA.265@forums.sybase.com...
> Is it ok for two tables to have foreign keys to each other based on the
same
> columns? If so, are there rules about the cascade settings in such a case?
I
> am thinking of tables that are virtually one-to-one with each other and
can
> tend to be viewed or deleted equally "from either end".
>
> --
> Steve
> http://www.geocities.com/SiliconValley/Sector/6376
>
>
>


-1
Paul
9/20/1999 7:00:33 PM
I've got a setup with all "check on commit" anyway, so that should be ok.
Why they are two separate tables is a bit of a long story. There are huge
numbers of columns involved but the main reason is that one is a sort of
diary header table that contains entries to do with multiple different
applications. When the user looks in the diary they see entries for lots of
different things going on. The other tables are the application main tables
which contain a foreign key to the diary header. Now if the user deletes the
diary header it is virtually saying delete this application main table entry
too and vice versa. Well I mentioned it was a long story :). Anyway, it was
more of an abstract question - at present there is a normal foreign key
setup which is liveable with, I guess.

Steve


0
Steve
9/20/1999 7:38:11 PM
Set to Null - possible maybe. I'll bear that in mind, thanks.

Steve


0
Steve
9/20/1999 7:41:03 PM
However, it is a valid structure.  For instance, I have an Employee table
with all company employees and a Department table that groups employees
together.  The Employee table has a FK (Dept_ID) that points to Department.
However, each department needs a manager, so there is a FK (Mgr_ID) that
points back to Employee.  This is a simple cyclic relationship that could
set up the kinds of problems Jim is concerned about.  Many other business
situations are similar, so the key is to communicate (the mantra we should
all live by) clearly with everyone what is being designed, developed and
implemented so that no one is caught off-guard.

Now, Steve's situation with the 1:1 both ways with the "delete either" idea
sounds like interesting.  I can see making one the "parent", with cascade
deletes in the declarative RI, but the other way I might want to have a
trigger instead.

Any other ideas?

--
Michael F. Nicewarner [TeamSybase]
mailto:mike.nicewarner@ibpinc.com
http://www.datamodel.org

Jim Egan <jegan@dyn-data.com> wrote in message
news:MPG.1250342cffee740b98a162@forums.sybase.com...
> The first problem I see with this is that it is a "which came first, the
> chicken or the egg" type of a problem.
>
> When you insert into one table the FK is going to want to check to see if
> the value is in the other table.  And vice-versa.
>
> There is a way to get around this.  When you create the FK use the CHECK
> ON COMMIT option.  That way the FK validation doesn't occur until a
> COMMIT.
>
> I'm not a big fan of this.  It isn't all that intuitive.  Joe Blow
> programmer isn't going to know that the CHECK ON COMMIT option has been
> set.  He isn't going to understand what's going on.  To him it's all
> magic.
>
> My preference would be to just assume that one of the tables is the
> parent and the other is the child.  Also, if they are so tied together
> why are they two separate tables?
> --
> Jim Egan [TeamSybase]
> Dynamic Data Solutions, Inc. http://www.dyn-data.com
> Houston, TX


0
Michael
9/20/1999 8:50:54 PM
I agree that sometimes this arrangement makes sense.  however, I don't see why you can't declare
both sides CASCADE DELETE.  I'm pretty sure that I've tested this and it was OK. 


Leo Tohill - Team Sybase
>> Please post in newsgroup, not via email <<
0
leotohill
9/21/1999 4:17:20 AM
leotohill@csi.com (Leo Tohill) wrote:

>I agree that sometimes this arrangement makes sense.  however, I don't see why you can't declare
>both sides CASCADE DELETE.  I'm pretty sure that I've tested this and it was OK. 

Wow... when you think of it, there's no reason why it *shouldn't* work
<g>

Breck Still Trying To Get The Other Side Of His Brain To Work
Breck Carter [TeamSybase]
bcarter@bcarter.com
www.bcarter.com
0
NOSPAM__bcarter
9/23/1999 12:10:53 PM
jegan@dyn-data.com (Jim Egan) wrote:

>It isn't all that intuitive.  Joe Blow 
>programmer isn't going to know that the CHECK ON COMMIT option has been 
>set.  He isn't going to understand what's going on.  To him it's all 
>magic.

I disagree. RI is a debugging tool to help Joe Blow catch errors
early. CHECK ON COMMIT is a valuable technique which allows you to
*keep* RI checks in situations where otherwise you would have to
dispense with a foreign key altogether (e.g., a parent-child update
window where the user can do simultaneous inserts and deletes of both
parent and child rows).

Therefore, CHECK ON COMMIT is Mr. Blow's friend. He doesn't need to
understand how it works, he just needs to repair his mistake when an
RI error is diagnosed.

IMHO the only downside to CHECK ON COMMIT is the low quality of error
message: it doesn't tell you *which* check failed.

Breck
Breck Carter [TeamSybase]
bcarter@bcarter.com
www.bcarter.com
0
NOSPAM__bcarter
9/23/1999 12:17:52 PM
Reply:

Similar Artilces:

Table design question (to foreign key or not to foreign key)
Our application is a message board and is full of messages, threads, and forums. A message is an individual entry (like the one I'm typing now ;)). Our software gives the user the ability to upload a single file attachment to each message. I can think of a couple different ways to design the two tables: message( messageid numeric identity, attachmentid numeric null, messagetext text, other, message, stuff... ) attachment( attachmentid numeric identity, title varchar, other, stuff ) or message( messageid, messagetext text, other, stuff ) attachment( messageid numeric foreig...

Attempt to over-write foreign key column "Code" where table A has 3 foreign keys to table X
Generating from CDM to Logical. PD 9.5.2.889 This is a weird one - 4 tables in my model have more than one foreign key reference to table COROrg. By default a foreign ket to COROrg generates as COROrgKy. I have renamed the other foreign keys appropriately - e.g. COROrgKyEmpGrp. After the rename when I generate from CDM to Logical everything's fine for all but on of the foreign key columns (i.e. the new name/code are maintained in all but one). The list of changes to happen (obtained from Merge dialog -> Merge Preview) says it's going to 'Modify Column "...

Query to only display information from one table where the foreign key doesnt exist in the other table.
I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.  SELECT * FROM Computer where ComputerId NOT IN (SELECT ComputerId FROM [Hard Drives])*****...

integer primary/foreign key vs. varchar(255) primary/foreign key
Hello. Is there any principal performance (or some other) difference between using the integer primary/foreign key and using the string (varchar(255)) keys in queries that use tables joins? Server is Sybase SQL Anywhere 5.5.04 Yes! The primary key will only be made up of 12 bytes. By using an integer you ensure that your entire column is in the key. -- Jim Egan [TeamPS] Dynamic Data Solutions, Inc. http://www.dyn-data.com Houston, TX *********************************************************** Enterprise Applications in Your F...

Updating tables in sequence with primary key and foreign key relations
Hi all,        In my project i will have the  data in a collection of objects, I need to update series of tables with foreign key relations        Right now my code looks like this        foreach(object obj in Objects){        int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table        int DebtId=Debt....

Primary Keys containing common foreign keys from different tables
I have 2 tables Table 1 and Table 2 which have primary keys which include the same foreign key from a third Table 0. Table 1 has a primary key consisting of T1_KEY<pk> and T0_KEY<pk,fk>. Table 2 has a primary key consisting of T2_KEY<pk> and T0_KEY<pk,fk>. Now I want Table 3 to reference both Table 1 and Table 2 so that it has a primary key consisting of: T3_KEY<pk>, T0_KEY<pk, fk>, T1_KEY<pk, fk>, T2_KEY<pk, fk> I am trying to manually reverse engineer a database for which I only have a verbal description. I manage to achieve ...

Updating one table doesn't update another table using foreign key relationship
I created two tables named table1 and table2 using visual database tool . For table1 , the table defn is id int primary key ,name nvarchar(max) (allow null) and age int (allow null) For table2 , the table defn is id int primary key , address nvarchar(max) (allow null). I selected id from table1 and dragged it over to id in table2 and foreign key relationship was created. If i insert values into table1 say (2,"xyz",25) the values gets inserted. But if i select table2 to display id , it didn't show inserted id "2". I want table2 to update automatically i...

Foreign keys from one table to a parent field
I use the TableAdapter Configuration Wizard under VS2008. This is my database diagram: And this is my SELECT statement:   SELECT Parties.PartieID, Parties.RondeID, Parties.JoueurNoirID, Parties.JoueurBlancID, Parties.ScoreNoir, Parties.ScoreBlanc, Parties.VainqueurID, Parties.DetailVictoire, Rondes.Numero, Tournois.Nom, (SELECT Pseudonyme FROM Joueurs WHERE JoueurID = Parties.JoueurNoirID) AS JoueurNoir, (SELECT Pseudonyme FROM Joueurs WHERE JoueurID = Parties.JoueurBlancID) AS JoueurBlanc, (SELECT Pseudonyme FROM Joueurs WHERE JoueurID = Parties.VainqueurID) AS Vain...

assigning two foreign key to one table
hi all   can we assign two foreign key to one table .     Hi there, This can be done. 1. Go to the table design mode 2. Select the two columns using Ctrl tab. 3. Click on the primary key link and the two columns will be set to Primary key.  Kalyan Krishna.B(Web Developer)Email: kalyandotnetdeveloper@gmail.com Yes we can Satyabrat SinghB.E.(C.Sc.)Web developer Livetek Solution Dont forget to click "Mark as Answer" on the post that helped you. CREATE TABLE Employees(EmployeeID int identity(1,1) not null primary key clustered,EmployeeName ...

Primary key
Hello, I've got a modeling problem betwin a conceptual model and a physical model and espacially about the foreign key. See below the illustrated exemple of the problem : -Whether the entity � ARTICLE � with two informations : CODE & WORDING -Whether the entity � UNITE � with two informations : CODE & WORDING An article has necesseraly three unity, the unity of command, of storage and of sails. These three unity can be differents. Conceptual model phycical model The generated physical model doesn't suitable me. How ...

Primary Key/Foreign Key
Can I use SQL Commands to add Primary key or reference keys to existed tables? (Not a new table, that's why I can't use CREATE TABLE command.) If does, could someone give me some sample codes? Thanks. Hi John, What you need is the ALTER TABLE statement. Look for it in SQLA help for the syntax. Regards, Nat. John Chyan wrote in message <35A4FF5D.5F192806@dissol.com>... >Can I use SQL Commands to add Primary key or reference keys to existed >tables? (Not a new table, that's why I can't use CREATE TABLE command.) > >If does, could someone ...

Primary key/Foreign key again
Thanks, Nat and David. Under "SQL Anywhere", I get into ISQL to use "ALTER TABLE" command to add my primary key: ALTER TABLE Client ADD PRIMARY KEY (SYSID) I got an error: user 'dba' has the row in 'Client' locked. How can I release the locking condition? Click on another table in Sybase Central. Is suspect this is the issue. Dave Wolf Jaguar Product Team John Chyan wrote: > Thanks, Nat and David. > > Under "SQL Anywhere", I get into ISQL to use "ALTER TABLE" command > to add my primary key: > ...

AutoUpade UserId [Foreign Key] From the Primary Key UserId in the aspnet_Users table ?
 Hello everyoneI want the userId PrimaryKey in the aspnet_Users Table in the ASPNETDB.mdf  to Update it's foreign key in another table called ClientInfo in the same database, Once the User is created a new userId is created, i want this userId to be automatically entered in the foreign key table ClientInfo...how can that be accomplished?  You can have a trigger on aspnet_Users table doing the insert into the ClientInfo table.Regards,PrashantDont forget to click "Mark as Answer" on the post that helped you.  thanks for the fast reply..it would be great i...

Foreign key on a candidate key??
This is a multi-part message in MIME format. ------=_NextPart_000_011C_01C0E2D1.502EA3D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, How I do create a foreign key pointing to a candidate key. In other words, table A has an unique indexed field NUM and someother primary key. I would like to create a foreign key on Table B, pointing to A.NUM.=20 With PD V6, foreign keys can point to ONLY the primary key (or part of). Thanks. ------=_NextPart_000_011C_01C0E2D1.502EA3D0 Content-Type: text/html; charset...

Web resources about - Foreign key to other table, with foreign key back to first one: ok? - sybase.sqlanywhere.general

Foreign Intelligence Surveillance Act of 1978 Amendments Act of 2008 - Wikipedia, the free encyclopedia ...
The FISA Amendments Act of 2008 (also called the Foreign Intelligence Surveillance Act of 1978 Amendments Act of 2008 , H.R. 6304 , enacted 2008-07-10) ...

Western Sydney raids: alleged terrorist cell had Islamic flag, photos of foreign fighters
The arrests of Mohamed Rashad Almaouie, 20, and Abdullah Salihy, 24, brought to seven the number of men allegedly planning to attack police buildings ...

Former Neighbours, Underbelly actress Eliza Szonert calls for Foreign Minister Julie Bishop's help
Australian actress Eliza Szonert who snatched her son in Malaysia has been released from the local jail where she had been locked up for refusing ...

Burundi: We will not allow foreign troops to enter
... Union's decision to deploy a 5,000-strong peacekeeping force to curb ongoing violence in the troubled country, saying it will prevent foreign ...

Bernie Sanders Heads into Hillary Clinton's Foreign Policy Turf: The Democratic Debate By the Numbers ...
And Martin O'Malley stole the show on gun control, Bloomberg Politics analysis shows

Paul Ryan’s Christmas Warning: American Companies Will Shut Down Without More Foreign Workers
Paul Ryan defended his omnibus bill's expansion in the H-2B visa program by arguing that, without it, American companies would shut down.

What will biggest foreign policy challenges be for U.S. next year?
CBS News Senior National Security Analyst Juan Zarate discusses concerns ranging from ISIS to Bashar al-Assad and the type of terrorist threats ...

SAG Benefit Plans Accused Of Being A “Body Shop” For Illegal Foreign Workers
... of firing an older female employee who was undergoing treatment for breast cancer and, to save money, replacing her with a younger, male foreign ...

Taiwan attracts foreign direct investment of over US$4.1 billion in January-November, says MOEA
Taiwan's Ministry of Economic Affairs (MOEA) approved 3,412 foreign direct investment projects (except from China) with a total value of US$4.128 ...

Buy Rosetta Stone, Farina Tells Parents Upset Over Foreign Language Cuts
... schools chief said parents should buy the language teaching material for their kids. FORT GEORGE — Parents worried about cuts to school foreign-language ...

Resources last updated: 12/25/2015 7:03:54 PM