Tony
29 de Noviembre del 2005

¿COmo puedo declarar una clave foranea de forma que chequee las restricciones antes de hacer una inserción? He leido que debo declarar las tablas de tio INNODB, pero creo una tabla con una clave foranea y cuando inserto un dato el la tabla con el campo que corresponde a la clave foranea con un valor inexistente y aun asi lo inserta.

create table teams (team_code int unsigned primary key auto_increment,
team_name char(100)
) type = INNODB;


create table vendors (vendor_code int unsigned primary key auto_increment,
vendor_name char(100)
) type = INNODB;


create table phones (phone_code int unsigned primary key auto_increment,
phone_name char(100),
vendor int unsigned,
index(vendor),
constraint foreign_phones foreign key (vendor) references vendors(vendor_code) on delete cascade
) type = INNODB;
create table teams (team_code int unsigned primary key auto_increment,
team_name char(100)
) type = INNODB;


create table vendors (vendor_code int unsigned primary key auto_increment,
vendor_name char(100)
) type = INNODB;


create table phones (phone_code int unsigned primary key auto_increment,
phone_name char(100),
vendor int unsigned,
index(vendor),
constraint foreign_phones foreign key (vendor) references vendors(vendor_code) on delete cascade
) type = INNODB;
create table teams (team_code int unsigned primary key auto_increment,
team_name char(100)
) type = INNODB;


create table vendors (vendor_code int unsigned primary key auto_increment,
vendor_name char(100)
) type = INNODB;


create table phones (phone_code int unsigned primary key auto_increment,
phone_name char(100),
vendor int unsigned,
index(vendor),
constraint foreign_phones foreign key (vendor) references vendors(vendor_code) on delete cascade
) type = INNODB;create table teams (team_code int unsigned primary key auto_increment,
team_name char(100)
) type = INNODB;


create table vendors (vendor_code int unsigned primary key auto_increment,
vendor_name char(100)
) type = INNODB;


create table phones (phone_code int unsigned primary key auto_increment,
phone_name char(100),
vendor int unsigned,
index(vendor),
constraint foreign_phones foreign key (vendor) references vendors(vendor_code) on delete cascade
) type = INNODB;

insert into phones (phone_name, vendor) values('phone', 10);

Query OK, 1 row affected (0.02 sec)

dannny
29 de Noviembre del 2005

A partir de la versión 3.23.50, se pueden agregar restricciones de clave foránea a una tabla con el uso de la sentencia ALTER TABLE. La sintaxis es:
ALTER TABLE nombre_tabla ADD [CONSTRAINT símbolo] FOREIGN KEY (...)
REFERENCES otra_tabla (...) [acciones_ON_DELETE][acciones_ON_UPDATE]

Un ejemplo seria
alter table tabla1
add constraint campo1
foreign key (campo1)
references tabla2(campo1)
on delete cascade
on update cascade;


espero que te ayude