Check Constraint on Multiple Columns

CHECK CONSTRAINT on multiple columns

Yes, define the CHECK CONSTRAINT at the table level

CREATE TABLE foo (
   bar int NOT NULL, 
   fred varchar(50) NOT NULL,

   CONSTRAINT CK_foo_stuff CHECK (bar = 1 AND fred ='fish')
)

You are declaring it inline as a column constraint

...
fred varchar(50) NOT NULL CONSTRAINT CK_foo_fred CHECK (...)
...

Edit, easier to post than describe. Fixed your commas.

CREATE TABLE dbo.Test 
(   
  EffectiveStartDate  dateTime2(2)        NOT NULL,
  EffectiveEndDate    dateTime2(2)        NOT NULL,  --need comma
  CONSTRAINT CK_CmsSponsoredContents_EffectiveEndDate CHECK (EffectiveEndDate > EffectiveStartDate) --no comma
);

Of course, the question remains are you using a CHECK constraint where it should be an FK constraint...?

How to add CHECK constraints using multiple columns in PostgreSQL

Welcome to SO. You're naming your constraint group_collection or user_collection, which isn't a valid label :)

The ADD CONSTRAINT syntax for a table CREATE TABLE t (x INT, y INT); should be

    ALTER TABLE t ADD CONSTRAINT any_label CHECK (x IS NULL OR y IS NULL)

This should work in your case:

alter table collections
add constraint mycheck check (group_coll_id is null or user_coll_id is null)

Demo: db<>fiddle

check constraint that references multiple columns in the same table

Your SQL statement is valid.

However, your logic has an error: this check does not apply only if eip_linestatus = 'RELEASED'.

As written, your constraint is asserting that all rows must have eip_linestatus = 'RELEASED' AND eip_endate is not null AND eip_startdate is not null.

So, if any rows in your table have eip_linestatus with a value of anything other than RELEASED, you'll get the SQL0544N error when you try to add the constraint.

To create the constraint you're looking for, you need to handle the other state(s) for eip_linestatus. I can't guess what they are, so here's a potential generic option:

alter table pluspgbtrans 
    add constraint start_end_notnull check (
        (eip_linestatus <> 'RELEASED') 
        OR
        (
             eip_linestatus = 'RELEASED' 
             AND eip_endate is not null 
             AND eip_startdate is not null
        )
    );

Check constraint with multiple columns

You have to write it like this:

ALTER TABLE DEPENDANT 
    ADD CONSTRAINT CK_RELATEDF 
        CHECK (DEP_GENDER = 'F' AND RELATED_HOW IN ('Daughter', 'Spouse'));

Edit: you have to do it in one check:

ALTER TABLE DEPENDANT ADD CONSTRAINT CK_RELATEDF
  CHECK ((DEP_GENDER = 'F' AND RELATED_HOW IN ('Daughter', 'Spouse'))
    OR (DEP_GENDER = 'M' AND RELATED_HOW IN ('Son', 'Spouse')));

mysql CHECK constraint on multiple columns and values

Wish you had a fiddle, try the constrain below on the create or alter table.

Constraint should be:
UNIQUE (Y, (CASE WHEN X = 'idVal' THEN 'idVal' END))

Here is a Fiddle

SQL check constraint with multiple conditions

You can write the constraints as:

ALTER TABLE timesheets
  ADD constraint just_user__or__location_or_customer_with_user__or__just_task check (
      (
        user_id is not null
        and task_schedule_id is null
        and (
          (location_id is null and customer_id is null)
          and (location_id is not null or customer_id is not null)
        )
      ) or (
        (location_id is not null or customer_id is not null)
        and not (location_id is not null and customer_id is not null)
        and user_id is not null
      ) or (
        task_schedule_id is not null
        and user_id is null
        and location_id is null
        and customer_id is null
      )
    );

sequelize check constraint on multiple columns

Something like this:

where: {
  [Op.ne]: {
    isA: false,
    isB: false
  }
}

---

Related Topics