CODEWITHSUNDEEP
sqlazureazure-sql-database
Joe Ruder
Joe Ruder

Reputation: 2194

SQL on Azure - using a computed column as a primary key index

I am not sure what is wrong with the below SQL. I used to have a primary key based off of the customer_reference_no. They now have some duplicates so I am creating a column called uniquePoint that is a combination of customer_no, customer_reference_no and stop_zip.

The below works fine:

CREATE TABLE [dbo].[stop_address_details] (
[customer_no]                NCHAR (5)    NOT NULL,
[customer_reference_no]      VARCHAR (20) NOT NULL,
[stop_name]                  VARCHAR (40) NOT NULL,
[stop_address]               VARCHAR (40) NULL,
[stop_city]                  VARCHAR (30) NULL,
[stop_state]                 CHAR (2)     NULL,
[stop_zip]                   VARCHAR (10) NULL,
[point_no]                   VARCHAR (20) NULL,
[branch_id]                  VARCHAR (6)  NULL,
[delivery_route]             VARCHAR (10) NULL,
[dateTimeCreated]            DATETIME     NULL,
[dateTimeUpdated]            DATETIME     NULL,
[estimated_delivery_time]    TIME (0)     NULL,
[est_del_time]               DATETIME     NULL,
[dateTimeLastUsedInDatatrac] DATETIME     NULL,
[uniquePoint] as customer_no + '_' + customer_reference_no + '_' + stop_zip PERSISTED , 
CONSTRAINT [AK_stop_address_details_customer_reference_no] UNIQUE NONCLUSTERED ([customer_reference_no] ASC), 
CONSTRAINT [PK_stop_address_details] PRIMARY KEY ([uniquePoint])

But when I remove the constraint for customer_reference_no I get the following error: SQL71516 :: The referenced table '[dbo].[stop_address_details]' contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column is a computed column, it should be persisted.

I am referencing the computed column and it is persisted.

Not sure what is missing?

Thank you,

Joe

Upvotes: 0

Views: 78

Answers (2)

Joe Ruder
Joe Ruder

Reputation: 2194

The answer appears to be that I have another table that is referencing this table with a foreign key:

REATE TABLE [dbo].[rep_assigned_stop_matrix] (
[customer_reference_no] VARCHAR (20) NOT NULL,
[rep_id]                INT          NULL,
[dateTimeCreated]       DATETIME     NULL,
[sendSMS]               BIT          NULL,
[sendEmail]             BIT          NULL,
[id]                    INT          IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_rep_assigned_stop_matrix] PRIMARY KEY CLUSTERED ([id] ASC),
CONSTRAINT [AK_rep_assigned_stop_matrix_Column] UNIQUE NONCLUSTERED ([customer_reference_no] ASC, [rep_id] ASC),
CONSTRAINT [FK_pod_update_lookup_rep_info] FOREIGN KEY ([rep_id]) REFERENCES [dbo].[rep_info] ([id]) ON DELETE CASCADE,
CONSTRAINT [FK_lookup_Stop_Details] FOREIGN KEY ([customer_reference_no]) REFERENCES [dbo].[stop_address_details] ([customer_reference_no])

);

When this bottom constrain was removed the error went away. What I don't understand is why the error message was not a bit clearer (meaning naming the rep_assigned_stop_matrix table) -- or am I still missing something?

Joe

Upvotes: 1

Tim3880
Tim3880

Reputation: 2583

It seems that your '[dbo].[stop_address_details]' is still referring to the customer_reference_no column. Try Remove and re-add it using the new column name.

Upvotes: 0

Related Questions