This is my example scenario.
I want to insert into this database. My record comes across having
both the customer info and the order info, one record at a time. My
question is:

Given that:
There is are 0 to n orders for a customer.
A customer record can not be deleted until all their orders are
deleted.

Do I insert the customer data into the customer table first...
I assume it is alright for that to fail as the customer is unique
and might be being added for the second time.
I assume the primary key be created for me by the auto increment

Have I got the Foreign Key setup properly? It should point to
customer.ID
What does the insert statement look like for this record?

ENGINE=InnoDB; I guess this is what MySQL prefers. I don't know if
I've handled Index files correctly...

I hope this question is phrased better this time...




Here is the table creation sql...
CREATE SCHEMA MyTest;
USE MyTest;

CREATE TABLE IF NOT EXISTS `test`.`customer` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`Cust_Name` VARCHAR(32) NOT NULL ,
PRIMARY KEY (`ID`) ,
UNIQUE INDEX `cust_id_UNIQUE` (`ID` ASC) )
ENGINE = InnoDB;


--
-----------------------------------------------------
-- Table
`test`.`orders`
--
-----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test`.`orders` (
`Order_ID` INT NOT NULL ,
`Customer_ID` INT NULL ,
`Order_Date` DATE NULL ,
`Amount` DOUBLE NULL ,
PRIMARY KEY (`Order_ID`) ,
INDEX `ID` () ,
CONSTRAINT `ID`
FOREIGN KEY ()
REFERENCES `test`.`customer` ()
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

On 27-01-11 18:45, SpreadTooThin wrote:
^^ above line is not correct

^^ above line is not correct

try to read the docs again on CREATE TABLE, and on FOREIGN KEYS

http://dev.mysql.com/doc/refman/5.0/...ate-table.html

http://dev.mysql.com/doc/refman/5.0/...nstraints.html

(both link are for 5.0, but in this case, nothing much changed)

--
Luuk

On 27-01-11 18:45, SpreadTooThin wrote:
Ye, you do insert the customer first, but only if it does not exists!
There is no reason for storing a customer more than once, is there?


The INSERT looks just like a normal INSERT

INSERT into `customer` (<fields>) VALUES (<values>) ;



--
Luuk

On 1/27/2011 12:45 PM, SpreadTooThin wrote:
OK, that's pretty common

In such a scenario, you have to have a customer table present before you
can enter an order. Otherwise you have no customer to attach the order to.

However, they are not necessarily added at the same time, and you
shouldn't get duplicates for the customer. For instance, when a person
registers on the system, that would create a customer which could be
used for multiple orders. They may or may not have any orders in the
system, but they are registered as a customer.

On the other end, if a person chooses not to register, they would get a
customer entry at checkout time. They could chose to make this a
permanent customer entry, i.e. by supplying registration information
like passwords, etc., or a temporary customer only for this order.

But if they attempt to create a second customer with the same
information as the first i.e. same user id, same email address,
whatever), then they would get an error message to that effect instead
of a duplicate row being created.

But in no case should you have duplicate customers, and attempts to add
a duplicate should not be ignored.

That's a good way to do it.

See below

The same as it would look for any table. Whether or not you have a
FOREIGN KEY does not affect INSERT statement syntax. It just adds extra
constraints to the values in a column.

If you want foreign key constraints to work, yes. MyISAM supports the
syntax but ignores the actual relationship.

I would assume you would have more information than just cust_name in
here. Things like userid (which may or may not be the customer's name),
email (might be the userid), password, etc.

Also, you don't need a index on ID - the fact it is the primary key
automatically creates a unique index on the column.

Also, drop the `test` - if you want to create in the test database, USE
the database first. Then your SQL is easily transportable to a
production table.

One other thing - I detest using the backticks - they are a MySQLism,
and only required if you use reserved words in your table or column
names. Among other things, the statements are not transportable to
another database. But then it's better not to use reserved words
anyway, in which case the backticks just add a layer of confusion.

However, this also looks like it was at least in part created by a tool
(PHPMyAdmin?), in which case I understand all the extra stuff.

First of all, I'm assuming you're going to want the order id to be auto
increment also, so you don't have to generate a new id each time though.

What do you want to index on? Customer_ID? If so, you should have:

INDEX ID (Customer_ID)

On your CONSTRAINT, you can specify an index, but I prefer not to.
MySQL will still use an index if it can, but it can choose whether or
not to use the index. Here you are forcing it to use an index which may
be suboptimal (it isn't in this case, but not necessarily a good habit
to get into).

On your CONSTRAINT you also need to specify the columns, i.e.

FOREIGN KEY (Customer_ID)
REFERENCES customer(ID)

Also, while you can use NO ACTION, I prefer RESTRICT for two reasons:
both will cause the DELETE or UPDATE to fail if it causes a foreign key
constraint violation. However, NO ACTION is a delayed check while
RESTRICT is an immediate check. There are advantages and disadvantages
to both (which I won't get into here as it's not pertinent), but
generally I prefer RESTRICT).

In MySQL, both cause an immediate check (no delayed check), so there's
no difference, except that I think RESTRICT is easier to understand and
closer to what happens.

So my final version would look something like:

USE TEST;

CREATE TABLE IF NOT EXISTS customer (
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
Cust_Name VARCHAR(32) NOT NULL,
PRIMARY KEY (ID)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS orders (
Order_ID INT NOT NULL AUTO_INCREMENT,
Customer_ID INT NULL,
Order_Date DATE NULL,
Amount DOUBLE NULL,
PRIMARY KEY (Order_ID),
INDEX ID (Customer_ID),
CONSTRAINT
FOREIGN KEY (Customer_ID)
REFERENCES customer (ID)
ON DELETE RESTRICT
ON UPDATE RESTRICT
) ENGINE = InnoDB;




--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Most excellent post Jerry... Thank you very much..
You too luuk...
I was curious about the insert statement and it's syntax...
is there a notion of an insert crossing tables? or must each insert
be restricted to one table?

On Thu, 27 Jan 2011 12:01:05 -0800 (PST), SpreadTooThin wrote:
Some RDBMS allow multi-table inserts. MySQL currently doesn't support
it.

Reading up on the LAST_INSERT_ID() function and/or the corresponding
functions in your host language API will make exactly how to manage the
auto-increment stuff that Jerry talked about very clear and easy.

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting. [TOFU := text oben,
Q: What is the most annoying thing on usenet? followup unten]

On 1/27/2011 3:01 PM, SpreadTooThin wrote:
You will have two INSERT statements - one for the customer and one for
the order.

But that's how it should be anyway. While the tables have a
relationship, you may insert into one or the other - not necessarily both.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle:

[This is meant as some advice/thoughts for OP, but written as a
follow-up to Jerry's post, because it is related to his correct
comments:]

There's something else to consider in this type of situations, though:
"what makes a person a Customer?"

Can a customer really have no Orders? If so, why is he considered a
Customer? Why not his neighbor and why not someone else? You don't keep
track of everyone on this planet, right?

There may be a good reason to only keep data of people, who have placed
at least one Order. Privacy reasons, legal reasons, or perhaps plain
application reasons (you don't want to keep, maintain and backup data
unnecessarily).
In that case, you need to set up your application in such a way, that
you gather Customer and Order data first (at least one Order) and then
store them in your database in _one_ transaction. That way, you make
sure, that Customers always have at least one Order. Should the Order
be deleted, then the Customer needs to be deleted automatically in the
same transaction.
If you allow data entry for the Customer first, and then initiate a
next step for Order entry, the operator might simply not enter the
Order and you are left with a Customer without Orders.
Some organizations have a nightly batch run that deletes such orphans,
but they would have Customers-without-Orders at least temporarily,
which is not desirable.

However, if a Customer is defined as a person who doesn't need to have
at least one order (he merely inquired about an article, or he placed
an order which got deleted later) you don't need transactions. It
wouldn't be a disaster if someone entered Customer data first, then
quit his job and never entered the Order that should be entered in the
next step. The data would still be valid.

I am not pleading in favor of either definition, but I do suggest you
consider both and make the proper choice for your situation.

=====

Oh, and another point, which _always_ comes up when designing such
application and the processes of the organization:
- how are you going to keep the Customer data up to date? Do you get
notified if a Customer moves to another address, or if he dies? If not,
won't you have useless data after several years?
One way to solve this, is sending all Customers a mail with their
stored data, asking them to confirm or correct them.
- how are you going to make sure that Customers aren't entered multiple
times? Is Peter Mueller, on Cross Road 34 in Kingston the same person
as Mr.P.Müller at the same address? Your application needs some type of
logic that attempts to support the operator in identifying duplicates
upon data entry, and/or deleting duplicates afterwards.


--
Erick

El 27/01/2011 21:01, SpreadTooThin escribió/wrote:
As everything else, the syntax for INSERT is documented:

http://dev.mysql.com/doc/refman/5.1/en/insert.html

As you can see in the above link, only one table is mentioned.

Whatever, other DBMS have this feature and the query can get complex to
get right. Before caring about this kind of stuff you should get
familiar with basic SQL syntax and also get used to the reference manual:

http://dev.mysql.com/doc/refman/5.1/en/



--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--