Constraints in SQL Database

Question

I need to have a table in T-SQL which will have the following structure

KEY     Various_Columns       Flag
 1          row 1              F
 2          row_2              F
 3          row_3              T
 4          row_4              F

Either no rows, or at most one row can have the Flag column with the value T. My developer claims that this can be achieved with a check constraint placed on the table.

Questions:

1. Can such a constraint be placed on the database itself (ie an inter-row constraint) at the database level, rather than in business rules for updating or inserting rows
2. Is such a table in normal form?
3. Or would normal form require removing the Flag column, and instead (say) had another simple table or variable containing the value of row which had Flag=T, ie in the above case row=3.

Answer 1

1 No. A check constraint is per row. No other constraint will do this either.

You need one of:

• a trigger (all versions)
• indexed view with filter Flag = T, and unique index on Flag (SQL Server 2000+)
• filtered index (SQL Server 2008)

2 Good enough

3 Overkill really. You're splitting the same data up to avoid one the solutions above. But using a one row table, FK for the ID columns, and a unique constraint on Flag

Answer 2

My developer claims that this can be achieved with a check constraint placed on the table.

SQL Server does not directly** support subqueries in CHECK constraints (a requirement for Full SQL-92; SQL Server is only compliant with Entry Level SQL-92, broadly speaking).

While there are almost certainly better ways of enforcing this constraint in SQL Server, purely out of interest it can indeed be achieved using a row-level CHECK constraint and a UNIQUE constraint e.g. here's one way:

CREATE TABLE YourStuff
(
 key_col INTEGER NOT NULL UNIQUE, 
 Various_Columns VARCHAR(8) NOT NULL, 
 Flag CHAR(1) DEFAULT 'F' NOT NULL
    CHECK (Flag IN ('F', 'T')), 
 Flag_key INTEGER UNIQUE, 
 CHECK (
        (Flag = 'F' AND Flag_key = key_col)
        OR
        (Flag = 'T' AND Flag_key = NULL)
       )
);

The issue here is that you will need to maintain the Flag_key column's values 'manually'. Replacing the column + CHECK with a calculated column would mean the values are maintained automatically:

CREATE TABLE YourStuff
(
 key_col INTEGER NOT NULL UNIQUE, 
 Various_Columns VARCHAR(8) NOT NULL, 
 Flag CHAR(1) DEFAULT 'F' NOT NULL
    CHECK (Flag IN ('F', 'T')), 
 Flag_key AS (
              CASE WHEN Flag = 'F' THEN key_col
                   ELSE NULL END
             ), 
 UNIQUE (Flag_key)
);

---

** While SQL Server does not directly support subqueries in CHECK constraints, there is a workaround in some cases using a user defined function (UDF) e.g.

CREATE FUNCTION dbo.CountTFlags ()
RETURNS INTEGER
AS
BEGIN
DECLARE @return INTEGER;
SET @return = (
               SELECT COUNT(*)
                 FROM YourStuff
                WHERE Flag = 'T'
              );
RETURN @return;
END;

CREATE TABLE YourStuff
(
 key_col INTEGER NOT NULL UNIQUE, 
 Various_Columns VARCHAR(8) NOT NULL, 
 Flag CHAR(1) DEFAULT 'F' NOT NULL
    CHECK (Flag IN ('F', 'T')), 
 CHECK (1 >= dbo.CountTFlags())
);

Note that the UDF approach won't work in every case and that caution is required. The important point is that UDF will be evaluated for each row affected (rather than at the SQL statement or transaction level, as you may expect). In this case, the constraint needs to be true for every row affected and therefore -- I think! -- it is safe. For more details, see Trouble with CHECK Constraints by David Portas.

---

Personally, I would simply use a second table to model Flag, which would only involve keys and a foreign key e.g.

CREATE TABLE YourStuff
(
 key_col INTEGER NOT NULL UNIQUE, 
 Various_Columns VARCHAR(8) NOT NULL
);

CREATE TABLE YourStuffFlag
(
 key_col INTEGER NOT NULL UNIQUE
    REFERENCES YourStuff (key_col)
);

---

Is [my] table in normal form?

You should by aiming for Fifth normal form (5NF). Whether you have achieved this depends upon the design of Various_Columns. I do not believe that your Flag falls fowl of the requirements for 5NF and I do not see any update, delete or insert anomalies (which is the point of normalization but a 5NF design can still exhibit anomalies). That said, to switch the row that gets the flag attibute, my two-table design requires a single UPDATE statement while your single-table design requires two ;)