How to add a complex constraint of 2 conditions-CodePudding

I have a table in SQL Server like this:

CREATE TABLE my_tablel
(
    main_id UNIQUEIDENTIFIER,
    name_id CHAR(13) NOT NULL,
    my_flag BIT,
    active BIT NOT NULL,

    CONSTRAINT my_table_pk PRIMARY KEY (main_id)
)

I would like to add an extra constraint line on 'main flag' satisfying the following conditions:

There can only be one record with main_flag set to true for each name_id.
main_flag can only be set to true if active is also true

Can anybody help me?

CodePudding user response：

This will need to be served by 2 different objects, a filtered UNIQUE INDEX for the first, and a CHECK CONSTRAINT for the second.

For the first, you can create a filtered UNIQUE INDEX like this, which will mean that only name_id must be unique for rows where my_flag (I assume this is what you mean by "main_flag") is 1 (not "true", bit is not a boolean):

CREATE UNIQUE INDEX UQ_name_id_my_flag_equal_1 ON dbo.my_tablel (name_id)
WHERE my_flag = 1;

For the latter you can use a CHECK CONSTRAINT to check that the value of my_flag and active are both 1, or my_flag is 0 or NULL.

ALTER TABLE dbo.my_tablel ADD CONSTRAINT CK_MyFlag_Active
CHECK ((my_flag = 1 AND active = 1) OR my_flag = 0 OR my_flag IS NULL);

db<>fiddle