Sql-server – Unique Contraint/Index Based On Values

constraintindexsql serversql-server-2005

I have a table that has the following column definitions:

ID (INT, PK)
Name (VarChar)
Active (Bit)
Bunch_of (Other_columns)

Question: I want to have a Constraint on Name/Active such that we can only have 1 record with a given Name that is Active (Active = 1), but we could have many records that have the same Name that are Inactive (Active = 0). Is this possible?

I've tried the normal Unique constraints, but they limit the dataset to only allow 1 record with a given Name to be Inactive.

Best Answer

To enforce this declaratively in 2005 you can use an indexed view.

CREATE VIEW dbo.ActiveNames
WITH SCHEMABINDING
AS
SELECT Name
FROM  dbo.YourTable
WHERE Active = 1

GO

CREATE UNIQUE CLUSTERED INDEX UQ_ActiveNames ON dbo.ActiveNames(Name)

Related Solutions

Mysql – Unique insert performance: Primary Key vs Unique Index

From your question, I'm not completely sure if you're inserting multiple values per query, but you definitely should be. With MyISAM as the engine, there should not be a performance difference between the index being UNIQUE or PRIMARY; MyISAM doesn't treat them differently in this case. If you were using InnoDB, however, there would be a difference since it stores the data in primary key order. If you don't need the id column, removing it and making domain the primary key would help performance.

Changing the collation should help since ascii is much simpler than utf8, but you might want to use ascii_general_ci instead of ascii_bin since domain names are case-insensitive.

One other way to do the queries would be to get the number of rows, INSERT DELAYED, flush the delayed writes, and then get the new row count. The difference in the counts would be the same as the affected rows. However, I don't think this would be significantly faster, but it would make the process more complex.

PostgreSQL Unique Constraint Validation from Input – How to Create

This can be achieved by using a unique index on the combination of counter and the month of date_booked. Something like this:

create unique index idx_tbl on tbl (counter, to_char(date_booked, 'yyyymm'));

However the above will fail because to_char() is not an "immutable" function and thus cannot be used in an index (even if we know that the above is fine). The workaround for this is to create a wrapper function that is marked as immutable:

create function my_month(some_date date)
  returns varchar
  immutable 
  language sql
as  
$$
  select to_char(some_date, 'yyyymm')
$$
;

Then you can create an index using that function:

create unique index idx_tbl on tbl (counter, my_month(date_booked));

If you now insert the fourth row, you get the error:

duplicate key value violates unique constraint "idx_tbl"

If the table is big, you might want to use a number instead of a varchar in order to reduce the size of the index as well e.g. by using to_number(to_char(some_date, 'yyyymm'))

Best Answer

Related Solutions

Mysql – Unique insert performance: Primary Key vs Unique Index

PostgreSQL Unique Constraint Validation from Input – How to Create

Related Question