Table with Compound Key without repeating

Question

Table with Compound Key without repeating

Navigation

#1 by (3 votes)

1

How do I make a table with a compound key, but I can not repeat the combination of values.

I want to store FRIENDS , using a syntax similar to this:

tb_amizades id_usuario_um id_usuario_dois

SELECT * FROM tb_amizades 
WHERE id_usuario_um = id OR id_usuario_dois = id 
LIMIT (qtd_de_amigos);

This SELECT is inconsistent, but gives you the idea of what you want it to do. I'm using LIMIT so if I have 5 friends and the bank has more than 100 records and the system does not have to search at all.

mysql sql chave-composta

asked by anonymous 30.10.2017 / 15:10

1 answer

Use get_object_vars in a child class What is the difference between text / javascript and text / x-javascript? [duplicate]

score 3 · Accepted Answer

In this specific case, you can create a primary key .

CREATE TABLE tb_amizades 
(
    id_usuario_um INT NOT NULL,
    id_usuario_dois INT NOT NULL,
    CONSTRAINT tb_amizades_id_usu_um_id_usu_dois_pk 
      PRIMARY KEY (id_usuario_um, id_usuario_dois)
);

For all other cases, you can create a compound index

CREATE TABLE tb_amizades 
(
    id_usuario_um INT NOT NULL,
    id_usuario_dois INT NOT NULL
);

CREATE UNIQUE INDEX tb_amizades_id_usu_um_id_usu_dois_uindex 
    ON tb_amizades (id_usuario_um, id_usuario_dois);