Error creating table

Question

Error creating table

Navigation

#1 by (3 votes)

0

I have a users table and a friendship table, a friend is also a user and when I add a user as a friend it also has to be my friend, now that the concept is clear, I would like to know how do I create this table , because the following error is occurring:

CREATE TABLE IF NOT EXISTS postagens(
    id INT NOT NULL AUTO_INCREMENT,
    u_id                INT,
    titulo              VARCHAR(128) NOT NULL,
    conteudo            VARCHAR(4096),
    imagem              VARCHAR(256),
    audio               VARCHAR(256),
    video               VARCHAR(256),
    CONSTRAINT id_pk    PRIMARY KEY(id),
    CONSTRAINT u_id_fk  FOREIGN KEY(u_id) REFERENCES usuarios(id)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS amizade(
    id                  INT NOT NULL AUTO_INCREMENT,
    u_id                INT,
    a_id                INT,
    CONSTRAINT id_pk    PRIMARY KEY(id),
    CONSTRAINT u_id_fk  FOREIGN KEY(u_id) REFERENCES usuarios(id),
    CONSTRAINT a_id_fk  FOREIGN KEY(a_id) REFERENCES usuarios(id)
) DEFAULT CHARSET=utf8;

mariadb

asked by anonymous 09.04.2018 / 18:20

1 answer

What's wrong with this code to insert data into mysql? How to do? php echo $ i? be random between Y and X?

score 3 · Accepted Answer

The name of the foreign key that you set to u_id_fk is already declared in the postagens table. foreign key name is unique, regardless of table. Create another name for u_id_fk in table amizade .

You will receive this message if you are trying to add a with a name that has already been used elsewhere. If the table you are trying to create includes a key constraint and you provided your own name for this restriction , remember that it must be unique in the database.

A good practice of creating foreign keys is to always have a naming pattern, for example:

fk_<TABLE_NAME>_<FOREIGN_KEY_COLUMN_NAME>

To check the restrictions in the MySQL / Mariadb database, use the following SQL query:

SELECT
    constraint_name,
    table_name
FROM
    information_schema.table_constraints
WHERE
    constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
    constraint_name;

Response Source: ERROR: Error 1005: Can not create table (errno: 121)