4 Replies Latest reply: Jul 3, 2014 10:00 AM by SomeoneElse RSS

    Add false constraint to "lock" a table

    Brian Tkatch

      I apologize if this is simple or silly to anyone. While testing an idea to learn what can and cannot be done, i noticed this and thought to show and share.

       

      Conflicting constraints can be used: CREATE TABLE A(A INT, CHECK(A = 1), CHECK(A = 2));

      No reason for multiple constraints or even a column reference: CREATE TABLE A(A INT, CHECK(1 = 2));

       

      What's the use of this? When a TABLE, for whatever reason, needs to be "locked" so no new records can be added, just add it with NOVALIDATE:

       

      CREATE TABLE A(A INT);
      
      INSERT INTO A VALUES(1);
      INSERT INTO A VALUES(1);
      INSERT INTO A VALUES(2);
      
      ALTER TABLE A ADD CHECK(1 = 2) NOVALIDATE;
      
      INSERT INTO A VALUES(3);
      
      
      
      
      

       

      Last line results in ORA-2290 check constraint (...) violated

        • 1. Re: Add false constraint to "lock" a table
          Brian Bontrager

          alter table A read only;

           

          Doing it this way sets DBA_TABLES.READ_ONLY to YES so you can quickly see which tables are read only.  The constraint doesn't provide that detail, so you have to remember and your replacement when you move to another position has to guess or find out the hard way.

          It also throws a more useful error message that indicates you are writing to something read only, instead of a constraint violation.

          • 2. Re: Add false constraint to "lock" a table
            Brian Tkatch

            Ah, option in Oracle 11. Didn't know about that.

             

            I still find it amusing that conflicting or nonsensical constraints can be added. Now i'll just have to find a use for it.

            • 3. Re: Add false constraint to "lock" a table
              SomeoneElse

              If you need to prevent a table from being changed you can also revoke the necessary privs.

              • 4. Re: Add false constraint to "lock" a table
                Frank Kulash

                Hi,

                 

                 

                Brian Tkatch wrote:

                 

                Ah, option in Oracle 11. Didn't know about that.

                 

                I still find it amusing that conflicting or nonsensical constraints can be added. Now i'll just have to find a use for it.

                I agree, it is kind of amusing, in the same sort of way that having (or not having) a word for "No" in a a natural language is amusing.  My native language happens to have a word for "No", so I'm amused at how other languages phrase things differently.  I'm sure that if my native language didn't have a word for "No" (or where it wasn't used in everyday speech), I'd be amused at how speakers of languages other than mine thought they needed one, or how often they used it.

                 

                SQL doesn't have BOOLEAN data types, so there's no way to say (for example) "CHECK (FALSE)".  The way you've gotten around that is to put a condition that is obviously false in place of the boolean literal. It's rather qurky and amusing that SQL works that way.