CODEWITHSUNDEEP
sqloracle-databaseconstraints
touchchandra
touchchandra

Reputation: 1556

How to allow oracle table column to have multiple rows in column but sames values only. Using Constraints

How to allow oracle table column to have multiple rows in column but same value only.

    Create Table test ( col int);


    case - I
    insert into test values (1);
    --should work
    insert into test values (1);
    --should work
    insert into test values (2);
    --Should throw error !!!!

    case - II
    truncate table test;
    insert into test values (2);
    --should work
    insert into test values (2);
    --Should work
    insert into test values (1);
    --Should throw error !!!!!!

Upvotes: 1

Views: 110

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

UPDATE

Based on OP's two cases, I think a BEFORE INSERT TRIGGER would do the job:

Test case:

SQL> DROP TABLE TEST PURGE;

Table dropped.

SQL>
SQL> CREATE TABLE test
  2    ( col INT
  3    );

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER trg BEFORE
  2    INSERT ON TEST FOR EACH ROW DECLARE ID NUMBER;
  3    BEGIN
  4      BEGIN
  5        SELECT DISTINCT col INTO ID FROM TEST;
  6      EXCEPTION
  7      WHEN no_data_found THEN
  8        NULL;
  9      END;
 10      IF :NEW.col <> id THEN
 11        RAISE_APPLICATION_ERROR(-20001, 'Cannot Insert different value in the table');
 12      END IF;
 13    END;
 14  /

Trigger created.

SQL>

The NO_DATA_FOUND exception is to ignore the first insert, since there would be no rows before that.

Now, let's test the INSERT statements:

CASE 1

SQL> INSERT INTO TEST VALUES (1);

1 row created.

SQL>
SQL> INSERT INTO TEST VALUES (1);

1 row created.

SQL>
SQL> INSERT INTO TEST VALUES (2);
INSERT INTO TEST VALUES (2)
            *
ERROR at line 1:
ORA-20001: Cannot Insert different value in the table
ORA-06512: at "LALIT.TRG", line 10
ORA-04088: error during execution of trigger 'LALIT.TRG'


SQL>

CASE 2

SQL> TRUNCATE TABLE TEST;

Table truncated.

SQL>
SQL> INSERT INTO TEST VALUES (2);

1 row created.

SQL>
SQL> INSERT INTO TEST VALUES (2);

1 row created.

SQL>
SQL> INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (1)
            *
ERROR at line 1:
ORA-20001: Cannot Insert different value in the table
ORA-06512: at "LALIT.TRG", line 10
ORA-04088: error during execution of trigger 'LALIT.TRG'


SQL>

Upvotes: 0

Pravin Satav
Pravin Satav

Reputation: 702

If I have understood your requirement correctly, you can use before insert trigger. Check the value present in table, if new value matches with table value or table is having no data then only allow insert.

Here's the link about trigger http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

Upvotes: 1

Related Questions