MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Rolf Hopkins Date:October 6 2000 6:10am
Subject:Re: Newbie Question
View as plain text  
What you have assumed is correct

This is a little too simple as you wouldn't even bother splitting up the
table like that.  You would just have one table, depending.  It is also not
very good example because of Date.  Instead of date, perhaps it is better to
have year (ie year 1, 2 and 3 not 1998) or semester or something similar.
Anyway, let's change it slightly for example purposes only.

> > Table Students:
> > |  Student_Name |  Student_ID |
> >     John                      1
> >     Mary                     2
> >     Peter                     3
> >
> > Table Tests:
> > | Student_ID | Score |    Subject   |
> >       1                94         Maths
> >       1                80         English
> >       2                97         Maths
> >       3                89         Maths

Now you can do simple queries like

SELECT t.Student_ID, t.Subject
FROM Students s,  Tests t
WHERE t.Student_ID = s.Student_ID
AND s.Student_Name = 'Mary'

Note that in the table Students, Student_ID is unique but in Tests, you can
have duplicate Student_IDs.  Ie 1 student, many subjects.

In Students, Student_ID is the primary key so that it can uniquely identify
each row.  You can also make (Student_ID, Subject) pair a primary key to,
again, uniquely identify each row in the Tests table.  Note that this time,
Student_ID alone, can not be primary key, however it can have an index.
Primary keys are automatically indexed so you don't have to do anything
there but you will need to define Student_ID alone as an index.  As I said,
indexes can be used to speed up queries.

In other DBs, Student_ID in Tests table can also be defined as a foreign key
of the Students table.  This means, if you add the values (4, 98, 'English')
to the table Tests, the DB would report an error because there is no student
with an ID of 4.

On the other hand, if you deleted Mary, you would also get an error because
Mary still has results in the Tests table.  This is known as referential
integrity.  Some databases (ie. MS ACCESS), allow for cascading.  In other
words, if you change Mary's ID to 4, it would automatically update the Tests
table without you doing anything.  Again, if you delete Mary, instead of an
error, it would automatically delete Mary's results from the Tests table.

If you don't understand/know this, you need to find a good general book on
RDBMSs.

Now, in MySQL, there is no referential integrity.  It was sacrificed for
speed.  In other words,  inserting (4, 98, 'English') into Tests or deleting
rows with ID 2 from Tests is perfectly allowable even though you have
defined a foreign key.  You, as a programmer/user, need to make sure that
this does not happen.

Cheers

Rolf


----- Original Message -----
From: "Eric" <edahnke@stripped>
To: "Mike Kaiser" <kaisermj@stripped>
Cc: <mysql@stripped>
Sent: Friday, October 06, 2000 12:43
Subject: Re: Newbie Question


