Enforce a unique constraint on a business key in single table

Question

I have this table created in SQL Server which defines a business operating in each state. Each business can be in multiple states.

• Row_ID is the surrogate key and unique for each row in the table.
• Business_ID is the unique value for each Business_name.
• Business_ID and State_code define the relationship that business operates in each state, it's a one-to-many relationship.

Row_Id	Business_ID	Business_name	State_code
1	101	Hair Salon	CA
2	102	Nail Salon	CA
3	103	Spa	CA
4	101	Hair Salon	TX
5	102	Nail Salon	TX
6	104	Styling	NY
--------	--------	--------	--------

CREATE TABLE [dbo].[Dim_Business_Regions]
(
    [Row_ID] [int] IDENTITY(1,1) NOT NULL,
    [Business_ID] [int] NOT NULL,
    [Business_Name] [varchar](100) NOT NULL,
    [State_Code] CHAR(2)
)

I am trying to enforce a unique constraint on the combination of Business_ID and Business_name so that there is always one Business_ID for each Business_name.

Please see the data in the table.

• If we insert a 7th row as (101, Hair Salon, NY) - this should be valid row, as 101 is assigned for Hair Salon.

• If we insert a 7th row as (103, Hair Salon, NY) - this should be an invalid row and the INSERT should fail, as 103 is assigned for Spa and Hair Salon is 101

I couldn't break/Normalize this table into two different tables as there is lot of dependent code that has to be refactored.

Please suggest a solution to enforce this so that I can insert rows with the same Business_ID and same Business name with a different state but insert should fail when I try to insert the reuse the Business_ID with a different Business_Name.

Answer 1

1. Create a new table that contains a normalized representation of the Business' data: dbo.Businesses.
2. Add a composite UNIQUE constraint to dbo.Businesses, so a BusinessId + BusinessName tuple can be used as an FK into dbo.Businesses and to ensure dbo.Dim_Business_Regions doesn't contain any anomalous data in those columns.
3. Copy DISTINCT data over (with or without SET IDENTITY_INSERT depending on if you want dbo.Businesses to use IDENTITY or a SEQUENCE - or some other value source).
4. Wrap it in a TRANSACTION with XACT_ABORT ON, as you should.

CREATE TABLE dbo.Businesses (
    BusinessId   int           NOT NULL /*IDENTITY*/,
    BusinessName nvarchar(100) NOT NULL,
    StateCode    char(2)       NOT NULL,

    CONSTRAINT PK_Businesses PRIMARY KEY ( BusinessId ),

    CONSTRAINT UK_BusinessIdName UNIQUE ( BusinessId, BusinessName )
);

GO

SET XACT_ABORT ON

BEGIN TRANSACTION btxn;

SET IDENTITY_INSERT dbo.Businesses ON;

INSERT INTO dbo.Businesses ( BusinessId, BusinessName, StateCode )
SELECT DISTINCT r.Business_ID, r.Business_Name, r.StateCode FROM dbo.Dim_Business_Regions AS r ORDER BY r.Business_ID;

SET IDENTITY_INSERT dbo.Businesses OFF;

GO

ALTER TABLE dbo.Dim_Business_Regions
    ADD CONSTRAINT FK_BusinessIdName FOREIGN KEY ( Business_ID, Business_Name ) REFERENCES dbo.Businesses ( BusinessId, BusinessName ) ON UPDATE CASCADE;

COMMIT TRANSACTION btxn;

Some notes:

• Having both PK_Businesses and UK_BusinessIdName is redundant, but I assume that you'll eventually fix the dbo.Dim_Business_Regions table (or convert it to a VIEW) in which case you'll only need to DROP CONSTRAINT UK_BusinessIdName while retaining the PK_Businesses (which avoids having to rebuild the clustering-index).

• Note that I made BusinessName an nvarchar(100) column instead of varchar(100). Always store human-readable names in a Unicode-compatible way.

• Consider making dbo.Businesses a temporal table - it's been a huge lifesaver overall.