How to create a Foreign key and unique attribute at the same time?

1

I wonder if there is any way, or how best, to restrict a relationship between two entities, one of which can not be repeated in this relationship. I have the 'Customers' and 'Telephone' tables. But 1 customer can have several phones, so I made a 'Clients_Telephone' relationship. The problem is that the phone number can not be repeated, but it must exist in the

tabela telefones.'create table cliente(
    cod_cli int primary key,
    nome varchar(40)
);

create table telefone(
    identificador int primary key,
    numero int unique
);

 create table cliente_telefone(
    id_cli_tel int primary key,
    cod_cli int,
    identificador int,
    constraint fk_cli foreign key(cod_cli) references cliente(cod_cli),
    constraint fk_tel foreign key(identificador) references telefone(identificador)
);
    
asked by anonymous 30.10.2017 / 02:52

1 answer

2

A Unique Key Constraint resolves

ALTER TABLE cliente_telefone
ADD CONSTRAINT cliente_telefone_uk UNIQUE (numero);

Documentation: link

    
30.10.2017 / 15:53