CODEWITHSUNDEEP
sql-server-2008triggersconstraints
Kelvin
Kelvin

Reputation: 225

Constraints or Trigger

I am in a really hot spot here,

I have two tables

  1. MonthlyPayment (EmpID, PaymentType, swiftcode)
  2. EmpAssignment (EmpID, status)

EmpAssignment.Status can only be 1 or 2

The question is:

I would like a constraint or trigger, such that on update of MonthlyPayment, if PaymentType = 1 then Swiftcode should be unique and mandatory only for those whose status = 1.

If PaymentType <> 1, swiftcode should not be mandatory but unique Also, the constraint should not affect those whose status = 2

Thanks in advance.

Upvotes: 1

Views: 68

Answers (1)

wildplasser
wildplasser

Reputation: 44250

Using domains, the constraint would become a domain constraint:

DROP DOMAIN PAYTYPE CASCADE;
CREATE DOMAIN PAYTYPE AS INTEGER
    CHECK (value >= 1 AND value <= 666)
    ;

DROP DOMAIN ESTATUS CASCADE;
CREATE DOMAIN ESTATUS AS INTEGER
    CHECK (value >= 1 AND value <= 2)
    ;

DROP TABLE MonthlyPayment CASCADE;
CREATE TABLE MonthlyPayment
    ( EmpID INTEGER NOT NULL PRIMARY KEY
    , PaymentType PAYTYPE NOT NULL
    , swiftcode varchar
    );

DROP TABLE EmpAssignment CASCADE;
CREATE TABLE EmpAssignment
    ( EmpID INTEGER NOT NULL PRIMARY KEY
    , status ESTATUS NOT NULL
    );

Now test it:

 INSERT INTO EmpAssignment(EmpID,status) VALUES(1,9);

Result (using Postgresql-9.1):

ERROR:  value for domain estatus violates check constraint "estatus_check"

Upvotes: 1

Related Questions