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

    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 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. #2
    Join Date
    Sep 2013
    Posts
    20

    Question

    The decision:

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

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Oracle already has a table that contains exactly one row: DUAL.

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

    Code:
    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:

    Code:
    create view singleton (some_value, another_value)
    as
    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: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

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
  •