CODEWITHSUNDEEP
sql-server
Zoinky
Zoinky

Reputation: 5019

SQL Server UniqueIdentifier as FK

I am trying to execute the following but its giving an error of, I tried doing it with designer, same error saying data type, precision and length must be the same, even though they are

ALTER TABLE CustomerUsers
ADD CONSTRAINT fk_CustomerUsers_Users
FOREIGN KEY (CustomerID)
REFERENCES Customers(UniqueID)

Error thrown:

Msg 1769, Level 16, State 1, Line 1
Foreign key 'fk_CustomerUsers_Users' references invalid column 'UniqueID' in referencing table 'CustomerUsers'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Here is My Table Structure.

CREATE TABLE [dbo].[Customers](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](500) NOT NULL,
[ContentLocation] [nvarchar](500) NOT NULL,
[UniqueID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
[CustomerID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[CustomerUsers](
[CustomerUserID] [int] IDENTITY(1,1) NOT NULL,
[CustomerUniqueID] [uniqueidentifier] NOT NULL,
[UserID] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_CustomerUsers] PRIMARY KEY CLUSTERED 
(
[CustomerUserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CustomerUsers]  WITH CHECK ADD  CONSTRAINT [FK_CustomerUsers_AspNetUsers] FOREIGN KEY([UserID])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO

ALTER TABLE [dbo].[CustomerUsers] CHECK CONSTRAINT [FK_CustomerUsers_AspNetUsers]
GO

Upvotes: 0

Views: 5997

Answers (2)

Drew
Drew

Reputation: 2663

I do believe the following should do the trick (in the case that you're wanting to set up CustomerUsers.CustomerUniqueID as a FK)

ALTER TABLE customerusers 
  ADD CONSTRAINT fk_customerusers_users FOREIGN KEY (customeruniqueid) 
  REFERENCES customers(uniqueid)

what I changed: inferred that error was related to column not existing, noticed that you had already created a column which looked like what you might have been trying to shoot for, added that

Upvotes: 0

Rajesh
Rajesh

Reputation: 1620

There is no column with the name CustomerID in CustomerUsers just CustomerUniqueID modify your query as below

ALTER TABLE CustomerUsers
ADD CONSTRAINT fk_CustomerUsers_Users
FOREIGN KEY (CustomerUniqueID)
REFERENCES Customers(UniqueID)

Query same as @Drew

Upvotes: 1

Related Questions