I need to validate data in a Table where 1 column has multiple values but 1 value can only be present in 1 row by unique key

user_1500144 0
Viewed 56

Example: A table Keyed by Unique Name and Email Address with a column for Type The Type column can have Original, Work, Personal where you can have multiple work and personal emails but only 1 Original email I am using DB2 for i SQL and I want to constrain the data using UNIQUE or CHECK constraints but not sure how I can do this data set.

Scott scott@hotmail.com Original
Scott scott@gmail.com Personal
Scott scott@live.com Personal
Scott scott@NBC.com Work
Scott scott@ABC.com Work
Scott scott@yahoo.com Original

I want to identify that I cant have yahoo as Original if I already have hotmail as original. the rest are valid.

Let me know if I need to add more.

ibm-midrange db2 validation constraints sql
1 Answers

If you have Db2 for IBM i, then you may create a UNIQUE INDEX with the corresponding WHERE clause.

CREATE TABLE TEST_IND_EXPR 
(
  NAME VARCHAR (20) NOT NULL
, EMAIL VARCHAR (20) NOT NULL
, TYPE VARCHAR (20) NOT NULL
);

CREATE UNIQUE INDEX TEST_IND_EXPR1 ON TEST_IND_EXPR (NAME, EMAIL);
CREATE UNIQUE INDEX TEST_IND_EXPR2 ON TEST_IND_EXPR (NAME, TYPE) WHERE TYPE = 'Original';

INSERT INTO TEST_IND_EXPR VALUES ('Scott', 'scott@hotmail.com', 'Original');
INSERT INTO TEST_IND_EXPR VALUES ('Scott', 'scott@gmail.com', 'Personal');
INSERT INTO TEST_IND_EXPR VALUES ('Scott', 'scott@live.com', 'Personal');
INSERT INTO TEST_IND_EXPR VALUES ('Scott', 'scott@yahoo.com', 'Original');

The last statement returns SQL0803 as this row violates uniqueness of the TEST_IND_EXPR2 index.

user_104182640
Related