primary keys

Anon

I have a database with 3 separate tables, and each table has it's own primary key. However each table will be linked together using these primary keys. Do I need to define each primary key of all the tables in each table?

For examle, if I have table 1 with Primary Key A, and Table 2 with primary key B. Table 2 will be related to Table 1 using the Primary key A so do I need to define the primary key A in Table 2? Or is there a way I can just link to it without having to redefine it?

I hope that doesn't sound to complicated.

Thanx,
Chris

Anon

You need the concept of foreign keys.

Mysql doesn't support foreign keys per se but the concept is very helpful.

If table A has primary key column called:
keyA
and table B has a primary key column called:
keyB

And you want table B to be related in some way to table A, you need to have the primary key column in table B (key😎 in addition to the foreign key column (keyA) from table A

In table B the column keyB would be a unique id for each row in that table, while the column keyA would represent rows from table A. The keyA column in table B would not have to be unique.

I'm not sure that this is the best way to describe this, but should set you in the right direction.

matt.

Anon

Thanks, that kinda helps, do you know of an example of this of some MySQL sourcecode on the web somewhere?

-Chris

mattee

Here are three related tables.

The email and newsletter tables are related via the subscription table.

Notice how the primary keys of the email and newsletter are located in the subscription table as foreign keys.

The subscription table is known as a "relationship" table.

You probably want to find a book on database design.

CREATE TABLE pr_email (
email_id int(11) DEFAULT '0' NOT NULL auto_increment,
email varchar(100) NOT NULL,
PRIMARY KEY (email_id)
);

CREATE TABLE pr_newsletter (
newsletter_id int(11) DEFAULT '0' NOT NULL auto_increment,
name varchar(100) NOT NULL,
description text NOT NULL,
PRIMARY KEY (newsletter_id)
);

CREATE TABLE pr_subscription (
id int(11) DEFAULT '0' NOT NULL auto_increment,
email_id int(11) DEFAULT '0' NOT NULL,
newsletter_id int(11) DEFAULT '0' NOT NULL,
date_subscribed int(10) unsigned DEFAULT '0' NOT NULL,
validated tinyint(3) unsigned DEFAULT '0' NOT NULL,
http_referer varchar(200) NOT NULL,
PRIMARY KEY (id)
);

Anon

wow, thanks! That does help a lot to see concrete examples. I just bought an O'Reilly book called MySQL &mSQL. It's ok, but I'd like to find something better. Any ideas?

Thanks,
Chris

mattee

That's the same book I started with. It's not bad and you'll get more out of it each time you read it. But it's not really a beginners sql book.

I also bought the sams teach yourself sql but I don't recommend it. It's more for folks using ms-sql or oracle and covers lots of features that mysql doesn't support.

I'm assuming your are using mysql.

I learned by doing just what you are doing. Asking and trying things out. Not the quickest way, but effective.

Things to investigate would be database normalization, database theory, keys, select statements, where, etc... Read the articles on phpbuilder, there are at least two that I know of, they cover things fairly well. It's a good start anyway.

The mysql manual at www.mysql.com does a fairly good job of explaining things too.

If you do end up finding a good beginner to intermediate type sql book, let me know.
matt.