MySQL HELP

anotherF · 2 Oct 2006 at 11:19

I am after a little help with my database.

I have a db with a few tables the first table has an id field which auto increments

The other tables have id fields also that need to match the auto incrementing id field so the records match up!

How do you do this?

Dose this question make sense

Dj_Jestar · 2 Oct 2006 at 11:37

You don't, you create another column/field to contain the key from the first table.

e.g.:

Code:

CREATE TABLE `users` (
  `userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(40) NOT NULL,
);

CREATE  TABLE `messages` (
  `messageid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `messagetitle` VARCHAR(60) NOT NULL,
  `userid` INT NOT NULL,
  `messagebody` TEXT
);

Then you insert the userid of the user who sent the message into the `messages`.`userid` column. Creating an index on this, or even setting it as a foreign key may not be a bad idea either.

anotherF · 2 Oct 2006 at 11:45

Thanks i will try that.

toString · 2 Oct 2006 at 12:15

OT:
How come they make a big fus about forgien keys at uni, and then when you're in the real world harldy anyone uses them. Just speaking from experience. And this is not a complaint! :cool:

Dj_Jestar · 2 Oct 2006 at 12:30

What I've posted is a Foreign Key, it's just not using the FOREIGN KEY keywords.

I haven't stumbled upon a situation where I actually have needed to yet, but one use I can think of is when records are created/updated/deleted on one table, any other table that references the first table will be updated as well. (i.e. changes are cascaded)

But as I do this manually anyway..

toString · 2 Oct 2006 at 12:34

Dj_Jestar said:
But as I do this manually anyway..

Yeah me too! It just seems more effective the 'manual' way.
Perhaps it's another case of lecturers not being in touch with the real world. But I guess it's good practice.

Dj_Jestar · 2 Oct 2006 at 12:36

There are uses for it, I just haven't stumbled upon them yet personally.

Mickey · 2 Oct 2006 at 13:29

Foreign keys are great for keeping database consistency, especially when combined with cascading deletes.

Trying to do this manually in all of your code must be...fun?

Mick.

Dj_Jestar · 2 Oct 2006 at 13:55

That is such a subjective statement.

You may have visions of 20+ tables, where as I only have one or two.

I've also rarely come into the instance where I actually need to delete a record now you mention it, infact only in testing/dev do I ever delete records.