1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

table should allow to insert only one row

Discussion in 'SQL PL/SQL' started by ecivgamer, Aug 16, 2014.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,


    my need is to create table that allows to insert only one row.

    How do I perform it?


    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    "CORE 11.2.0.1.0 Production"
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
     
  2. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    The decision:

    CREATE UNIQUE INDEX table_x_u ON table_x (NVL2 (col_1, 1, 1));
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No. That only ensures each KEY is unique. You can still insert as many rows as you want. What needs to be done is create a materialized view against the table and put a check constraint on the materialized view:


    Code (SQL):

    SQL> CREATE TABLE one_row(
      2  id NUMBER,
      3  stuff      varchar2(40));


    TABLE created.


    SQL>

    SQL> ALTER TABLE one_row
      2  ADD CONSTRAINT onerow_pk PRIMARY KEY(id);


    TABLE altered.


    SQL>

    SQL> INSERT INTO one_row
      2  VALUES(1,'When in the course of human events');


    1 ROW created.
    SQL>

    SQL> commit;


    Commit complete.


    SQL>

    SQL> CREATE materialized VIEW log ON one_row;


    Materialized VIEW log created.


    SQL>
    SQL> CREATE materialized VIEW onerow_vw
      2  build immediate
      3  refresh complete ON commit AS
      4  SELECT COUNT(*) rowct
      5  FROM one_row;


    Materialized VIEW created.


    SQL>
    SQL> SELECT * FROM one_row;


            ID STUFF
    ---------- ----------------------------------------
             1 WHEN IN the course OF human events


    SQL>
    SQL> ALTER TABLE onerow_vw
      2  ADD CONSTRAINT row_count_chk
      3  CHECK(rowct <= 1)
      4  deferrable;


    TABLE altered.


    SQL>
    SQL> SELECT * FROM one_row;


            ID STUFF
    ---------- ----------------------------------------
             1 WHEN IN the course OF human events


    SQL>
    SQL> INSERT INTO one_row
      2  VALUES(2,'When in the course of human events');


    1 ROW created.


    SQL>
    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-12008: error IN materialized VIEW refresh path
    ORA-02290: CHECK CONSTRAINT (BINGNORFER.ROW_COUNT_CHK) violated

    SQL>
    SQL> SELECT * FROM one_row;


            ID STUFF
    ---------- ----------------------------------------
             1 WHEN IN the course OF human events


    SQL>
     

    Notice how only one row exists in the table.