>Dear Sir
>I am new to MySQL. I've created a table with three keys inside like this:
>
>CREATE TABLE reservation
>(
> reservation_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
> hotel_id INT UNSIGNED NOT NULL,
> customer_id INT UNSIGNED NOT NULL,
> PRIMARY KEY(reservation_id, hotel_id, customer_id),
> ............................
>
>and I tried to insert the data by using the following sql statements:
>
>INSERT INTO reservation (reservation_id, hotel_id,
>customer_id,business_trip, checkin_month,
>checkin_day, checkin_year, checkin_time, checkout_month, checkout_day,
>checkout_year,
>total_num_guest, room_type, hotel_recommendation, customer_request,
>customer_comments) VALUES
>(NULL, NULL, NULL, 'yes_business', 'January', '01', '2002', '11:00',
>'January', '07', '2002', '3',
>'Triple', 'no_recommendations', 'Morning call', 'No')
>
>The book says that I should put NULL in order to let AUTO_INCREMENT
>generate the id for me.
>However, it kept telling me that hotel_id can't be null (I guess it
>was because the hotel_id has been
>generated in the "hotel" table by using AUTO_INCREMENT as well). How
>am I supposed to
>connect different primary keys into one table (I mean how can I
>transfer the hotel_id in Hotel table to
>the Reservation table)? And after that how can I insert those
>primary keys inside one table?
>
>Thank you very much for your time.
>
>Sincerely,
>Cheri Peng
Ma'am, you don't have three keys. You have declared a PRIMARY KEY
with three columns. However, only the first column is a key. The
auto_increment column is all that is necessary to uniquely identify
each row. The other two columns appear to be foreign keys and should
be removed from your PRIMARY KEY clause.
You are correct about the NULLs. Since you've declared each of the
columns in your primary key as NOT NULL, you must insert a non-NULL
value in each of the three columns. Only the first column is
auto-increment, so your INSERT statement must have a value for the
other two.
Instead of a VALUES list, use a SELECT statement in your INSERT
statement that returns the necessary values for the two fields. The
field list in the SELECT clause of the SELECT statement would be
identical to the VALUES list in your present statement, except that
the second and third NULLs would be replaced by the names of the
columns you are extracting the hotel_id and customer_id values from.
Your constant values would remain unchanged. Also, remove the first
NULL from the VAlUES list and the reservation_id column from the
INSERT field list.
INSERT INTO reservation (hotel_id,
customer_id,business_trip, checkin_month,
checkin_day, checkin_year, checkin_time, checkout_month, checkout_day,
checkout_year,
total_num_guest, room_type, hotel_recommendation, customer_request,
customer_comments)
SELECT hotel_id, custumer_id, 'yes_business', 'January', '01', '2002', '11:00',
'January', '07', '2002', '3',
'Triple', 'no_recommendations', 'Morning call', 'No'
<whatever FROM and WHERE clauses are necessary to return appropriate
values for hotel_id and customer_id>;
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
