-1

I have a table "Table" that contains an ID, Col1 and Col2, Col3. Col2 can either be 0 or 1. I want Col2 in rows where Col1 has the same value to be the same. Ex

I want something like this

+----+-------+------+-----------+
| ID | Col1  | Col2 |   Col3    |
+----+-------+------+-----------+
|  1 | "One" |    0 | "Yeah"    |
|  2 | "One" |    0 | "Meh"     |
|  3 | "One" |    0 | "Why Not" |
|  4 | "Two" |    1 | "Huh"!    |
+----+-------+------+-----------+

And not 

+----+-------+------+-----------+
| ID | Col1  | Col2 |   Col3    |
+----+-------+------+-----------+
|  1 | "One" |    0 | "Yeah"    |
|  2 | "One" |    0 | "Meh"     |
|  3 | "One" |    1 | "Why Not" | (Normally it must be 0 or line 1 and 2 
|  4 | "Two" |    1 | "Huh"!    | Must be "1" )
+----+-------+------+-----------+
Elmehdi93
  • 70
  • 1
  • 9
  • 1
    Even after looking at your expected output it is not clear what you want. One thing though if you want to have `col2` based on other column values then it is calculated column and it is always best to not store calculated values instead do the calculations when you are fetching the data. – Mahesh Apr 18 '17 at 10:15
  • 1
    mention your input table data and what you want in output – Ashutosh SIngh Apr 18 '17 at 10:20
  • maybe related to this: http://stackoverflow.com/questions/7522026/how-do-i-add-a-check-constratint-to-a-table ? - it is unclear when your condition is supposed to be checked: on input, or do you want to create an update script? – cypherabe Apr 18 '17 at 10:24
  • Ah sorry I didn't make myself clear enough. If the first row to have "One" as a value of Col1 has "0" as a value of Col2, Then all rows that will be added after and that have "One" as value of Col1 MUST have "0" as value of Col2. – Elmehdi93 Apr 18 '17 at 11:11

1 Answers1

0

Even if MySQL supported check constraints, you wouldn't do this with check constraints. I think the best way is using foreign key constraints.

You need a second table with the valid col1/col2 values:

create table Col1Col2 as (
    col1 varchar(255) not null primary key,
    col2 int not null,
    unique (col1, col2)  -- this is not strictly necessary see below
);

Then your table would be:

create table t as (
    id int auto_increment primary key,
    col1 varchar(255) not null,
    col2 int not null
    col3 int,
    constraint fk_t_col1_col2 foreign key (col1, col2) references col1col2(col1, col2)
);

However, I wouldn't even store col2 in t. Instead remove it from t and just look up the value in col1col2.

Your basic problem is that you are not storing the data in a relational format, because this requirement suggests that you have another entity.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What would it be if I'm using Oracle ? – Elmehdi93 Apr 18 '17 at 11:13
  • @Elmehdi93 . . . Pretty much the same thing. The proper way to store data is not closely related to the database you are doing (there are differences, but usually not in the definition of an entity). – Gordon Linoff Apr 18 '17 at 12:10