SQL Database Limitations

Question

I need to have a table in T-SQL that 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 neither a row nor more than one row can have a Flag column with a value of T. My developer claims that this can be achieved by using the check constraint placed in the table.

Questions:

• Whether such a restriction can be placed in the database itself (i.e., the restriction between rows) at the database level, and not in business rules for updating or inserting rows.
• Is such a table normal?
• Or for the normal form, you need to remove the “Flag” column, but instead (say) there was another simple table or variable containing the value of the row that had the flag = T, that is, in the above row row = 3.

Answer 1

1 № . .

:

• ( )
• Flag = T (SQL Server 2000 +)
• (SQL Server 2008)

2

3 . , . , FK ID Flag

Answer 2

, .

SQL Server ** CHECK ( SQL-92; SQL Server SQL-92).

SQL Server, CHECK UNIQUE, . :

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)
       )
);

, Flag_key "". + CHECK , :

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)
);

---

** SQL Server CHECK , (UDF), .

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())
);

, UDF . , UDF ( SQL , ). , - ! -- . . CHECK Constraints David Portas.

---

Flag, , .

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)
);

---

[] ?

, (5NF). , Various_Columns. , Flag 5NF, , ( , 5NF ). , , Flag, UPDATE, ;)