4 Replies Latest reply: Apr 30, 2012 5:07 PM by 275191 RSS

    Unique Contraint

      Added a constraint on Purchase Order example as:

      ALTER TABLE purchase_order
      ADD CONSTRAINT reference_is_unique

      Unfortunately it is not enforced, any possible reason why? Does it have to be a virtual column?
        • 1. Re: Unique Contraint
          any possible reason why?
          There could be many. Most common cause being ... there are entries that are not unique.
          select count(*) from <table>;
          select count(*) from (select distinct <unique col1>[[,<unique col2>]...<unique colN>] from <table>);
          If the counts differ, then yep can't put a unique constraint. But nulls don't "count", they should be a non-issue.
          • 2. Re: Unique Contraint
            I was able to load 3 identical copies of the same file (with different names of cause) with Reference as UK, no complains. Why?
            • 3. Re: Unique Contraint
              Can you provide what example you are referring? I ask because
              Connected to Oracle Database 11g Enterprise Edition Release 
              SQL> create table purchase_order(seq number(4), xml_col xmltype);
              Table created
              SQL> ALTER TABLE purchase_order
                2  ADD CONSTRAINT reference_is_unique
                3  UNIQUE (xml_col);
              ALTER TABLE purchase_order
              ADD CONSTRAINT reference_is_unique
              UNIQUE (xml_col)
              ORA-02329: column of datatype Opaque Type cannot be unique or a primary key
              so a test case sample that reproduces what you are seeing would be good.
              • 4. Re: Unique Contraint
                Thanks for looking into it, I did not define names properly.