Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2014
    Posts
    5

    Unanswered: Using non-unique indexes to enforce primary key and unique key constraints?

    I've got a ton of experiene with SQL Server, but the context of my career has afforded an opportunity (or requirement? LOL!) to learn Oracle. I've purchased a few books to get started.

    One of those books is Oracle Database 1c Administrator Certified Associate Study Guide. I'm not took keen on certifications, but the study books generally provide a structured way for me to evaluate what I know and what I don't, and direct my efforts. I'm trying to pick up on what's different and what's not.

    The book offers a practice exam for the "SQL Fundamentals" certification. One of the offered questions (on page xxxviii, question 12) has me baffled:

    Which of the following statements are true? (Choose two.)

    A) Primary key constrians allow NULL values in the columns.
    B) Unique key constrains allow NULL values in the columns.
    C) Primary key constraints do not allow NULL values in the columns.
    D) A non-unique index cannot be used to enforce primary key constraints.
    I think that A) isn't true because a primary key column can't be NULL, even in a multi-column primary key. Therefore, I think C) is true. Sure enough, the Oralce documentation explains that a primary key constraint is a combination of a Unique constraint and a not-null constraint.

    I'm a bit confused by choice B) because I don't know what a "unique key constraint" is. I can find Oracle documentation on "unique constraint", but not on a "unique key constraint". That same documentation page seems to imply that multiple NULL values are allowed under a unique constraint, so it appears that choice B is true, as well.

    The last choice D, is nebulous. A non-unique index can't enforce a primary key constraint itself because a non-unique index alone will allow duplicate values, and will also allow NULL values. Thing is, one could implement a PK constraint in a variety of ways, some of them not even involving an index. A trigger, for example, that checks changes to see if they violate a constraint is one approach. I guess that the choice is just at face value; a non-unique index doesn't directly help implement a primary key constraint.

    Picking B and C, then, I went to the answer key. On page lvi, choices B and C are identified a correct answers. However, the answer key explains:

    Primary key and unique key constraints can be enforced using non-unique indexes.
    What?! Now I'm completely baffled. If that statement is true, then D is a viable answer for this question. How can primary key and unique key constraints be enforced using non-unique indexes?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You need to distinguish the "concept" of a primary key and the physical implementation of it.

    The following will work:
    Code:
    create table foo 
    (
      id integer -- note: nullable!
    );
    
    create index idx_foo on foo (id); -- note: not uniqe!
    
    alter table foo
      add constraint pk_foo primary key (id) 
      using index idx_foo;
    Just because the index is non-unique does not mean you can insert more than one value for the id column. This is prevented because of the primary key constraint. The primary key also prevents inserting NULL values into the column.

    The index is just a technical implementation detail. Theoretically you can enforce a primary key without any index, it would just be very expensive because the database would have to scan the complete table on each insert or update. The index only makes this faster that's all.
    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

  3. #3
    Join Date
    Nov 2014
    Posts
    5
    Quote Originally Posted by shammat View Post
    The index is just a technical implementation detail. Theoretically you can enforce a primary key without any index, it would just be very expensive because the database would have to scan the complete table on each insert or update. The index only makes this faster that's all.
    Indeed, I'm well-aware the physical implementation doesn't necessarily match the logical implementation. I point out that constraints could be implemented lots of ways -- triggers that do their own probing was one specific example I mentioned.

    I'll read up on the "using index" clause in the constraint declaration syntax. Given that your example shows a mechanism for using a non-unique index to implement a primary key, isn't the answer given in the book incorrect? Why isn't D, then, an acceptable choice for the question?

    Thanks!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by mikeblas View Post
    However, the answer key explains:

    Primary key and unique key constraints can be enforced using non-unique indexes.

    What?! Now I'm completely baffled.
    Well, the explanation is not wrong, it is just incomplete. They can be enforced using non-unique indexes, but primary key constraint - additionally - requires NOT NULL to be applied on primary key columns.

    It seems that the author didn't pay enough attention to it, proof-readers didn't notice it either. Not everything you find in a book is 100% correct.

    Because, reading your question, I also chose B and C to be correct answers. I believe that you got the idea right and your explanations sound reasonable to me.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by mikeblas View Post
    Why isn't D, then, an acceptable choice for the question?
    D) says: "A non-unique index can not be used to enforce primary key constraints"

    And because you can use a non-unique index to enforce a PK, answer D is wrong.
    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

  6. #6
    Join Date
    Nov 2014
    Posts
    5
    Augh! You're right -- how embarrassing. Thanks, Shamat!

Posting Permissions

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