6 replies to this topic

[Alhazred]

I create 2 tables with this code

create table localita (
    nome varchar(255) PRIMARY KEY
);
INSERT INTO localita VALUES ('Roma');
INSERT INTO localita VALUES ('Milano');
INSERT INTO localita VALUES ('Venezia');

create table zone (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome varchar(255),
    localita varchar(255)
);
ALTER TABLE zone ADD CONSTRAINT FK_zone FOREIGN KEY (localita) REFERENCES localita(nome) ON UPDATE CASCADE;
INSERT INTO zone (nome,localita) VALUES ('Testaccio','Roma');
INSERT INTO zone (nome,localita) VALUES ('Trastevere','Roma');
INSERT INTO zone (nome,localita) VALUES ('EUR','Roma');
INSERT INTO zone (nome,localita) VALUES ('Fiera','Milano');

If I modify a value inside the table "localita" in example I change "Roma" to "Roma (RM)" with an update, nothing happens on the table "zone". The value changes into "localita" but not into "zone".
I can also change the "localita" field into "zone" with a value not present inside the table "localita" and no error occurs.

Why the foreign key is not working?

[sam_l]

ALTER TABLE zone ADD CONSTRAINT FK_zone FOREIGN KEY [B]zone[/B](localita) REFERENCES localita(nome) ON UPDATE CASCADE;

Try that.

[Alhazred]

Thanks, I forgot to declare innodb as engine, but I have another problem.
Why this foreign key gives me an error?

create table sottocategorie (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    categoria varchar(255),
    nome varchar(255),
    descrizione text,
    KEY (categoria,nome)
) ENGINE=InnoDB;

create table appartamenti (
    codice varchar(20) PRIMARY KEY,
    categoria varchar(255) NOT NULL,
    sottocategoria varchar(255) NOT NULL,
    localita varchar(255),
    zona varchar(255)
) ENGINE=InnoDB;

ALTER TABLE appartamenti ADD CONSTRAINT FK_app_sub FOREIGN KEY (sottocategoria) REFERENCES sottocategorie(nome) ON UPDATE CASCADE;

I've already tried to use
... FOREIGN KEY appartamenti(sottocategoria) ...
but the problem is still there.

[sam_l]

What is the error?

[Alhazred]

It is the generic errno:105 it doesen't explain the problem.

[wim DC]

Can't you make 'id' of 'sottocategorie' the primary key? Makes things much easier ^^
Every table I create always has an ID as sole primary key.
If required you can still put a unique constraint on nome + categoria.

[LuthfiHakim]

Did you put index on sottocategorie(nome)? Remember foreign key also requires index on the referenced fields.