Constraint for only one record marked as default

by

Use a unique filtered index

On SQL Server 2008 or higher you can simply use a unique filtered index

CREATE UNIQUE INDEX IX_TableName_FormID_isDefault
    ON TableName(FormID)
    WHERE isDefault = 1

Where the table is

CREATE TABLE TableName(
    FormID INT NOT NULL,
    isDefault BIT NOT NULL
)

For example if you try to insert many rows with the same FormID and isDefault set to 1 you will have this error:

Cannot insert duplicate key row in object ‘dbo.TableName’ with unique
index ‘IX_TableName_FormID_isDefault’. The duplicate key value is (1).

Source: http://technet.microsoft.com/en-us/library/cc280372.aspx

More Related Contents:

  1. What is wrong with this DB Diagram? [closed]
  2. I’m getting ‘Column name is invalid in the select list’ error when i execute the query
  3. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  4. Efficiently convert rows to columns in sql server
  5. How can I do an UPDATE statement with JOIN in SQL Server?
  6. Why does NULL = NULL evaluate to false in SQL server
  7. The multi-part identifier could not be bound
  8. How can I select the first day of a month in SQL?
  9. How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
  10. T-SQL – Insert Data into Parent and Child Tables
  11. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  12. Is it possible to specify condition in Count()?
  13. Fastest way to update 120 Million records
  14. Find the smallest unused number in SQL Server
  15. MS SQL “ON DELETE CASCADE” multiple foreign keys pointing to the same table?
  16. Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?
  17. Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column
  18. Should I use SQL_Variant data type?
  19. Get the records of last month in SQL server
  20. Log record changes in SQL server in an audit table
  21. I want to use CASE statement to update some records in sql server 2005
  22. alternatives to REPLACE on a text or ntext datatype
  23. How can I backup a remote SQL Server database to a local drive?
  24. Sql Server trigger insert values from new row into another table
  25. Getting the Id of a row I updated in Sql Server
  26. Zero SQL deadlock by design – any coding patterns?
  27. UNION ALL vs OR condition in sql server query
  28. How to execute a stored procedure inside a select query
  29. How to Execute SQL Query without Displaying results
  30. SQL Inner Join On Null Values

Leave a Comment