4

I have a table named Table1 with three columns col1, col2, col3. col1 can have one of the three values(1 or 2 or 3).

I need to add a check constraint such that it checks, if col1 has value 1 then the values of col2 and col3 should be same else if col1 has values 2 or 3, then col2 and col3 values may or may not be same.

Can anyone tell me how to add the constraint for this ?

Thanks in advance.

Indhumathi
  • 41
  • 1
  • 5

3 Answers3

4

You can add a check constraint like this:

ALTER TABLE Table1 ADD CONSTRAINT chk_table1_cols
    CHECK ( (col1 = 1 AND col2 = col3) OR (col1 IN (2, 3)) );

Note that "is the same" presumes that the values are not NULL. That logic can be added, if you want to consider NULL values as equal.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You can express these condition with a series of logical operators:

(col IN (1, 2, 3)) AND (col2 = col3 OR col1 IN (2, 3)
Mureinik
  • 297,002
  • 52
  • 306
  • 350
0

I didn't verify (I don't have MySql) but in Oracle the following works:

ALTER TABLE Table1 ADD
(
  CONSTRAINT CHK_1 CHECK ((col1 BETWEEN 1 AND 3) AND (col1 <> 1 OR col2=col3))
)
Robert Kock
  • 5,795
  • 1
  • 12
  • 20
  • I can tell you this does not work in MySQL, instead off this you can use a view with check option in MySQL which can do the [same](https://dev.mysql.com/doc/refman/5.6/en/view-check-option.html) – Raymond Nijland Feb 15 '19 at 10:26
  • @RaymondNijland you're probably right (I'm not really familiar with mysql) but https://www.w3schools.com/sql/sql_check.asp says it should work. – Robert Kock Feb 15 '19 at 11:43
  • Wait... MySQL 8.0.16 (Not yet released, General Availability) seams be to going to support it [finally](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html) .. For general cases i won't not advice to use w3schools for SQL documentation. – Raymond Nijland Feb 15 '19 at 11:46