>
> i recently went through the same confusion and thought processes. the
answer
> for me was that the type of logic you're looking for is how you INSERT
data and
> within your SELECT statements to get the data out.  you manage the
referencial
> integrity.
>
> you can pull different columns from different tables with a query. perhaps
Rolf
> can show the correct syntax to obtain the result you describe below.
>
>
>
>
>
> Mike Kaiser wrote:
>
> > Rolf,
> >
> > Woe indeed!  I'm either incredibly thick-headed or we're having a
"failure
> > to communicate" (although I'm much more inclined to believe the former).
> > Let's look at a very simple example:
> >
> >                        - Database School -
> > Table Students:
> > |  Student_Name |  Student ID |
> >     John                      1
> >     Mary                     2
> >     Peter                     3
> >
> > Table Tests:
> > | Student ID |      Date        | Score |
> >       1             2000-10-01     94
> >       2             2000-10-01     97
> >       3             2000-10-01     89
> > In the table "Students" Student_ID is unique, not null, and
auto-indexed.
> > Am I right in assuming that the Student_ID in the table "Tests" should
come
> > from table "Students"?  So at some future point I could query the
database
> > to list students who got a 90 or better on a test given on October 1st.
Or
> > is this simply the wrong approach when designing a database?
> >
> > Mike
> > ----- Original Message -----
> > From: "Rolf Hopkins" <rhopkins@stripped>
> > To: "Mike Kaiser" <kaisermj@stripped>; <mysql@stripped>
> > Sent: Thursday, October 05, 2000 11:25 PM
> > Subject: Re: Newbie Question
> >
> > > Woe, woe, woe.
> > >
> > > Maybe I misunderstood you but I thought you asked if MySQL can do what
MS
> > > Access can do, for example.  Cascading updates and deletes when
> > referential
> > > integrity is enforced.
> > >
> > > And the answer is NO it can't.  This is clearly stated in the manual.
It
> > > only has foreign keys for compatibility only with other DBs but it
doesn't
> > > do anything.
> > >
> > > Indexes have very little to do with referential integrity.  Indexes
can be
> > > used for data integrity in terms of uniqueness but mainly to speed up
> > > qeuries.
> > >
> > > Eric is right, indexes aren't the answer.
> > >
> > > Cheers
> > >
> > > Rolf
> > >
> > > ----- Original Message -----
> > > From: "Mike Kaiser" <kaisermj@stripped>
> > > To: <mysql@stripped>
> > > Sent: Friday, October 06, 2000 10:52
> > > Subject: Re: Newbie Question
> > >
> > >
> > > > Rolf,
> > > >
> > > > Thanks for the reply.  I went out tonight and spent some time
looking in
> > > > MySQL by Paul DuBois and Monty and pretty much figured it out.  What
I
> > > don't
> > > > understand why the answer to my question is so hard to find.  In the
> > book
> > > > the "tutorial" chapter discusses indexing tables and querying by
index
> > but
> > > > they just seem to pass right over the part about how one gets two
> > indexes.
> > > > There's a little database with school children and grades that
clearly
> > > shows
> > > > the two indexes but no mention of how their obtained.  Well the
problem
> > is
> > > > solved, although I'm sure I'll be back with another question or two.
<g>
> > > >
> > > > Thanks again!
> > > >
> > > > Mike
> > > > ----- Original Message -----
> > > > From: "Rolf Hopkins" <rhopkins@stripped>
> > > > To: "Mike Kaiser" <kaisermj@stripped>;
> <mysql@stripped>
> > > > Sent: Thursday, October 05, 2000 9:59 PM
> > > > Subject: Re: Newbie Question
> > > >
> > > >
> > > > > Mike,
> > > > >
> > > > > That's because MySQL doesn't support referential integrity and
> one
> > > reason
> > > > > why it is the fastest DB in the world that I know of.
> > > > >
> > > > >
> > > > > Cheers
> > > > >
> > > > > Rolf
> > > >
> > > >
> > > >
> > > > --
> > >
> ---------------------------------------------------------------------
> > > > Please check "http://www.mysql.com/documentation/manual.php" before
> > > > posting. To request this thread, e-mail
> > mysql-thread52593@stripped
> > > >
> > > > To unsubscribe, send a message to:
> > > >    
> <mysql-unsubscribe-rhopkins=mail.waytech.com.tw@stripped>
> > > >
> > > > If you have a broken mail client that cannot send a message to
> > > > the above address (Microsoft Outlook), you can use:
> > > >     http://lists.mysql.com/php/unsubscribe.php
> > >
> >
> > --
> > ---------------------------------------------------------------------
> > Please check "http://www.mysql.com/documentation/manual.php" before
> > posting. To request this thread, e-mail
mysql-thread52599@stripped
> >
> > To unsubscribe, send a message to:
> >     <mysql-unsubscribe-edahnke=istreetlabs.com@stripped>
> >
> > If you have a broken mail client that cannot send a message to
> > the above address (Microsoft Outlook), you can use:
> >     http://lists.mysql.com/php/unsubscribe.php
>
>
> --
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/documentation/manual.php" before
> posting. To request this thread, e-mail mysql-thread52602@stripped
>
> To unsubscribe, send a message to:
>     <mysql-unsubscribe-rhopkins=mail.waytech.com.tw@stripped>
>
> If you have a broken mail client that cannot send a message to
> the above address (Microsoft Outlook), you can use:
>     http://lists.mysql.com/php/unsubscribe.php

Thread
MySQL-HELPJordi Amor√≥s Castell√†5 Oct
  • Re: MySQL-HELPVivek Khera5 Oct
  • Newbie QuestionMike Kaiser5 Oct
  • Re: MySQL-HELPBenjamin Pflugmann5 Oct
  • Re: Newbie QuestionRolf Hopkins6 Oct
  • Re: Newbie QuestionEric6 Oct
  • Re: Newbie QuestionMike Kaiser6 Oct
  • Re: Newbie QuestionMike Kaiser6 Oct
  • Re: Newbie QuestionEric6 Oct
  • Re: Newbie QuestionRolf Hopkins6 Oct
  • Re: Newbie QuestionMike Kaiser6 Oct
  • Re: Newbie QuestionEric6 Oct
  • Re: Newbie QuestionRolf Hopkins6 Oct
  • Re: Newbie QuestionMike Kaiser6 Oct
  • Re: Newbie QuestionRolf Hopkins6 Oct
RE: Newbie QuestionJohn Foley6 Oct