Unique Constraint Workaround? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unique Constraint Workaround?

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2008-10-18 : 16:09:33

I want to create a unique constraint on three columns. The only time I need for the constraint to force an exception is when all 3 have the same values. However, since two of them are NVARCHAR fields, the recommended size is exceeded. Is there another way to implement this business rule?

Here is an example of what I tried to do:

[CODE]IF NOT OBJECT_ID('[dbo].[foo_table]') IS NULL
DROP TABLE [foo_table];

CREATE TABLE [dbo].[foo_table](
[ft_id] INT NOT NULL IDENTITY(1,1),
[module_id] INT NOT NULL,
[setting_name] NVARCHAR(50) NOT NULL,
[setting_value] NVARCHAR(500) NULL,
[IsDeleted] BIT NOT NULL,
[LastUpdatedBy] INT NULL,
[LastUpdatedOn] DATETIME NULL
) ON [PRIMARY];

ALTER TABLE [dbo].[foo_table] ADD CONSTRAINT [PK_foo_table_id] PRIMARY KEY CLUSTERED
([pm_id]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

ALTER TABLE [dbo].[foo_table] ADD CONSTRAINT [DF_foo_table_IsDeleted] DEFAULT ((0)) FOR [IsDeleted];

ALTER TABLE [dbo].[foo_table] ADD CONSTRAINT [UQ_foo_table_settings]
UNIQUE NONCLUSTERED ([module_id],[setting_name],[setting_value]) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_foo_table_module_id] ON [dbo].[foo_table]([module_id]) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [IX_foo_table_module_id_setting_name_isdeleted]
ON [dbo].[foo_table]([module_id],[setting_name],[IsDeleted]) ON [PRIMARY];[/CODE]

Here is the warning error that is sent back to me:

Warning! The maximum key length is 900 bytes. The index 'UQ_foo_table_settings' has maximum length of 1104 bytes. For some combination of large values, the insert/update operation will fail.

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2008-10-18 : 21:15:36

I've run into a similiar problem before and added an additional field CHECKSUM(setting_value) and then created the unique index on the setting name, checksum and isdeleted fields. Only problem is the checksum isn't guaranteed to create a unique value. It depends on what you are willing to live with.

One other question I have for you seeing that you are enforced the constraint with the IsDeleted field. Once a record is deleted and it is added a second time, are you not allowed to deleted that record?

For example if you have

Setting Name Setting Value IsDeleted
Setting1 Value1 0
Setting1 Value1 1

If this situation existed you couldn't delete setting1 again.

hismightiness
Posting Yak Master

164 Posts

Posted - 2008-10-19 : 11:56:51

I am just trying to make sure that the "setting name" and "setting value" combination only appears once for each module id.

- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://blog.strohlsitedesign.com/
http://skins.strohlsitedesign.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources