I can not create a foreign key in MariaDB

Question

I can not create a foreign key in MariaDB

Navigation

#1 by (0 votes)

0

This is what I write:

alter table T_reserva add constraint pk_codHotel foreign key (codHotel) references T_hotel(codHotel);

This is the T_hotel.codHotel table:

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| tipo           | varchar(255) | NO   | PRI | NULL    |       |
| numMaxPersonas | int(11)      | YES  |     | NULL    |       |
| disponibilidad | int(11)      | YES  |     | NULL    |       |
| codHotel       | varchar(255) | NO   | PRI | NULL    |       |
| precio         | double       | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

In this table you have the primary key T_reserva.codHotel that I want to reference to the table T_hotel.codHotel :

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| fechaLlegada  | varchar(255) | YES  |     | NULL    |       |
| numNoches     | int(11)      | YES  |     | NULL    |       |
| numAdultos    | int(11)      | YES  |     | NULL    |       |
| numMenDos     | int(11)      | YES  |     | NULL    |       |
| numMenDosDoce | int(11)      | YES  |     | NULL    |       | 
| localizador   | varchar(255) | NO   | PRI | NULL    |       |
| observaciones | varchar(255) | YES  |     | NULL    |       |
| precio        | double       | YES  |     | NULL    |       |
| regimen       | varchar(255) | YES  |     | NULL    |       | 
| codCliente    | varchar(255) | YES  | MUL | NULL    |       |
| codHotel      | varchar(255) | YES  |     | NULL    |       |
| tipoHab       | varchar(255) | YES  |     | NULL    |       |
  +---------------+--------------+------+-----+---------+-------+

When I want to do the foreign key pulls me:

ERROR 1005 (HY000): Can't create table reserves . # sql-4e18_22 (errno: 150 "Foreign key constraint is incorrectly formed")

Here are the scripts the each table:

T_reservation:

   'T_reserva | CREATE TABLE 'T_reserva' (
  'fechaLlegada' varchar(255) DEFAULT NULL,
  'numNoches' int(11) DEFAULT NULL,
  'numAdultos' int(11) DEFAULT NULL,
  'numMenDos' int(11) DEFAULT NULL,
  'numMenDosDoce' int(11) DEFAULT NULL,
  'localizador' varchar(255) NOT NULL,
  'observaciones' varchar(255) DEFAULT NULL,
  'precio' double DEFAULT NULL,
  'regimen' varchar(255) DEFAULT NULL,
  'codCliente' varchar(255) DEFAULT NULL,
  'codHotel' varchar(255) DEFAULT NULL,
  'tipoHab' varchar(255) DEFAULT NULL,
  PRIMARY KEY ('localizador'),
  KEY 'codCliente' ('codCliente'),
  CONSTRAINT 'T_reserva_ibfk_1' FOREIGN KEY ('codCliente') REFERENCES 'T_cliente' ('codCliente')
) ENGINE=InnoDB DEFAULT CHARSET=latin1

T_hotel:

  'T_hotel | CREATE TABLE 'T_hotel' (
  'tipo' varchar(255) NOT NULL,
  'numMaxPersonas' int(11) DEFAULT NULL,
  'disponibilidad' int(11) DEFAULT NULL,
  'codHotel' varchar(255) NOT NULL,
  'precio' double DEFAULT NULL,
  PRIMARY KEY ('tipo','codHotel')
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql sql linux mariadb backend

asked by TOMAS 19.11.2017 в 03:24

source

1 answer

how to insert a dataset into a gridview How do I duplicate Panels or create new Panels?

score 0 · Accepted Answer

Solved: The column to reference was part of a foreign key composed by that did not have the index with ALTER TABLE T_hotel add index(codHotel) ; solve it.

Here I found the solution

On the page it says:

One of the fields in the relationship is part of a combination (composite) key and does not have it’s own individual index. Even though the field has an index as part of the composite key, you must create a separate index for only that key field in order to use it in a constraint. (Thanks to Alex for this tip)

The SHOW CREATE DATABASE reservas; command specifies the error.