ho ricreato il db dall'inizio.
ho creato queste tre tabelle:
codice:
mysql> desc author;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| author_id | int(11) | NO | PRI | NULL | auto_increment |
| author_name | varchar(100) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc editor;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| editor_id | int(11) | NO | PRI | NULL | auto_increment |
| editor_name | varchar(50) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc position;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| position_id | int(11) | NO | PRI | NULL | auto_increment |
| position_name | varchar(20) | NO | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
poi ho provato a creare la tabella book:
codice:
mysql> create table book (
-> title varchar(100) not null,
-> author int not null,
-> editor int not null,
-> price decimal(7,3),
-> position int not null,
-> primary key book_key (book_id),
-> index author_key (author),
-> index editor_key (editor),
-> index position_key (position),
-> foreign key (author) references author(author_id) on delete no action on update no action,
-> foreign key (editor) references editor(editor_id) on delete no action on update no action,
-> foreign key (position) references position(position_id) on delete no action on update no action) //linea 13
-> TYPE=InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'position(position_id) on delete no action on update no action)
TYPE=InnoDB' at line 13
nn riesco a capire dove sbaglio.
le tabelle sono uguali come le foreign key.