How to create a FOREIGN KEY using uniqueidentifier in SQL Server

Question

Hi, I’d like a hand.

I have a table on SQL Customer call, 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 make one FOREIGN KEY using the uniqueidentifier.

I did it but it didn’t 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 anyone could help me I’d appreciate it.

Answer 1

First you must mark your primary keys.

In the case of the Client table, I believe to be the "Clienteid" and in the case of the lease I believe to be the "Locacaoid". Then just create the FOREIGN KEY

Leaving the command as follows:

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

Answer 2

It was not clear the need for use of the type uniqueidentifier in creating their tables.

The unique data type stores 16 byte binary values operating as Guids (Globally Unique Identifiers). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value. The main use of a GUID is to assign an indicator that must be identical on a network with several computers on many websites.

The type of uniqueidentifier data has the following disadvantages:

• The values are long and obscure. This makes it difficult for users to correctly type the values. Moreover, it becomes difficult remind them.
• Values are random and cannot accept any pattern that makes them more meaningful to users.
• There is no way to determine the sequence in which uniqueidentifier values were generated. They are not appropriate for existing applications that depend on the increment of the values of serial key.
• At 16 bytes, the uniqueidentifier data type is relatively larger than other data types, such as 4-byte integers. This means that indexes created using uniqueidentifier keys can 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 type IDENTITY and set the primary keys for the two tables.

Would look like this:

Create table Client ( Clienteid int Primary key Identity not null, Name varchar(50) )

Create table Location ( Locacaoid int Primary key Identity not null, Clienteid int not null, );

ALTER TABLE Locacao ADD Constraint Fk_locacao_cliente_clienteid FOREIGN KEY(Clienteid) REFERENCES Client(Clienteid);

Reference: https://technet.microsoft.com/pt-br/library/ms190215(v=sql.105). aspx