MySQL 2 Fragen zur Syntax von SQL-Abfragen

[Enigma228]

Hi Leute!!

hab mal wieder Fragen und zwar vorerst nur zwei.. grins

Warum kann er die Tabelle tbl_telefon nicht erstellen??? Ist vermutlich ein Flüchtigkeitsfehler.. aber ich finde ihn nicht!!
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 'usage
varchar(15),
type varchar(10),
description varchar(255),
PRIMARY KEY(id' at line 6

.. und warum auch nicht die Verbindungsttabellen??
ERROR 1005 (HY000): Can't create table 'bibliothek.tbl_person_tel' (errno: 150)
ERROR 1005 (HY000): Can't create table 'bibliothek.tbl_publisher_person' (errno:
150)
ERROR 1005 (HY000): Can't create table 'bibliothek.tbl_actor_person' (errno: 150
)
ERROR 1005 (HY000): Can't create table 'bibliothek.tbl_director_person' (errno:
150)

laut Errorcode soll der Fremdschlüssel falsch sein. Habe ihn aber genauso wie im Handbuch beschrieben gesetzt.
MySQL :: MySQL 5.0 Reference Manual :: 13.2.4.4 FOREIGN KEY Constraints

Achja für Verbesserungsvorschläge an der DB bin ich immer offen (z.B. betreffend der Wiederholungen in den Tabellen für Bücher, CDs und Filme)

Das ist meine DB (noch nicht vollständig da Tabellen bisher nur auf tbl_movie bezogen):

[sql]
create table tbl_movie(
id int unsigned not null auto_increment,
title varchar(30),
description blob,
publication year(4),
cover varchar(255),
genre_id int not null default 1,
language_id int not null default 1,
subtitle_id int,
publisher_id int,
actor_id int,
director_id int,
borrow boolean,
location_id int,
PRIMARY KEY(id)
)engine=innodb;

create table tbl_book(
id int unsigned not null auto_increment,
title varchar(30),
description blob,
publication year(4),
cover varchar(255),
genre_id int not null default 1,
language_id int not null default 1,
publisher_id int,
author_id int,
isbn10 varchar(10),
isbn13 varchar(13),
borrow boolean,
location_id int,
PRIMARY KEY(id)
)engine=innodb;


create table tbl_cd(
id int unsigned not null auto_increment,
title varchar(30),
description blob,
publication year(4),
cover varchar(255),
genre_id int not null default 1,
language_id int not null default 1,
publisher_id int,
interpret_id int,
borrow boolean,
location_id int,
PRIMARY KEY(id)
)engine=innodb;



create table tbl_person(
id int unsigned not null auto_increment,
given_name varchar(30),
surname varchar(30),
street varchar(50),
city varchar(50),
country varchar(50),
telefon_id int,
email varchar(50),
homepage varchar(50),
PRIMARY KEY(id)
)engine=innodb;

create table tbl_telefon(
id int unsigned not null auto_increment,
int_area_code varchar(5),
prefix varchar(10),
callnumber varchar(15),
usage varchar(15),
type varchar(10),
description varchar(255),
PRIMARY KEY(id)
)engine=innodb;

create table tbl_language(
id int unsigned not null auto_increment,
language varchar(30),
PRIMARY KEY(id)
)engine=innodb;

create table tbl_genre(
id int unsigned not null auto_increment,
genre varchar(15),
PRIMARY KEY(id)
)engine=innodb;

#m:n Beziehungen#

create table tbl_person_tel(
person_id int not null,
tel_id int not null,
INDEX(person_id),
FOREIGN KEY(person_id) REFERENCES tbl_person(id),
INDEX(tel_id),
FOREIGN KEY(tel_id) REFERENCES tbl_telefon(id)
)engine=innodb;


create table tbl_publisher_person(
publisher_id int not null,
person_id int not null,
INDEX(publisher_id),
FOREIGN KEY(publisher_id) REFERENCES tbl_movie(publisher_id),
INDEX(person_id),
FOREIGN KEY(person_id) REFERENCES tbl_person(id)
)engine=innodb;

create table tbl_actor_person(
actor_id int not null,
person_id int not null,
INDEX(actor_id),
FOREIGN KEY(actor_id) REFERENCES tbl_movie(actor_id),
INDEX(person_id),
FOREIGN KEY(person_id) REFERENCES tbl_person(id)
)engine=innodb;

create table tbl_director_person(
director_id int not null,
person_id int not null,
INDEX(director_id),
FOREIGN KEY(director_id) REFERENCES tbl_movie(director_id),
INDEX(person_id),
FOREIGN KEY(person_id) REFERENCES tbl_person(id)
)engine=innodb;
[/sql]

 

[eRaaaa]

Ist "usage" vllt ein Schlüsselwort o.ä.? (EDIT: -> MySQL :: MySQL 5.1 Referenzhandbuch :: 9.5 Ist MySQL pingelig hinsichtlich reservierter Wörter? )
Versuche mal
[sql]
create table tbl_telefon(
id int unsigned not null auto_increment,
int_area_code varchar(5),
prefix varchar(10),
callnumber varchar(15),
`usage` varchar(15),
type varchar(10),
description varchar(255),
PRIMARY KEY(id)
)engine=innodb;
[/sql]

 

[Enigma228]

ok das scheints zu sein.. ich hatte auch using und use an der Stelle getestet, welche anscheinend auch reservierte Wörter zu sein scheinen..

so hast du vielleicht auch eine Idee bezüglich des 2. Problems?

 

[eRaaaa]

so hast du vielleicht auch eine Idee bezüglich des 2. Problems?

