MYSQL Help Needed

[eMonk]

ERROR 1062 (23000) at line 3: Duplicate entry '3' for key 1

I get this error when trying to run the following mysql command:

mysql -h xxx -u xxx -p books < /path/to/book_insert.sql

book_insert.sql

use books;

insert into customers values
  (3, 'Julie Smith', '25 Oak Street', 'Airport West'),
  (4, 'Alan Wong', '1/47 Haines Avenue', 'Box Hill'),
  (5, 'Michelle Arthur', '357 North Road', 'Yarraville');
  
insert into orders values
  (NULL, 3, 69.98, '2007-04-02'),
  (NULL, 1, 49.99, '2007-04-15'),
  (NULL, 2, 74.98, '2007-04-19'),
  (NULL, 3, 24.99, '2007-05-01');
  
insert into books values
  ('0-672-31697-8', 'Michael Morgan', 'Java 2 for Professional Developers', 34.99),
  ('0-672-31745-1', 'Thomas Down', 'Installing Debian GNU/Linux', 24.99),
  ('0-672-31509-2', 'Pruitt, et al.', 'Teach Yourself GIMP in 24 Hours', 24.99),
  ('0-672-31769-9', 'Thomas Schenk', 'Caldera OpenLinux System Administration Unleashed', 49.99);
  
insert into order_items values
  (1, '0-672-31697-8', 2),
  (2, '0-672-31769-9', 1),
  (3, '0-672-31769-9', 1),
  (4, '0-672-31509-2', 1),
  (5, '0-672-31745-1', 3);
  
insert into book_reviews values
  ('0-672-31697-8', 'The Morgan book is clearly written and goes well beyond most of the basic Java books out there.');

bookorama.sql

create table customers
( customerid int unsigned not null auto_increment primary key,
  name char(50) not null,
  address char(100) not null,
  city char(30) not null
);

create table orders
( orderid int unsigned not null auto_increment primary key,
  customerid int unsigned not null,
  amount float(6,2),
  date date not null
);

create table books
( isbn char(13) not null primary key,
  author char(50),
  title char(100),
  price float(4,2)
);

create table order_items
( orderid int unsigned not null,
  isbn char(13) not null,
  quantity tinyint unsigned,
  primary key (orderid, isbn)
);

create table book_reviews
( isbn char(13) not null primary key,
  review text
);

any ideas? i'm new to mysql and this is my first experiment.

[gornyhuy]

If you have a unique index defined on one of your fields and then you try to insert duplicate values into that table the item with a unique key will cause it to fail.

Looks like you have isbn defined as a primary key on your order_items table, which means you will never be able to insert the same book twice.


EDIT: on closer inspection - the key is actually defined on the combination of order id and isbn, so that should be okay actually. You probably have already entered some of this data before and are now tyring to enter duped data where it is uniquely keyed.

[eMonk]

thx for your response. there is no data in the books database. i was told elsewhere that it needs to auto increment but he didn't say where. this code is straight from a php & mysql book. first mysql lesson & i'm stuck lol.

[eMonk]

edit: there is data in these tables, werid. guess i will delete this database & try readding info.

[mikesouth]

first rule in developing a database ...when loading info for the first time delete * from table;

or drop the table and recreate it put it in your script as the first thing

[eMonk]

thx mikesouth.. i used TRUNCATE TABLE tablename; then performed the above command & now it works. during the 1st attempt my insert_book.sql file had some typos. in result duplicate data was detected.

[eMonk]

btw isn't there a way to delete ALL data from a database? deleting data in tables one by one is a bit tedious. good thing i didn't have too many.

[sarettah]

btw isn't there a way to delete ALL data from a database? deleting data in tables one by one is a bit tedious. good thing i didn't have too many.

You could just drop the entire database and then create it again at the top of your script.

drop database dbname;
create dbname;
use dbname;

create table....... etc etc etc