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 need the entries in sName to be unique, and I need the entries in sAltName to be unique with this additional requirement:Any sName must not match any sAltName except when they are in the same record.Is there a way of setting up an index / constraint / whatever for this table to fulfill these requirements?

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) GOIt does what I wanted to achieve.

Unusual uniqueness requirement on two fields in a table

6 Replies

LainRobertson

Silver Contributor

Nov 10, 2021

robertrackl , bake13

I might be missing something in the requirement, but looking at the third constraint there, bake, that's not quite going to preclude an sAltName featuring in the sName column.

For example, the following will pass the third constraint where - again, so long as I've understood the requirement correctly - it shouldn't:

sName	sAltName
an sname	an saltname
an saltname	something else

The first two constraints are necessary but I've gone with a function to satisfy the robert's bullet-pointed requirement (i.e. as the third constraint). This throws the expected constraint violation when using the above bogus data.

Let me know your thoughts.

CREATE TABLE dbo.test
(
	sName varchar(max) NOT NULL
	, sAltName varchar(max) NOT NULL
)
GO


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

	return 0;
END
GO


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

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

bake13

Microsoft

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.

Forum Discussion

Unusual uniqueness requirement on two fields in a table

6 Replies

Resources