Composite Key Table without repetition

Question

How do I make a table with composite key, but I can’t repeat the combination of values.

I want to store FRIENDSHIPS, using a syntax similar to is:

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 you can give the idea of what it does to you. I’m using LIMIT in case I have 5 friends and the bank has more than 100 records and the system doesn’t have to search at all.

Answer 1

In this specific case, it is possible to create a Primary key composed.

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, it is possible to create a composite 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);