The pk column does not need a not null constraint

Track changes to Oracle schemas in your existing version control system.

Moderators: eddie davis, richardjm, Michael Christofides, neil.anderson

The pk column does not need a not null constraint

Postby sfrade » Wed Dec 18, 2013 8:12 am

Hi,

Source control generate the following SQL code
Code: Select all
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

Code: Select all
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]
sfrade
 
Posts: 6
Joined: Tue Dec 17, 2013 2:07 pm

Postby eddie davis » Thu Jan 16, 2014 11:50 am

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
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby sfrade » Wed Feb 05, 2014 11:24 am

Thanks for the reply.
sfrade
 
Posts: 6
Joined: Tue Dec 17, 2013 2:07 pm


Return to Source Control for Oracle

Who is online

Users browsing this forum: No registered users and 1 guest