Unique ID carried to a table where ID is not unique.

a2jfreak

I'm not great (or even good) with SQL of any kind but I think this is a relational question.

Say I have a database named "MyDatabase" with two tables: "FirstTable" and "SecondTable" where both have a field named "ID" ID is unique on FirstTable, but SecondTable it isn't unique (so as to allow the listing of multiple rows that relate to the same ID).

FirstTable fields:
ID, Buyer, Address, Telephone, Fax, Email

SecondTable fields:
ID, PartPurchased, Price, Quantity

Say "Buyer" (Bob's Inc.) purchases 3 widgets at $3.25 each and 7 sprockets at $4.15 each. I insert into FirstTable "Bob's Inc." along with his other information. Since "Bob's Inc." isn't a unique field--nor are any fields other than "ID"--so I can't query just the "Buyer" field and be assured the ID returned is the ID of the newly added record. I could query multiple fields (since no two Buyers are going to have the exact same field values) to find out what the newly-assigned ID is and use that to make my inserts into SecondTable for the parts Bob's Inc. purchased, but isn't there any easier and less error-proned way?

Park_7677

I would have MySQL handle the auto_increment and uniqueness

CREATE TABLE `FirstTable` (
`ID` tinyint(4) NOT NULL auto_increment,
`Buyer` text NOT NULL,
`Address` text NOT NULL,
`Telephone` text NOT NULL,
`Fax` text NOT NULL,
`Email` text NOT NULL,
UNIQUE KEY `ID` (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

When you insert a row, just leave "ID" blank and MySQL does it for you

Wired

Park, I think he already has it auto incrementing.

a2jfreak, I don't quite understand your question. Could you rephrase it? If I'm understanding what you have so far, your first table has a unique ID and the rest is buyer info that could all repeat. Although, if it all could be identical to another row, then why bother putting it into the table? Anyway, you're taking that ID and placing it into the second table with the order info.

What I'd do for organizing purposes is this for the SecondTable fields:
ID, FirstTableID, PartPurchased, Price, Quantity

This way, each order is unique, yet still retains the info from the first table. It's all in normalization, it's your friend!

Park_7677

Wired wrote:

Park, I think he already has it auto incrementing.

He might, now that I reread the post for like the 10th time

Are you looking for how to find the highest (latest) added entry so you can reference it?

a2jfreak

Yes.

Park_7677 wrote:

He might, now that I reread the post for like the 10th time

Are you looking for how to find the highest (latest) added entry so you can reference it?

Park_7677

SELECT * FROM firsttable ORDER BY ID DESC LIMIT 1

Done

That selects the whole row. You can limit it to just returning ID by removing * and putting ID in its place.

a2jfreak

Thanks much, Park (and Wired)!

Wired

No problemo!