The pk column does not need a not null constraint

sfradesfrade Posts: 10
edited February 5, 2014 5:24AM in Source Control for Oracle
Hi,

Source control generate the following SQL code
CREATE TABLE unified.titi (
  titi_id RAW(16) DEFAULT SYS_GUID() NOT NULL,
  titi_name VARCHAR2(50 BYTE) NOT NULL CONSTRAINT nn_titi_tina CHECK ("TITI_NAME" IS NOT NULL),
  CONSTRAINT titi_pk PRIMARY KEY (titi_id)
);

Whereas the original code is
CREATE TABLE TITI
(
  TITI_ID RAW(16) DEFAULT SYS_GUID(),
  TITI_NAME VARCHAR2(50 BYTE) CONSTRAINT NN_TITI_TINA NOT NULL,
  CONSTRAINT TITI_PK PRIMARY KEY (TITI_ID)
);

Source control code create an additional constraint on the field "titi_id" which is not necessary. As this field has a primary constraint, Oracle implicitly creates a not null constraint and a unique index on the field.

Seb

[/code]

Comments

  • Eddie DEddie D Posts: 1,780 Rose Gold 5
    Hi

    I understand the problem, I believe it to be caused in the method that Oracle creates the metadata information.

    Although you create your SQL script as follows:
    Code:

    CREATE TABLE TITI
    (
    TITI_ID RAW(16) DEFAULT SYS_GUID(),
    TITI_NAME VARCHAR2(50 BYTE) CONSTRAINT NN_TITI_TINA NOT NULL,
    CONSTRAINT TITI_PK PRIMARY KEY (TITI_ID)
    );

    Oracle is storing the metadata information as follows:
    Code:

    CREATE TABLE unified.titi (
    titi_id RAW(16) DEFAULT SYS_GUID() NOT NULL,
    titi_name VARCHAR2(50 BYTE) NOT NULL CONSTRAINT nn_titi_tina CHECK ("TITI_NAME" IS NOT NULL),
    CONSTRAINT titi_pk PRIMARY KEY (titi_id)
    );

    As Source Control for Oracle will read how the table is created from the metadata, hence the difference.

    I hope this does not cause a problem for you?

    I have submitted a Bug Report OSC-313 to highlight the problem to the Development Team.

    A support call has also be created for you which can be found HERE.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Thanks for the reply.
Sign In or Register to comment.