ErrorMsg in loading sample database from O'Reilly Media's "Learning SQL"

Learning SQL, Second Edition

By Alan Beaulieu
Publisher: O'Reilly Media
Released: April 2009
ISBN: 9780596520830

The above book has code to create a MySQL database, which they've posted here: http://examples.oreilly.com/9780596520847/LearningSQLExample.sql

When I run the code, everything "appears" to be fine, but only after I remove these lines of code:

/* recreate employee self-referencing foreign key */
alter table employee add constraint fk_e_emp_id
foreign key (superior_emp_id) references employee (emp_id);

I believe it's clear to me what the this code is doing, and that it was executed in the create table statements -- and appears to never been undone (so it's unclear to me why the code causing the error is nee开发者_StackOverflowded); might be wrong about that.

Here's the error message I get for those lines of code, which is the same regardless if it's execute within the code linked to above, or in the case below, run after removing it from the supplied code, running the supplied code (which creates the database), and then running it on it's own on the created database:

Enter password: **********************************************************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.53-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use bank
Database changed
mysql> alter table employee add constraint fk_e_emp_id
    -> foreign key (superior_emp_id) references employee (emp_id);
ERROR 1005 (HY000): Can't create table 'bank.#sql-80c_11' (errno: 121)
mysql>exit

UPDATE:

Just in case you're wondering, since I find the code a little strange, the book's instructions for using the code linked to are basically:

create database bank;
use bank
source C:\LearningSQLExample.sql;

I see no reason for that statement being there.

They are using a temp table to ensure that only valid FKs get assigned to the superior_emp_id column, so I can't imagine needing to drop the constraint and then re-add it. My guess is they originally had code to do just that because of the way there were populating the employees table, but then rearranged the code to where it is now. Eh, even that explanation might not make sense.

In any case, sloppy of them to not at least do a simple run through of the sample code before publishing.

Remove the unsigned deceleration in employee table for superior_emp_id and emp_id and try again.

In the table creation for employee:

create table employee
(emp_id smallint unsigned not null auto_increment,
fname varchar(20) not null,
lname varchar(20) not null,
start_date date not null,
end_date date,
superior_emp_id smallint unsigned,
dept_id smallint unsigned,
title varchar(20),
assigned_branch_id smallint unsigned,
constraint fk_e_emp_id 
foreign key (superior_emp_id) references employee (emp_id),
constraint fk_dept_id
foreign key (dept_id) references department (dept_id),
constraint fk_e_branch_id
foreign key (assigned_branch_id) references branch (branch_id),
constraint pk_employee primary key (emp_id)
);

empid is marked as unsigned: emp_id smallint unsigned not null auto_increment Try removing this unsigned.

It is June 2016 and i am using MySQL Community Server 5.7.13 on Win7 PC

First, Add SET SQL_SAFE_UPDATES = 0; as shown below

/* create data for self-referencing foreign key 'superior_emp_id' */

SET SQL_SAFE_UPDATES = 0;

create temporary table

emp_tmp as select emp_id, fname, lname from employee;

etc

Second, Remove unsigned from any place it appears in the code.

The code works like a charm with these mods.