How can I disable entering the same values in two fields at the same time on a table in Oracle?

user_13628686 0
Viewed 131

Let´s say I create this table:

CREATE TABLE MYTABLE (
    id INT NOT NULL AUTO_INCREMENT,
    Field1 VARCHAR(30),
    Field2 NUMBER(10),
);

Then I will insert this values:

INSERT INTO MYTABLE VALUES(null, 'Value', 10);

What I want is to be able to do both of these inserts:

INSERT INTO MYTABLE VALUES(null, 'Value', 5);
/* This works as there isn´t a row with both Field1='Value' and Field2=5 at the same time */

INSERT INTO MYTABLE VALUES(null, 'Something', 10);
/* This works as there isn´t a row with both Field1='Something' and Field2=10 at the same time */

But I don´t want to be able to do this (repeat both the Field1 and Field2 values together):

INSERT INTO MYTABLE VALUES(null, 'Value', 10);
/* This doesn´t work as there is a row with both Field1='Value' and Field2=10 at the same time */

How can I achieve this behaviour in Oracle? I thought about using ASSERTIONS but they are not yet implemented in Oracle.

oracle sql
4 Answers

I don´t want to be able to do this (repeat both the Field1 and Field2 values together)

You can use a COMPOUND trigger:

CREATE OR REPLACE TRIGGER mytable__not_repeat_f1_and_f2
FOR UPDATE OR INSERT ON MyTable
COMPOUND TRIGGER
  TYPE MyTable_Fields_Type IS RECORD(
    rid     ROWID,
    field1  MyTable.Field1%TYPE,
    field2  MyTable.Field2%TYPE
  );

  TYPE MyTable_Fields_Table_Type IS TABLE OF MyTable_Fields_Type;

  fields MyTable_Fields_Table_Type := MyTable_Fields_Table_Type();
AFTER EACH ROW IS    
  BEGIN
    fields.EXTEND;
    fields(fields.COUNT) := MyTable_Fields_Type(
      :NEW.ROWID,
      :NEW.Field1,
      :NEW.Field2
    );
  END AFTER EACH ROW;
AFTER STATEMENT IS
    num_field1 PLS_INTEGER;
    num_field2 PLS_INTEGER;
  BEGIN
    FOR i IN 1 .. fields.COUNT LOOP
      SELECT COUNT( CASE WHEN Field1 = fields(i).Field1 THEN 1 END ),
             COUNT( CASE WHEN Field2 = fields(i).Field2 THEN 1 END )
      INTO   num_field1,
             num_field2
      FROM   MyTable
      WHERE  ROWID != fields(i).RID;
     
      IF num_field1 > 0 AND num_field2 > 0 THEN
        RAISE_APPLICATION_ERROR( -20000, 'Cannot have duplicate Field1 and Field2' );
      END IF;
    END LOOP;
  END AFTER STATEMENT;    
END;
/

Then, for the table:

CREATE TABLE MYTABLE (
    id     INT
           GENERATED ALWAYS AS IDENTITY
           PRIMARY KEY,
    Field1 VARCHAR2(30),
    Field2 NUMBER(10)
);

If you do:

INSERT INTO MyTable ( Field1, Field2 )
  SELECT 'a', 1 FROM DUAL UNION ALL
  SELECT 'b', 2 FROM DUAL UNION ALL
  SELECT 'c', 3 FROM DUAL;

That works but then trying to do:

INSERT INTO MyTable ( Field1, Field2 ) VALUES ( 'b', 3 );

Would raise the exception:

ORA-20000: Cannot have duplicate Field1 and Field2
ORA-06512: at "SCHEMA_NAME.MYTABLE__NOT_REPEAT_F1_AND_F2", line 33
ORA-04088: error during execution of trigger 'SCHEMA_NAME.MYTABLE__NOT_REPEAT_F1_AND_F2'

But:

INSERT INTO MyTable ( Field1, Field2 ) VALUES ( 'b', 4 );

Would work since this doesn't repeat a Field1 and a Field2 value together.

db<>fiddle here

user_15092640

If you want each column to be unique, you can just use unique constraints:

CREATE TABLE MYTABLE (
    id INT NOT NULL AUTO_INCREMENT,
    Field1 VARCHAR(30) UNIQUE,
    Field2 NUMBER(10) UNIQUE
);
user_11440350

Create a compound unique index containing both columns so that the combination can't be inserted more than once, whether it be at the same time or at different times.

user_61148880

I don't think there's any way to do this reliably. If I understand correctly, you want to reject an inserted row if Field1 and Field2 both already exist in the table, but not necessarily in the same row.

So if your table looks like:

Field1     Field2
------     ------
Value      5
Something  10

I should be able to insert ('Apple', 7), right? Then when i try to insert ('Value', 7), it fails because there are rows with both those values already.

But I could do them in the reverse order: insert ('Value', 7) and then ('Apple', 7)

So what happens if I do this?

WITH mydata AS (
    SELECT 'Apple' AS field1, 7 AS field2 FROM dual UNION ALL
    SELECT 'Value', 7 FROM dual
INSERT INTO mytable
SELECT null, field1, field2 FROM mydata

Does that succeed or fail? There's no way to know. You are not guaranteed that the database actions happen in the order in which you think they're going to happen.

You'll run into the same problem if two sessions try to insert these two rows, then commit.

user_236690
Related