Foreign Key Sql Server

Question

Foreign Key Sql Server

Navigation

#1 by (3 votes)

1

I have a doubt refuse the foreign key in the following situation:

Tehno the 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 an FK between the two tables with the Cod_UF column, Sql gives a unique identifier error.

I understand the reason for the error, but what is the best solution to create an 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 certain UF. The column Cod_UF is the code that the IBGE determines.

The IDE table receives information from a document populated by third parties that I receive via webservice.

As I have no control of this webservice nor can I validate the information filled out 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 an IBGE code that does not exist in this table.

sql sql-server chave-estrangeira

asked by anonymous 30.08.2016 / 17:07

1 answer

Timer with Chronometer Place a table created in an existing container

score 3 · Accepted Answer

... what is the best solution to create an FK in a situation like this?

Pointing the foreign keys only to the 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);

Incidentally, I do not understand why you have two Cod_UF in both tables.

EDIT

To prevent CodIBGE from being able to support duplicate values, use:

ALTER TABLE UF
ADD UNIQUE (CodIBGE);

Or:

ALTER TABLE UF
ADD CONSTRAINT UQ_UF_CodIBGE UNIQUE (CodIBGE);