CODEWITHSUNDEEP
sqldatabaseschema
Immad Amir
Immad Amir

Reputation: 1

SQL Database Systems

I have three Boolean Attributes in my Relation and I want only one of them to have true value or the table should give an error. How Can I do that?

Upvotes: 0

Views: 38

Answers (2)

user330315
user330315

Reputation:

You can use a check constraint for that. 

create table some_table
(
  flag1 boolean not null,
  flag2 boolean not null,
  flag3 boolean not null,
  constraint only_one_true 
      check ( (flag1,flag2,flag3) IN ( (true, false, false), 
                                       (false, true, false),
                                       (false, false, true)) ) 
);

The above is standard SQL.

Some DBMS system also allow casting a boolean to a number representing 0 or 1 in that case you can just add them and the sum must be equal to 1 (ensuring that exactly one flag is set to true)

create table some_table
(
  flag1 boolean not null,
  flag2 boolean not null,
  flag3 boolean not null,
  constraint only_one_true 
      check ( cast(flag1 as integer) + 
              cast(flag2 as integer) + 
              cast(flag3 as integer) = 1 )
);

Upvotes: 0

sticky bit
sticky bit

Reputation: 37507

You can use a check constraint, if your DBMS supports them (most of them do). In it you check, that exactly one of the flags is true. For that you can use a Boolean expression.

CREATE TABLE elbat
             (...
              CHECK (flag1 = true
                     AND flag2 = false
                     AND flag3 = false
                      OR flag1 = false
                         AND flag2 = true
                         AND flag3 = false
                       OR flag1 = false
                          AND flag2 = false
                          AND flag3 = true));

(Just to get the idea, syntax may vary from DBMS to DBMS.)

Upvotes: 2

Related Questions