Evitar registro duplicado en tabla de BD relacional

[manke10]

Hola amigos!

Tengo una duda, mejor dicho no sé como encarar este problema:
Tengo una base de datos con tres tablas:
Digamos que los nombres son: Especie Nombres y Nombre_y_especie

Especie y Nombres tienen un sólo campo (nombre) que es a su vez primaryKey.

Nombre_y_especie tiene dos campos(nombre y especie) que son tipo index. Solo pueden contener valores que estén definidos en las tablas Especie y Nombres.

Aqui viene mi problema / duda:

la tabla Nombre_y_especie puede tener por ejemplo:
ave | gallina
ave | aguila
animal | gallina
animal | aguila
animal | aguila
animal | aguila
ó sea, si repito un registro (como animal | aguila) me lo acepta y no sé cómo hacer para evitarlos.

Alguien me puede tirar un hilo para ver cómo solucionar este problema?

Gracias!

[leonardo_josue]

Hola marke10:

Hay varias formas de abordar este problema. No nos dices cómo es la estructura completa de tus tablas, por lo que no puedo darte una respuesta puntual. Una idea sería que tu llave en la tabla Nombres_y_especies fuera una llave compuesta, justamente por los campos nombre y especie, No importa si estos dos campos son a su vez FK sobre otras tablas, Checa este script:

Código MySQL:

mysql> CREATE TABLE nombres(nombre VARCHAR(15) NOT NULL, PRIMARY KEY (nombre));
Query OK, 0 rows affected (0.11 sec)
 
mysql> INSERT INTO nombres VALUES ('ave'), ('animal');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> CREATE TABLE especies(especie VARCHAR(15) NOT NULL, PRIMARY KEY (especie));
Query OK, 0 rows affected (0.13 sec)
 
mysql> INSERT INTO especies VALUES ('gallina'), ('aguila');
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> CREATE TABLE nombres_y_especies (nombre VARCHAR(15) NOT NULL,
    -> especie VARCHAR(15) NOT NULL, PRIMARY KEY (nombre, especie),
    -> FOREIGN KEY (nombre)
    ->     REFERENCES nombres(nombre)
    ->     ON DELETE CASCADE,
    ->  FOREIGN KEY (especie)
    ->     REFERENCES especies(especie)
    ->     ON DELETE CASCADE);
Query OK, 0 rows affected (0.19 sec)
 
mysql> INSERT INTO nombres_y_especies VALUES ('ave', 'aguila'), 
    ->('animal', 'gallina');
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM nombres_y_especies;
+--------+---------+
| nombre | especie |
+--------+---------+
| ave    | aguila  |
| animal | gallina |
+--------+---------+
2 rows in set (0.00 sec)
 
mysql> #intentamos insertar un registro existente
mysql> INSERT INTO nombres_y_especies VALUES ('animal', 'gallina');
ERROR 1062 (23000): Duplicate entry 'animal-gallina' for key 'PRIMARY'
 
mysql> #intentamos insertar un valor que no exista en la tabla nombres
mysql> INSERT INTO nombres_y_especies VALUES ('otro', 'gallina');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`pruebas`.`nombres_y_especies`, CONSTRAINT `nombres_y_especies_ibfk_1` 
FOREIGN KEY (`nombre`) REFERENCES `nombres` (`nombre`) ON DELETE CASCADE)
 
mysql> #intentamos insertar un valor que no exista en la tabla especies
mysql> INSERT INTO nombres_y_especies VALUES ('animal', 'otro');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint 
fails (`pruebas`.`nombres_y_especies`, CONSTRAINT `nombres_y_especies_ibfk_2` FOREIGN KEY 
(`especie`) REFERENCES `especies` (`especie`) ON DELETE CASCADE)
 

Observa que los criterios de integridad de los datos se están respetando, primero evitando duplicados:

Código:

Duplicate entry 'animal-gallina' for key 'PRIMARY'

y los respectivos errores porque los valores no existen en las tablas foráneas:

Código:

FOREIGN KEY (`nombre`) REFERENCES `nombres` (`nombre`)

FOREIGN KEY  (`especie`) REFERENCES `especies` (`especie`)

Dale un vistazo para ver si te sirve.

Saludos.
Leo.

[manke10]

Uhhhh !!! Leo!
Muchas gracias por tu ayuda, realmente has sido tan claro que no me dajaste lugar a consultarte nada jaja!

Ya pude resolver mi problema, de acuerdo a tu ejemplo, yo estaba fallando en asignar primary key a ambos campos en la tercera tabla, ademas de asignar references a los PK.

De nuevo, muchas gracias Leonardo. Abrazos.