0

I'm having this table:

Table: example

DROP TABLE IF EXISTS example;
CREATE TABLE IF NOT EXISTS example(
    example_id      BIGINT      NOT NULL AUTO_INCREMENT,
    category_id     BIGINT,
    user_id         BIGINT      NOT NULL,
    customer_nr     TEXT        NOT NULL,
    global          BOOLEAN     NOT NULL DEFAULT 0,
    createtime      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(example_id),
    CONSTRAINT key_example_uniq         UNIQUE (user_id,custumer_nr),
    CONSTRAINT example_fk_category_id     FOREIGN KEY (category_id)     REFERENCES doktyp(doktyp_id),
    CONSTRAINT example_fk_user_id       FOREIGN KEY (user_id)       REFERENCES user(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Table: user

CREATE TABLE IF NOT EXISTS user (
    user_id         BIGINT      NOT NULL AUTO_INCREMENT,
    group_id        BIGINT      NOT NULL,
    right_id        BIGINT      NOT NULL,
    nz_id           TEXT        NOT NULL,
    nr              TEXT,
    name            TEXT        NOT NULL,
    email           TEXT        NOT NULL,
    password        TEXT        NOT NULL,
    language        TEXT        NOT NULL DEFAULT 'de',
    active          BOOLEAN     NOT NULL DEFAULT 1,
    createtime      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modtime         TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login      TIMESTAMP,
    PRIMARY KEY (user_id),
    CONSTRAINT key_user_nr_uniq   UNIQUE(nr) USING HASH, 
    CONSTRAINT user_fk_group_id    FOREIGN KEY (group_id)    REFERENCES group(group_id),
    CONSTRAINT user_fk_right_id     FOREIGN KEY (right_id)     REFERENCES right(right_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

My Problem is it isn't possible to get both of key_example_uniq and example_fk_user_id user_id and customer_nr should be unique and user_id should be a foreign key.

If I just create the uniq key or the foreign key it works but if I try both I get the following error:

ERROR 1005 (HY000): Can't create table `test`.`example` (errno: 150 "Foreign key constraint is incorrectly formed")

or

ERROR 1025 (HY000): Error on rename of './test/#sql-alter-5ea5-2f9' to './test/example' (errno: 150 "Foreign key constraint is incorrectly formed")
Banda89
  • 1
  • 1
  • 1
  • 1
    UNIQUE (user_id,custumer_nr - customer incorrectly spelled, GROUP is a reserved word if you really have a table called group it should be backticked, i suspect RIGHT has the same issue , and why all those TEXT columns - varchar is probably more appropriate? – P.Salmon Nov 29 '21 at 11:15
  • I forgot to mention that I renamend the columns for stack overflow because of data protection. The user table is created without any errors also the example table if don't use the foreign key or the unique key. – Banda89 Nov 29 '21 at 11:28
  • If what you have posted is not representative then we can't help you.. – P.Salmon Nov 29 '21 at 11:29
  • The key problem is that the unique key and foreign key cannot be together – Banda89 Nov 29 '21 at 11:30
  • I cannot reproduce your problem -see https://www.db-fiddle.com/f/2bw9ETmbo3WrRXoMC5CX4Z/0 which contains your code with a bit of tidying please post code which we can use to reproduce your issue. – P.Salmon Nov 29 '21 at 11:34
  • "150" usually means that the other table needed to be declared first. – Rick James Nov 29 '21 at 17:44
  • You cannot have a `TEXT` column in `UNIQUE` (or `INDEX`). This is a problem waiting to bite you. Start a new question about it. – Rick James Nov 29 '21 at 17:46

0 Answers0