Sql-server – Creating a constraint

constraintsql serverssrsunique-constraint

I created a report that records entries
from a table which has 4 columns

1 Shipper ID 
2 SHipper Reference
3 loading
4 Completion

My primary keys are columns 1 and 2 there are no foriegn keys, columns 3 and 4 are mandatory fileds
with a yes or no value constraint

what I want is to create a constraint that doesnt allow the user to pick these two fields (completeion and loading) as N in the same time but they can be both Y at the same time

what is the code for that constraint?

this code was my best shot

ALTER TABLE Table_Name
ADD CONSTRAINT Double_constraint check (Completion!='N' and Loading!='N')

but this one wont allow anything but completion to be Y and Loading to be N

Best Answer

You'd probably be better off making this a bit field with NOT NULL or otherwise you also need to validate that only the strings 'Y' and 'N' were entered.

With the bit column arrangement the check constraint would be

CHECK (1 IN (Completion, Loading))

Otherwise with char(1) the check constraint could be

CHECK ('Y' IN (Completion, Loading))

but you'd need two additional column level constraints ensuring Completion IN ('N','Y') and similar for Loading.

Related Solutions

Oracle: DB partial constraint

You can create a function-based index. If the table is named TRANSACTION

CREATE UNIQUE INDEX only_one_purchase
    ON transaction( (case when transaction_type = 'PURCHASE'
                          then order_id
                          else null
                      end) );

This leverages the fact that Oracle doesn't store completely NULL rows in the index so only the order_id values that are part of PURCHASE transactions will be stored in the index.

Mysql – Creating a partial unique constraint for MySQL

The only option that comes to mind is a BEFORE INSERT trigger. In 5.5 you could use SIGNAL:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SIGNAL SQLSTATE '45000'
     SET MESSAGE_TEXT = 'Cannot insert this item due to unique constraint', 
         MYSQL_ERRNO = 1001;
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

But pre 5.5, you would do this something like this:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SET NEW='Error: Cannot insert this item due to unique constraint';
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

Best Answer

Related Solutions

Oracle: DB *partial* constraint

Mysql – Creating a partial unique constraint for MySQL

Related Question

Oracle: DB partial constraint