CODEWITHSUNDEEP
sqldb2ddlcheck-constraints
Monika
Monika

Reputation: 301

Avoiding multiple "OR's" in a SQL query

I have the following working SQL query that adds a constraint MindestensEinKontakt_CHECK to the table KundenKontaktDaten. The constrainst ensures that at least one of the attributes Twitter_Id, Google_Id, Facebook_Id, Skype_Id, and Telefonnummer is not null: 

ALTER TABLE KundenKontaktDaten 
    ADD CONSTRAINT MindestensEinKontakt_CHECK 
        CHECK (Twitter_Id IS NOT NULL OR Google_Id IS NOT NULL OR 
               Facebook_Id IS NOT NULL OR Skype_Id IS NOT NULL OR 
               Telefonnummer IS NOT NULL);

I want to avoid the multiple "OR's" and write the query in a more compact way. Is anyone aware of a way to do this?

Upvotes: 1

Views: 109

Answers (1)

Mureinik
Mureinik

Reputation: 311188

coalesce returns the first non-null argument, or null if they are all nulls. You could utilize it in your alter table statement:

ALTER TABLE KundenKontaktDaten
ADD CONSTRAINT MindestensEinKontakt_CHECK 
CHECK (COALESCE(Twitter_Id, Google_Id, Facebook_Id, Skype_Id, Telefonnummer) IS NOT NULL);

Upvotes: 4

Related Questions