Error while creating table

Asked

Viewed 120 times

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 he also has to be my friend, now that the concept is clear, I would like to know how to create this table because the following error is occurring: inserir a descrição da imagem aqui

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

  • Is mysql or mariadb ?

  • 2

    Sorry, the concept is still not very clear. I could explain better: a friend is also a user and when I add a user as a friend he also has to be my friend. Additionally, you can ask the question the table structure usuarios?

  • i have no way to test mysql here, only with Sql server... if I run it on S.Server it gives error if I do not create the key Primary in the users table. In your time?

  • Sorry Mother, I don’t understand!

  • RBZ is mariadb, but there is some difference?

  • What else is in this db? I tested the query here and it worked perfectly, you must be duplicating something as it says in the error, there must already be some other table using the keys

  • I believe the friendship table already exists and you instead of creating it, you want to add/change some field

  • I meant if Voce created PRIMARY KEY in the use table Users

  • @Danrleyfernandeslopes, the tags are to determine exactly your scenario. If you put tags that are unrelated, yes, they may have differences depending on the context.

  • Some differences for new functions: https://mariadb.com/kb/en/library/mariadb-vs-mysql-features/

Show 5 more comments

1 answer

3


The name of the Foreign key you have defined as u_id_fk is already stated in the table postagens. Name of Foreign key is unique, table independent. Create another name for u_id_fk on the table amizade.

You will receive this message if you are trying to add a restriction with a name that has already been used elsewhere. If the table you are trying to create includes a key constraint foreign 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, example:

fk_<TABLE_NAME>_<FOREIGN_KEY_COLUMN_NAME>

To check the existing 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;

Source of the answer: ERROR: Error 1005: cannot create table (Rrno: 121)

Browser other questions tagged mariadb

You are not signed in. Login or sign up in order to post.