473,230 Members | 1,449 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

Two tables out of sync?



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

Jul 20 '05 #1
4 2622
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.tableone_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
Jul 20 '05 #2
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.
Jul 20 '05 #3
On Sat, 11 Dec 2004 13:58:56 -0800, Bill Karwin <bi**@karwin.com> 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

Jul 20 '05 #4
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.
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Perttu Pulkkinen | last post by:
I want to copy certain tables from server to server b. How do I make it fastly and efficiently with PHP? I wrote below my current start, but it may not be the best approach. Perttu Pulkkinen,...
0
by: Ferindo Middleton Jr | last post by:
I am trying to write a Perl Function for one of the databases I'm building a web application for. This function is triggered to occur BEFORE INSERT OR UPDATE. This function is complex in that it...
1
by: kartik | last post by:
I open an fstream in read-only mode, read till the end, then try to sync() before seeking to position 0 & reading again. But the sync fails. I discovered that clear()ing the stream before the sync...
4
by: Stefan Kowalski | last post by:
I recently posted a question which was answered by Allen Browne and gave me some tips to structure the tables. However, when it comes to searching the database, performance is unacceptably slow...
2
by: DickChristoph | last post by:
Hi I tried posting this query in microsoft.public.sqlserver.programming but got no response. I am new to replication but I am trying to setup up a transactional replication of tables from one...
5
by: rdemyan via AccessMonster.com | last post by:
I have a need to add another field to all of my tables (over 150). Not data, but an actual field. Can I code this somehow. So the code presumabley would loop through all the tables, open each...
2
by: Kees de Winter | last post by:
Hi, I am thinking about using the personalization features of .net 2.0 but I can't figure out the following. If I use the standard features of personalization, I will have users in the...
0
by: sachjn | last post by:
Hi, I have three tables with columns as: Person_sys1 Id(pk) name telephoneNum userid ----- -------- ---------------------- --------- Person_sys2 Id(pk) name ...
7
by: john | last post by:
I am reading TC++PL3 and on page 644 it is mentioned: "Flushing an istream is done using sync(). This cannot always be done right. For some kinds of streams, we would have to reread characters...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.