0

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
Improve this question
1
  • Well CustomerUsers doesn't have a column named UniqueID. Did you mean to use CustomerUniqueID? Did you also consider calling it CustomerID in all locations? ID already implies that it is probably unique... Commented Nov 17, 2014 at 20:36

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

1 Comment

Suggestion for improvement of your answer: Explain why your revised code "should do the trick" instead of just posting it. As it is, we have to compare your revised code against to original just to see what you have changed, which might be a hint why the original wasn't working. Also, you don't explicitly say why you think the OP would want to set up CustomerUniqueID. It's obvious that his original FK column doesn't exist, but you're not actually saying that.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.