Forum Discussion

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

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.

robertrackl

Copper Contributor

Nov 12, 2021

LainRobertson , bake13

Building on LainRobertson 's ideas, this is what I have come up with (it prevents repeating something across the sName and sAltName columns, but it also prevents sName and sAltName being the same in the same record which I am trying to allow):

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.SNameInAltName (@sName AS nvarchar(128))
RETURNS bit
AS
BEGIN
	IF EXISTS (SELECT sAltName FROM dbo.test AS [t] WITH (nolock) WHERE [t].sAltName = @sName) 
		return 1;
	return 0;
END
GO

CREATE FUNCTION dbo.SAltNameInName (@sAltName AS nvarchar(256))
RETURNS bit
AS
BEGIN
	IF EXISTS (SELECT sName FROM dbo.test AS [t] WITH (nolock) WHERE [t].sName = @sAltName) 
		return 1;
	return 0;
END
GO

ALTER TABLE dbo.test
	ADD CONSTRAINT sNameNotInAltNames CHECK (dbo.SNameInAltName(sName) = 0)
GO
ALTER TABLE dbo.test
	ADD CONSTRAINT sAltNameNotInNames CHECK (dbo.SAltNameInName(sAltName) = 0)
GO

Can someone help with the last requirement that sName = sAltName in the same record should be allowed?

LainRobertson

Silver Contributor

Nov 12, 2021

robertrackl

Sticking with the original three constraint approach, you can just alter the existing function to accommodate this requirement. A second function isn't necessary to achieve this.

Here's an example for dropping the constraint, updating the function and putting it back. Just change things like the tables names to suit your environment.

-- Drop the existing constraint so the function can be updated.
ALTER TABLE [dbo].[test] DROP CONSTRAINT [sNameNotInAltNames]
GO

-- Alter the function.
ALTER FUNCTION [dbo].[SNameInAltName] (@sName AS varchar(max))
RETURNS int
AS
BEGIN
	IF EXISTS (SELECT sAltName FROM dbo.test AS [t] WITH (nolock) WHERE [t].sName <> @sName AND [t].sAltName = @sName) 
		return 1;

	return 0;
END
GO

-- Put the constraint back in place.
ALTER TABLE [dbo].[test]  WITH CHECK ADD  CONSTRAINT [sNameNotInAltNames] CHECK  (([dbo].[SNameInAltName]([sName])=(0)))
GO

robertrackl

Copper Contributor

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.

LainRobertson

Silver Contributor

Nov 12, 2021

robertrackl

Glad to hear it, mate!

In the interest of efficiency, you might want to consider cutting the two queries inside the function down to one:

ALTER FUNCTION [dbo].[SNameInAltName] (@sName AS varchar(max), @sAltName AS varchar(max))
RETURNS int
AS
BEGIN
	IF (@sName <> @sAltName) AND EXISTS (SELECT 1 FROM dbo.test AS [t] WITH (nolock) WHERE [t].sName = @sAltName OR [t].sAltName = @sName)
		return 1;

	return 0;
END