Foreign Key Sql Server

Question

I have a question about Foreign key in the following situation:

Tehno table UF, with 3 columns:

ID_UF (PK), Cod_uf (int) and Descuf (nvarchar(50))

I have another table called IDE, with 3 columns:

ID_IDE (PK), Cod_uf(int) and Descide (nvarchar(50))

When I try to create a FK between the two tables with the Cod_uf column Sql gives a unique identifier error.

I understand the reason for the mistake, but what is the best solution to create a FK in a situation like this?

EDIT:

Answering questions about Cod_uf:

The ID_UF column is the internal code of the application for a given FU. The Cod_uf column is the code that the IBGE determines.

The IDE table receives information from a third party document that I receive via web service.

As I have no control of this webservice nor have to validate the information filled by the citizen on the other side of the webservice I thought of creating this FK between the UF table and the IDE table to avoid saving in this table an IBGE code that does not exist.

Answer 1

..., what is the best solution to create an FK in such a situation?

Pointing foreign keys only to primary keys. For example:

CREATE TABLE UF (
    ID_UF INT PRIMARY KEY IDENTITY, 
    Cod_UF INT NOT NULL,
    DescUF nvarchar(50) NOT NULL
);

CREATE TABLE IDE (
    ID_IDE INT PRIMARY KEY IDENTITY, 
    Cod_UF INT NOT NULL
    DescIDE nvarchar(50) NOT NULL
);

ALTER TABLE IDE
ADD CONSTRAINT IDE_UF_FK FOREIGN KEY (Cod_UF) REFERENCES UF (ID_UF);

In fact, I don’t understand why there are two Cod_UF in both tables.

---

EDIT

To prevent CodIBGE cannot accept duplicate values, use:

ALTER TABLE UF
ADD UNIQUE (CodIBGE);

Or:

ALTER TABLE UF
ADD CONSTRAINT UQ_UF_CodIBGE UNIQUE (CodIBGE);