hint1: nemoj da prenosis text polja kroz tabele, prenosi ID (4 bajta umesto "mnogo" bajtova, mnoooogo je brze, indexi su manji, zauzima manje mesta na disku u kesevima u ramu ..)
hint2: da li su ti tabele innodb?
hint3: kada postavljas pitanje zgodno je da odma stavis rezultat od "show create table imetabele\G" kako ne bi gubili vreme na "podpitanja"
priprema
Code:
drop table if exists t2;
drop table if exists t1;
create table t1 (id int auto_increment primary key not null, name char(10), index i_name(name)) engine=innodb;
create table t2 (id int auto_increment primary key not null, t1name char(10), index x(t1name),
foreign key (t1name) REFERENCES t1(name) ON UPDATE CASCADE ON DELETE CASCADE) engine=innodb;
insert into t1 (name) values ('pera'), ('zika'), ('laza');
i kako to na kraju izgleda:
Code:
mysql> insert into t2 (t1name) values ('pera');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 (t1name) values ('zika');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 (t1name) values ('laza');
Query OK, 1 row affected (0.02 sec)
mysql> insert into t2 (t1name) values ('laza');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 (t1name) values ('laza');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 (t1name) values ('mika');
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1name`)
REFERENCES `t1` (`name`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql>
kao sto vidis ne moze da se ubaci vrednost koja ne postoji u t1 - to je to sto si trazio ... problem je u tome sto da bi to radilo tabele moraju da budu InnoDB, myisam to ne podrzava