Bruce,
It all depends on what you are storing in foo1 and foo2.....
For a one-to-many relationship, you need to have that ID column in your
child table to reference the parent record.
The "classic" example of a one-to-many relationship is Companies to
Employees. In the real world there are some cases where one person "could"
work for more than one company at a time but not for this example....
CREATE TABLE Company (
ID int auto_increment
, Name varchar(40) primary key
,Key(ID)
);
CREATE TABLE Employee (
ID int auto_increment
, Company_ID int
, Name varchar(40) primary key
,KEY(ID)
);
INSERT Company (Name) values ('ABC Corp'),('XYZ Supply Company'),('Some
Other Company');
INSERT Employee (Name, Company_ID) Value ('J Smith',1), ('K Jones',1), ('S
Serpentine', 2), ('B Dog', 2), ('S Pony',3);
This results in the following data:
SELECT* FROM Company;
+----+--------------------+
| ID | Name |
+----+--------------------+
| 1 | ABC Corp |
| 2 | XYZ Supply Company |
| 3 | Some Other Company |
+----+--------------------+
3 rows in set (0.04 sec)
SELECT * FROM Employee;
+----+------------+--------------+
| ID | Company_ID | Name |
+----+------------+--------------+
| 1 | 1 | J Smith |
| 2 | 1 | K Jones |
| 3 | 2 | S Serpentine |
| 4 | 2 | B Dog |
| 5 | 3 | S Pony |
+----+------------+--------------+
5 rows in set (0.00 sec)
This shows that J Smith and K Jones both work for ABC Corp. The value in
the Company_Id field must exist in both tables to form that relationship.
What exactly are you trying to model?
In regards to your question about unique ID numbers between tables, you may
be looking for the term "foreign key" but I won't know if that applies to
you until I understand your situation better.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"bruce"
<[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
ink.net> cc:
Fax to:
07/08/2004 12:49 Subject: table layout structure
PM
Please respond to
bedouglas
hi...
i have an issue/prob and i'm trying to figure out the best approach...
i have multiple tables foo1, foo2
foo1: foo2:
- name - name
- id - foo1_id
- id
- unique key (id, name, foo1_id)
my question: does mysql provide a way to do a unique key on table items
between multiple tables, or do i need to essentially create my logic/code
to
handle/ensure the relationships myself. im my above exmaple, i'd rather not
have to have "foo1_id" in table foo2 as it is a duplicate of "foo1.id"....
i'd really like to be able to do something like
"unique key (id, name, foo1.id)"<<<<<
any thoughts/comments/criticisms...
thanks
-bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
