List:General Discussion« Previous MessageNext Message »
From:Arthur Fuller Date:October 23 2002 6:05pm
Subject:RE: how do you define a relationship?
View as plain text  
Perhaps what is confusing you is that you never explicitly define a
relationship as such: there are no relationship objects. Rather, at the
table leve you define a foreign key that references the primary key of
another table.

Second, you must use the InnoDB or BDB table types to get foreign keys.

Both tables have to be InnoDB type and there must be an index where the
foreign key and the referenced key are listed as the first columns. InnoDB
does not auto-create indexes on foreign keys or referenced keys: you have to
create them explicitly.

Finally, here is the (relevant part of the) syntax:

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
                  REFERENCES table_name (index_col_name, ...)
                  [ON DELETE CASCADE | ON DELETE SET NULL]

Just tack a clause like this onto the end of your CREATE TABLE statement. If
your tables are already built, then you can use ALTER TABLE instead. Look in
the manual under CREATE TABLE and ALTER TABLE.

hth,
Arthur

-----Original Message-----
From: Hammons Randy G SSgt 612 ACOMS/SCXX
[mailto:Randy.Hammons@stripped]
Sent: Monday, October 21, 2002 3:48 PM
To: mysql@stripped
Subject: RE: how do you define a relationship?


Unfortunately this goes well beyond the scope of my original question.
Although the inputs that you all have provided thus far have been a very
good read, I still don't grasp the concept of defining a relationship.  Is
it simply referring to a primary key in a field other than the one where the
key was defined?  Or is there some other way to define a key that I don't
see?  All I'm looking for is a simple example, syntax excerpt, etc...that
shows me (only the "slow learner" level) exactly how to define a
relationship.

-----Original Message-----
From: Jan Steinman [mailto:Jan@stripped]
Sent: Monday, October 21, 2002 12:07 PM
To: mysql@stripped
Subject: RE: how do you define a relationship?


>From: "Arthur Fuller" <afuller@stripped>
>
>Unfortunately that oversimplifies the situation. A least a few movies have
>more than one director.

My motto: "Generalize for the norm; specialize for the exception."

The vast majority of movies have but one director. And I suspect that movies
with multiple directors have but one who is primary.

Keep a single director field, with a second NULL FULLTEXT field for
additional directors. The NULL test for the normal case is much cheaper than
what you have to go through by assuming that ALL movies have multiple
directors!

This is how books are handled in large databases. Each has a primary author,
and may contain secondary authors. There is no assumption that large numbers
of books have multiple, equal authors.

This way, you can easily and conveniently list multiples as "Speilberg (et.
al.)" without doing joins. An interested browser can then go further to find
out who the others are. In the other case, each request for a director
requires a JOIN.

Of course, your particular application may be director-centric, like if
you're building a special database to support research on directors. But if
it's just a general-purpose movies database, why bog the whole thing down
just to suit a few exceptional cases?

---- SQL SQL SQL SQL SQL SQL SQL SQL  ----
--
: Jan Steinman -- nature Transography(TM): <http://www.Bytesmiths.com>
: Bytesmiths -- artists' services: <http://www.Bytesmiths.com/Services>
: Newsletters now on-line at <http://www.Bytesmiths.com/Newsletter>

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread122834@stripped>
To unsubscribe, e-mail
<mysql-unsubscribe-randy.hammons=dm.af.mil@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread122840@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-afuller=etsys.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



Thread
how do you define a relationship?Randy Hammons20 Oct
  • Re: how do you define a relationship?Richard Clarke20 Oct
  • RE: how do you define a relationship?bwarehouse21 Oct
    • RE: how do you define a relationship?Arthur Fuller21 Oct
      • Re: how do you define a relationship?Michael T. Babcock21 Oct
RE: how do you define a relationship?Jan Steinman21 Oct
Re: how do you define a relationship?Michael T. Babcock21 Oct
RE: how do you define a relationship?Hammons Randy G SSgt 612 ACOMS/SCXX22 Oct
  • Re: how do you define a relationship?Peter Brawley22 Oct
  • RE: how do you define a relationship?Arthur Fuller23 Oct
    • re: RE: how do you define a relationship?Egor Egorov24 Oct
  • Re: how do you define a relationship?Michael T. Babcock8 Nov