Two tables out of sync?

[Paul Marcel]

I'm new to databases and am experimenting with mysql.

I have a clients table and a sales table in a mysql database.
Client_id is the primary key in clients and a secondary key in sales.

The only client_id's that show up in the clients table are client_id's
starting at 28 (they autoincrement from there). 1-27 were old ones
that I used in testing. I added a new client and clients shows the new
client_id as 188 which makes sense. I added a new sale to the sales
table for this client.

But in sales the client_id is set to '1'. Why isn't it 188?
Probably '1' was the original test client_id and had the same data.
The database seems to have remnants left over from past deleted
records from the clients table.

Does anyone know what's going on? Is there something like a 'pack' or
're-sync' command in mysql?

thanks,
Paul

[Aggro]

Paul Marcel wrote:

But in sales the client_id is set to '1'. Why isn't it 188?

Sounds to me like you are doing something very wrong which will propably
cause you even more problems later.

You should have something like this:

--------------
| tableone
| ---------
| id
| something_else
--------------

--------------
| tabletwo
|-------------
| id
| tableone_id
| something_else
--------------

And when you insert stuff into tabletwo, that should be linked to
tableone, you need to make sure that the value in tabletwo.tableo ne_id
is the correct id value from tableone.id. Common way to do this is to
use the last_insert_id( ), to get the id for previously inserted row. (
You don't have to have the id-field in tabletwo, but it is commonly a
good idea to have a unique field in table which can be used to identify
rows. )

See this manual page for more info how to use it:
http://dev.mysql.com/doc/mysql/en/Ge...unique_ID.html

[Bill Karwin]

Paul Marcel wrote:

I added a new client and clients shows the new
client_id as 188 which makes sense. I added a new sale to the sales
table for this client.
But in sales the client_id is set to '1'. Why isn't it 188?

Each table has its own auto_increment counter. They don't share values.
Generally one doesn't define a foreign key as an auto_increment,
because you need to specify explicit values that actually exist in the
referenced table. Inserting foreign key values is not automatic or
implicit.

Does this help? It's hard to tell if this really addresses your
problem. Can you provide the insert statements that you used for both
tables?

Regards,
Bill K.

[Paul Marcel]

On Sat, 11 Dec 2004 13:58:56 -0800, Bill Karwin <bi**@karwin.co m> wrote:

Paul Marcel wrote:

I added a new client and clients shows the new
client_id as 188 which makes sense. I added a new sale to the sales
table for this client.
But in sales the client_id is set to '1'. Why isn't it 188?

Each table has its own auto_increment counter. They don't share values.
Generally one doesn't define a foreign key as an auto_increment,
because you need to specify explicit values that actually exist in the
referenced table. Inserting foreign key values is not automatic or
implicit.

Does this help? It's hard to tell if this really addresses your
problem. Can you provide the insert statements that you used for both
tables?

Regards,
Bill K.

Hi,

I'm getting back to the mysql database problem that I was having.
I ran mysqldump and I get this:

DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
`client_id` int(10) unsigned NOT NULL auto_increment,
`lastname` char(15) NOT NULL default '',
`firstname` char(15) NOT NULL default '',
`birthdate` date NOT NULL default '0000-00-00',
`username` char(20) NOT NULL default '',
`email` char(40) NOT NULL default '',
PRIMARY KEY (`client_id`),
KEY `lastname` (`lastname`),
KEY `firstname` (`firstname`),
KEY `birthdate` (`birthdate`),
KEY `username` (`username`),
KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
`client_id` int(10) unsigned NOT NULL default '0',
`xname` char(80) NOT NULL default '',
`ms` int(10) unsigned NOT NULL default '0',
KEY `xname` (`xname`),
KEY `ms` (`ms`),
KEY `client_id` (`client_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I created the datbase and tables from a php script. The dump is a little different
from my original script. In the script I had this line in the sales table statement:

constraint foreign key (client_id) references clients (client_id)

instead of the dump's:

KEY `client_id` (`client_id`)

I am trying to have client_id as a primary key in clients and as a foreign key in sales.
Is that happening? I don't see any difference between the key statemens for xname, ms, or client_id
in the sales table.

In clients I added a few other keys because I think that I will need to search on fields like
'lastname'. Did I do that correctly? Does the key directive cause mysql to make an index for that
column? I've seen an explicit index command, but I'm not sure that is necessary.

Should I create these extra keys (lastname, firstname, birthdate, username, email, xname, ms) for
fast searching? Did I do it correctly?
thanks,
Paul

[Bill Karwin]

Paul Marcel wrote:

I am trying to have client_id as a primary key in clients and as a foreign key in sales.
Is that happening? I don't see any difference between the key statemens for xname, ms, or client_id
in the sales table.
If you define your tables with the ENGINE=MyISAM clause, foreign keys
are not supported. MySQL will read the command and not complain, and it
does create an index for the column, but there it keeps no record of the
reference between the tables, nor does it enforce the referential
integrity. That is, you can put a value into sales.client_id that does
not exist in clients.client_ id.

To have foreign key referential integrity enforced, use the InnoDB table
type. See the documentation for more information.
In clients I added a few other keys because I think that I will need to search on fields like
'lastname'. Did I do that correctly? Does the key directive cause mysql to make an index for that
column? I've seen an explicit index command, but I'm not sure that is necessary.

INDEX and KEY are synonymous, as far as I know. They are
interchangeable because they accomplish exactly the same thing.

Yes, creating an index on a field is likely to speed up some types of
queries that use that field for table joins, WHERE conditions, or
sorting. But actually this depends on the type of query, and the
variability of data in the field. MySQL tries to be smart about when it
can gain benefit from using an index or not.

For instance, if you sort by a field that has exactly the same value on
every row, there's no benefit from using an index, and in fact it's
unnecessary extra work. MySQL tries to detect conditions like those.

If your data and pattern of typical queries is likely to preclude
usefulness of the index, don't bother to create an index. It costs a
little bit of work for MySQL to maintain the index every time you insert
or update data, and if it never benefits your queries, that's just a
waste. You can always add indexes later if the usage patterns change.

Regards,
Bill K.