2 Replies Latest reply: Feb 21, 2013 3:36 AM by David Last-Oracle RSS

    Issue with check constraints with parenthesis in the in clause

    Justin B-Oracle
      Hi,

      We have the below check constraint in one of our database tables but it doesn't import properly.

      CREATE TABLE "PAREN_TEST_TABLE"
      (     "OPEN_PARENTHESIS" VARCHAR2(20 BYTE)
      )
      ;
      --------------------------------------------------------
      -- Constraints for Table PAREN_TEST_TABLE
      --------------------------------------------------------
      ALTER TABLE "PAREN_TEST_TABLE" ADD CONSTRAINT "CHK_OPEN_PAREN" CHECK (open_parenthesis in ('(', NULL)) ENABLE;

      I have done File -> Import -> DDL File and chose my file. Import says it successfully read 2 statements.
      All Statements:           2
      Imported Statements:      2

      When going through the compare models, no Rule shows up for the check constraint and there isn't one present after I hit merge in the data model.

      I have also tried this through import via the data dictionary but it also doesn't pick up the check constraint in the compare models.
      It does show up in the ddl preview though
      -- Generated by Oracle SQL Developer Data Modeler 3.3.0.734
      -- at: 2013-02-05 12:52:08 CST
      -- site: Oracle Database 11g
      -- type: Oracle Database 11g

      CREATE TABLE PAREN_TEST_TABLE
      (
      OPEN_PARENTHESIS VARCHAR2 (20 BYTE)
      )
      ;

      ALTER TABLE PAREN_TEST_TABLE
      ADD CONSTRAINT CHK_OPEN_PAREN
      CHECK ( OPEN_PARENTHESIS IN ('(', 'NULL'))
      ;

      -- Oracle SQL Developer Data Modeler Summary Report:
      --
      -- CREATE TABLE 1
      -- CREATE INDEX 0
      -- CREATE VIEW 0
      -- ALTER TABLE 1
        • 1. Re: Issue with check constraints with parenthesis in the in clause
          David Last-Oracle
          Hi Justin,

          Thanks for reporting this problem. I have logged a bug on it.
          I have also tried this through import via the data dictionary but it also doesn't pick up the check constraint in the compare models.
          This is probably because it is treating the Table in the database as a different Table than the one in your model (because the one in the database has a user/schema associated with it).

          When the Compare Models panel is displayed during the import from database, you can go to the "Compare Options" tab and unset the 'Use "Schema" Property' option and then select the "Refresh Trees" button.
          The database Table should then be treated as the same Table as the one in your model, and the Check Constraint values should show up as a difference in the 'LOV Constraint' property for the Column (on the Details tab).
          Merge should then import these constraint values into your model.

          David
          • 2. Re: Issue with check constraints with parenthesis in the in clause
            David Last-Oracle
            Hi Justin,

            The problem with importing the DDL file containing
            ALTER TABLE "PAREN_TEST_TABLE" ADD CONSTRAINT "CHK_OPEN_PAREN" CHECK (open_parenthesis in ('(', NULL)) ENABLE;
            should be fixed in Data Modeler version 3.3 EA2 which is now available on OTN.

            David