How to create a FOREIGN KEY using uniqueidentifier in SQL Server

Question

How to create a FOREIGN KEY using uniqueidentifier in SQL Server

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

Hello, I'd like some help.

I have a table in SQL called Client, which has as primary key a type uniqueidentifier

Ex:

Create table Cliente (
    ClienteID uniqueidentifier not null,
    Nome varchar(50)
)

I'd like to know how to FOREIGN KEY using uniqueidentifier .

I did this but it did not work.

Create table Locacao (
    LocacaoId uniqueidentifier not null,
    ClienteID uniqueidentifier not null,
);

ALTER TABLE Locacao add constraint FK_Locacao_Cliente_ClienteID FOREIGN KEY(ClienteID) REFERENCES Cliente(ClienteID);

If someone can help me I appreciate it.

sql sql-server

asked by anonymous 19.07.2016 / 17:20

2 answers

How to open a new page when clicking on the table row? INSERT within a SELECT - SQL Server

score 2 · Answer 1

First you should mark your primary keys.

In the case of the Customer table, I believe it is the "ClientId" and in the case of the location I believe it to be the "LocationId". Then just create FOREIGN KEY

By following the command:

ALTER TABLE [dbo].[Locacao]  WITH CHECK ADD  CONSTRAINT [FK_Locacao_Cliente] FOREIGN KEY([ClienteID])
REFERENCES [dbo].[Cliente] ([ClienteID])

score 0 · Answer 2

UniqueIDentifier type is not required when creating your tables.

The uniqueidentifier data type stores 16-byte binary values   which operate as GUIDs (Globally Unique Identifiers). A GUID is a   exclusive binary number; no other computer in the world will generate a   duplicate of that GUID value. The primary use of a GUID is to assign   an indicator that must be identical on a network with multiple computers   on many websites.

The uniqueidentifier data type has the following disadvantages:


The values are long and dark. This makes it difficult for users to enter values correctly. In addition, it becomes difficult   remember them.

Values are random and can not accept any patterns that make them more meaningful to users.

There is no way to determine the sequence in which uniqueidentifier values were generated. They are not appropriate for   applications that depend on increasing   key in series.

At 16 bytes, the uniqueidentifier data type is relatively larger than other data types, such as 4-byte integers. That   means that indexes created using uniqueidentifier   be relatively slower than indexes that use an int key.

If there is no need to use this type of field, one way to solve it would be to use the IDENTITY type and set the primary keys for the two tables.

It would look like this:

Create table Client ( ClientID int primary key identity not null, Name varchar (50) )

Create table Location ( LocacaoId int primary key identity not null, ClientID int not null, );

ALTER TABLE Location ADD constraint FK_Locacao_Cliente_ClienteID FOREIGN KEY (ClientID) REFERENCES Client (ClienteID);

Reference: link