Is it possible not to allow duplicity in a combination of 2 columns or more?

Question

I know that SQL Server has the Primary Keys and start not to allow duplicate records, but it is possible to do this through 2 columns or more?

As an example, I have a table with column, house and dog, where the records can repeat the houses and also the dogs, but can not repeat the same house with the same dog.

INSERT INTO table VALUES ('Laranja', 'Rex') << OK
INSERT INTO table VALUES ('Azul', 'Rex') << OK
INSERT INTO table VALUES ('Laranja', 'Tob') << OK
INSERT INTO table VALUES ('Azul', 'Tob') << OK
INSERT INTO table VALUES ('Laranja', 'Rex') << ERRO - DUPLICIDADE

It is possible to do this only by table creation?

Answer 1

Yes, it is possible, the simplest way would be like this:

CONSTRAINT nome_da_chave UNIQUE (coluna1, coluna2)

If you are going to change an existing table you have to do:

ALTER TABLE tabela ADD

before this.

I put in the Github for future reference.

This is creating an index with these two columns as key.

If it is a primary key then just determine that both are part of the primary since every primary is unique.