Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013

    Question Unanswered: table should allow to insert only one row

    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 - 64bit Production
    PL/SQL Release - Production
    "CORE Production"
    TNS for Linux: Version - Production
    NLSRTL Version - Production

  2. #2
    Join Date
    Sep 2013


    The decision:

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

  3. #3
    Join Date
    Nov 2003
    Provided Answers: 23
    Oracle already has a table that contains exactly one row: DUAL.

    But if you do insist to duplicate Oracle functionality there are several ways:

    create table singleton
       id integer not null primary key check (id = 1)
    insert into singleton values (1);
    If you also want to prevent deleting that row, you will need to write a trigger that throws an exception. Another option is to create view instead with only a single value:

    create view singleton (some_value, another_value)
    select 42, 'foo' from dual;
    With the view you can be certain that the row will never be deleted or a new row will be inserted.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts