1

I have written the following statement:

CREATE TABLE ORDEN (
    `numero` INT UNSIGNED ZEROFILL NOT NULL,
    `fecha` DATE NOT NULL,
    `cliente` INT(6) UNSIGNED NOT NULL,
    `nombre_cliente` VARCHAR(64) NOT NULL,
    `direccion_cliente` VARCHAR(64) NOT NULL,
    `email_cliente` VARCHAR(64),

    /* Se utiliza ON DELETE NO ACTION porque probablemente (al igual que cualquier otro documento del mismo tipo) las órdenes se quieran conservar */
    FOREIGN KEY `fk_ORDEN_CLIENTE_ID`(`cliente`) REFERENCES CLIENTE(`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY `fk_ORDEN_CLIENTE_NOMBRE`(`nombre_cliente`) REFERENCES CLIENTE(`nombre`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY `fk_ORDEN_CLIENTE_DIRECCION`(`direccion_cliente`) REFERENCES CLIENTE(`direccion`) ON DELETE NO ACTION ON UPDATE CASCADE,
    FOREIGN KEY `fk_ORDEN_CLIENTE_EMAIL`(`email_cliente`) REFERENCES CLIENTE(`email`) ON DELETE NO ACTION ON UPDATE CASCADE,
    PRIMARY KEY(`numero`)
);

The problem is that I'm getting this error when trying to execute the statement:

Error Code: 1215. Cannot add foreign key constraint

I've been thinking that maybe referencing many columns from a single table with foreign keys in one statement is not allowed.

Here is the definition of the CLIENTE table as well:

CREATE TABLE CLIENTE (
    `id` INT(6) UNSIGNED ZEROFILL NOT NULL,
    `email` VARCHAR(64), /* Dirección electrónica */
    `nombre` VARCHAR(64) NOT NULL,
    `direccion` VARCHAR(64) NOT NULL,

    PRIMARY KEY(`id`),
    UNIQUE(`direccion`)
);

How can I resolve my issue? I've been searching for a solution but had no success.

1

Foreign Keys can be added in the Child Table with the reference to Unique or Primary keys of Parent Table. The columns nombre and email are NOT defined as Primary or Unique key in Cliente Table. Hence you are not able to add FKs to these columns in the table ORDEN.

  • Oh, I didn't knew that (thanks!). Is there any way to reference a column that is not primary key or unique in another table? According to the requirements these attributes ARE optional and they have to be in the ORDEN table. – Ariel Jan 20 '17 at 14:04
  • @Ariel the question (for you) is why you want to have these columns in the orden table (and not just the client_id). You surely can find the values through joining to the client table. Thats one of the reasons why we have FKs, so we don't repeat data here and there. – ypercubeᵀᴹ Jan 20 '17 at 14:29
  • @ypercubeᵀᴹ Because of the requirements. ORDEN table represents some kind of voucher (proof of request), which means it has to contain those details somehow. – Ariel Jan 20 '17 at 14:41
  • So, the details can change over time (updated in client table) but the values durign the request should be saved (in the orden table)? – ypercubeᵀᴹ Jan 20 '17 at 16:12
  • @ypercubeᵀᴹ Yes! That's exactly how it should be. – Ariel Jan 22 '17 at 22:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.