Forum Discussion

robertrackl's avatar
robertrackl
Copper Contributor
Nov 06, 2021
Solved

Unusual uniqueness requirement on two fields in a table

I have a table called SF_ACCOUNTS that represents the chart of accounts in a bookkeeping system. Among the fields of the table are sName and sAltName, the name of the account and an alternate name. I...
  • robertrackl's avatar
    robertrackl
    Nov 12, 2021

    LainRobertson 

    I think I've got it now. Thanks for your inspiration!

    CREATE TABLE dbo.test
(sName nvarchar(128) NOT NULL, sAltName nvarchar(256) NOT NULL)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_test_sName] ON [dbo].[test] ([sName] ASC)
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_test_sAltName] ON [dbo].[test] ([sAltName] ASC)
GO

CREATE FUNCTION dbo.bNameXAlt (@sName AS nvarchar(128), @sAltName AS nvarchar(256))
RETURNS bit
AS
BEGIN
	IF @sName = @sAltName  RETURN 0
	IF
		EXISTS (SELECT sName FROM dbo.test AS [t] WHERE [t].sName = @sAltName)
		OR
		EXISTS (SELECT sAltName FROM dbo.test AS [t] WHERE [t].sAltName = @sName)
		RETURN 1
	RETURN 0
END
GO

ALTER TABLE dbo.test
	ADD CONSTRAINT sNameNotInAltNames CHECK (dbo.bNameXAlt(sName, sAltName) = 0)
GO

    It does what I wanted to achieve.

     

bake13's avatar
bake13
Icon for Microsoft rankMicrosoft
Nov 08, 2021

Hi robertrackl --  You should be able to create a few unique constraints to address your requirements.  Below is an example based loosely on the information you provided regarding your table.

 

Here is my SF_ACCOUNTS table

CREATE TABLE SF_ACCOUNTS (AccountID smallint identity(1,1),sName nvarchar(128), sAltName nvarchar(128))

I'll create the following unique constraints:

1.  One unique constraint for the sName

2.  One unique constraint for the sAltName

3.  One unique constraint for the combination of AccountID, sName, sAltName 

ALTER TABLE SF_ACCOUNTS ADD CONSTRAINT UniquesName UNIQUE(sName)
ALTER TABLE SF_ACCOUNTS ADD CONSTRAINT UniquesAltName UNIQUE(sAltName)
ALTER TABLE SF_ACCOUNTS ADD CONSTRAINT validNames UNIQUE (AccountId,sName,sAltName)

And test using somethin like what is shown below:

-- This is valid
INSERT INTO SF_ACCOUNTS VALUES ('an sname','an saltname')
-- This is valid
INSERT INTO SF_ACCOUNTS VALUES ('an sname with the same saltname','an sname with the same saltname')
-- This is not valid
INSERT INTO SF_ACCOUNTS VALUES ('an sname','an saltname')
-- This is not valid
INSERT INTO SF_ACCOUNTS VALUES ('a new sname','an saltname')

Hope that helps.  Take care.

Resources