Idee schon, wirklich Ahnung nicht(deshalb habe ich dazu auch nichts weiter geschrieben)

Die Keys die du da referenzierst müssen soweit ich weiß

unsigned

sein, d.h. für die erste Tabelle

tbl_person_tel

müsste es so heißen:

create table tbl_person_tel(
	person_id int unsigned  not null,
	tel_id int unsigned not null,
	INDEX(person_id),
	FOREIGN KEY(person_id) REFERENCES tbl_person(id),
	INDEX(tel_id),
	FOREIGN KEY(tel_id) REFERENCES tbl_telefon(id)
)engine=innodb

Dann fällt mir noch auf dass du da versuchst Spalten zu referenzieren die gar kein Schlüssel sind?!
(z.B. REFERENCES tbl_movie(publisher_id))

Aber es wird sich sicher noch ein Datenbank-Profi dazu äußern

 

[Enigma228]

bei tbl_person_tel hat das mit dem unsigned funktioniert..
und stimmt da sind auch beide Werte Primärschlüssel..

hab jetzt mal die Tabellen umfunktioniert und publisher_id ebenfalls als Primärschlüssel gestetzt.. aber keinen Erfolg
hier die geänderten Tabellen:
[sql]
create table tbl_movie(
id int unsigned not null auto_increment,
title varchar(30),
description blob,
publication year(4),
cover varchar(255),
genre_id int unsigned not null default 1,
language_id int unsigned not null default 1,
subtitle_id int unsigned,
publisher_id int unsigned not null, // auch auf unsigned gesetzt
actor_id int unsigned,
director_id int unsigned,
borrow boolean,
location_id int,
PRIMARY KEY(id, publisher_id) // hier als Primärschlüssel hinzugefügt
)engine=innodb;

create table tbl_person(
id int unsigned not null auto_increment,
given_name varchar(30),
surname varchar(30),
street varchar(50),
city varchar(50),
country varchar(50),
telefon_id int,
email varchar(50),
homepage varchar(50),
PRIMARY KEY(id)
)engine=innodb;

create table tbl_publisher_person(
publisher_id int unsigned not null, // hier dem Wert in der tbl_movie angepasst
person_id int unsigned not null,
INDEX(publisher_id),
FOREIGN KEY(publisher_id) REFERENCES tbl_movie(publisher_id),
INDEX(person_id),
FOREIGN KEY(person_id) REFERENCES tbl_person(id)
)engine=innodb;

[/sql]

 

[SlaterB]

ein zusammengesetzer Primärschlüssel und dann auf einzelne Teile davon referenzieren, na das ist nun ganz ausgeschlossen,
bleibe doch erstmal bei nur einem Primärschlüssel id,
publisher_id ganz außer acht lassen,

kannst du auf id als Fremdschlüssel referenzieren?
vorteilhaft wären erstmal auch kleine Testtabellen mit nur 1-2 Attributen, nur einem Frendschlüssel usw.


-------

weitere Spalten wie publisher_id, die nicht Primärschlüssel sind, aber von anderen Tabellen als Fremdschlüssel referenziert werden,
sollten vielleicht NON NULL UNIQUE sein,
gibts da wirklich kein Lehrbuch zu?

 

[Enigma228]

aber das ist ja nicht das Ziel..

so funktioniert es, aber ich will ja auf den publisher referenzieren und nicht auf die ID:

[Java]
create table tbl_movie(
id int unsigned not null auto_increment,
title varchar(30),
description blob,
publication year(4),
cover varchar(255),
genre_id int unsigned not null default 1,
language_id int unsigned not null default 1,
subtitle_id int unsigned,
publisher_id int unsigned not null,
actor_id int unsigned,
director_id int unsigned,
borrow boolean,
location_id int,
PRIMARY KEY(id)
)engine=innodb;

create table tbl_person(
id int unsigned not null auto_increment,
given_name varchar(30),
surname varchar(30),
street varchar(50),
city varchar(50),
country varchar(50),
telefon_id int,
email varchar(50),
homepage varchar(50),
PRIMARY KEY(id)
)engine=innodb;

create table tbl_publisher_person(
publisher_id int unsigned not null,
person_id int unsigned not null,
INDEX(publisher_id),
FOREIGN KEY(publisher_id) REFERENCES tbl_movie(id),
INDEX(person_id),
FOREIGN KEY(person_id) REFERENCES tbl_person(id)
)engine=innodb;
[/code]

 

[Enigma228]

Ich prüfe die unique Idee und melde mich dann..

 

[Enigma228]

Mache zwar verbindungstabellen aber alle auf den Primärschlüssel von tbl_movie.
Alle anderen Id Punkte habe ich aus der Tabelle rausgenommen..

Auszug aus DB:

create table tbl_movie(
	id int unsigned not null auto_increment,
	title varchar(30),
	description blob,
	publication year(4),
	cover varchar(255),
	borrowed boolean,
	PRIMARY KEY(id)
)engine=innodb;

create table tbl_person(
	id int unsigned not null auto_increment,
	given_name varchar(30),
	surname varchar(30),
	street varchar(50),
	city varchar(50),
	country varchar(50),
	telefon_id int,
	email varchar(50),
	homepage varchar(50),
	PRIMARY KEY(id)
)engine=innodb;

create table tbl_publisher_person(
	publisher_id int unsigned not null,
	person_id int unsigned not null,
	INDEX(publisher_id),
	FOREIGN KEY(publisher_id) REFERENCES tbl_movie(id),
	INDEX(person_id),
	FOREIGN KEY(person_id) REFERENCES tbl_person(id)
)engine=innodb;