Nikhil Nikhil - 5 months ago 14
SQL Question

Oracle sql query with complex constraint

Table named T1 with following values

Col1 Col2 Col3
Rs1 S S2
Rs2 SX S3
Rs3 S S2


From a csv, I need to insert some values into the table, having values Rs4, SX and S3 respectively to each column.

I need to apply a check with following constraints.


  • One S3 can belong to only one SX, but S3 and SX as pair can belong can belong to multiple columns1's values.



What will be the oracle query for this? And if the above condition is true then I need to run an insertion query which is prepared. How can it validated?

PS: we can't create another table.

Answer

Finally, I'm able to resolve the question with a some select queries and few if conditions being applied. I have done this in a stored procedure.

  SELECT count(col3) 
  INTO V_exist_value
  FROM T3 
  WHERE col3's value = Variable_col3
  AND col1's value <> Variable_col1 
  AND col2's value = Variable_col2;

  IF (V_exist_value >= 1) THEN     
      INSERT INTO T3 (col1, col2, col3)
      VALUES (Variable_col1, Variable_col2, Variable_col3);
  ELSE 
      SELECT count(col3) 
      INTO V_exist_value1 
      FROM T3 
      WHERE col3's value = Variable_col3;

      IF (V_exist_value1 = 0) THEN
        INSERT INTO T3 (col1, col2, col3)
      VALUES (Variable_col1, Variable_col2, Variable_col3);
  ELSE 
        RAISE Exception_col3_value_exists;
      END IF;

  END IF;