MySQL HELP

Associate
Joined
5 Feb 2006
Posts
129
Location
Birmingham
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
 
Caporegime
Joined
18 Oct 2002
Posts
29,491
Location
Back in East London
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.
 
Soldato
Joined
18 Oct 2002
Posts
8,997
Location
London
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:
 
Caporegime
Joined
18 Oct 2002
Posts
29,491
Location
Back in East London
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..
 
Soldato
Joined
18 Oct 2002
Posts
8,997
Location
London
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.
 
Soldato
Joined
18 Oct 2002
Posts
3,157
Location
Leeds
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.
 
Caporegime
Joined
18 Oct 2002
Posts
29,491
Location
Back in East London
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.
 
Back
Top Bottom