2 Replies Latest reply: Mar 11, 2013 8:14 PM by 995316 RSS

    IZ047 - Constraints - Guide Error ?

    995316
      Hi,
      There is a simple create query as below in chapter 2 of IZ047 guide
      create table cruises
      (
      Cruiseid Number,
      ...
      ..
      captain_id Number NOT NULL,
      ..
      status varchar2(5) default 'DOCK',
      constraint curuise_pk primary key(cruiseid));
      it states query has two constraints primary key and NON NULL? What about default that is also a constraint right?
        • 1. Re: IZ047 - Constraints - Guide Error ?
          matthew_morris
          What about default that is also a constraint right?
          No. Default values are not a constraint. If you don't care to take my word for it, a test case is below. A constraint is created for the NOT NULL on column COL1, but no constraint is added for the DEFAULT on column COL2.
          CREATE TABLE default_no (
          col1    NUMBER NOT NULL,
          col2    NUMBER DEFAULT 0);
          
          SELECT constraint_name, constraint_type, search_condition
          FROM   user_constraints
          WHERE  table_name = 'DEFAULT_NO';
          
          CONSTRAINT_NAME      CONSTRAINT_TYPE SEARCH_CONDITION
          -------------------- --------------- ------------------
          SYS_C0012096         C               "COL1" IS NOT NULL
          • 2. Re: IZ047 - Constraints - Guide Error ?
            995316
            Great. Thanks for clarification.