Skip to content
Advertisement

Both columns can’t have nulls at the same time, only one per time, how to define this constraint?

I have a table:

create table tblFeedbacks
(
        Id int not null Primary key identity,
        ClientFeedback bit null,
        BarberFeedback bit null,
        BarberId int not null foreign key references tblBarberInfo(Id),
        ClientId int not null foreign key references tblClients(Id),
        Feedback nvarchar(max) not null
)

If clients feedback than 1 otherwise Barbers feedback 1, how to make a constraint for this situation?

  • ClientFeedback bit null,
  • BarberFeedback bit null,

Sorry for English

Advertisement

Answer

You use a check constraint. For instance, if you want exactly one value to be 1:

create table tblFeedbacks(
        Id int not null Primary key identity,
        ClientFeedback bit null,
        BarberFeedback bit null,
        BarberId int not null foreign key references tblBarberInfo(Id),
        ClientId int not null foreign key references tblClients(Id),
        Feedback nvarchar(max) not null),
        check ( (ClientFeedback = 1 and BarberFeedback = 0) or (ClientFeedback = 0 and BarberFeedback = 1) )
);

I am guessing that might be what you want. But your question is specifically about NULL values. That constraint would be:

        check ( ClientFeedback is not null or BarberFeedback is not null )
3 People found this is helpful
Advertisement