Skip to content
Advertisement

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?

Advertisement

Answer

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);
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement