NOT NULL in Table creation

Compares and synchronizes the schemas of Oracle databases

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

NOT NULL in Table creation

Postby Maarten » Mon Feb 07, 2011 2:37 pm

After comparing schemes I get this output:
Code: Select all
-- Creation

CREATE TABLE "MARC"."DESTDETT" (
  "CARR_TYPE" NVARCHAR2(9) NULL,
  "COMP_CODE" NVARCHAR2(6) NULL,
  "CUST_NAME" NVARCHAR2(90) NULL,
  "CUST_NO" NVARCHAR2(90) NULL,
  "CUST_ZONE" NVARCHAR2(9) NULL,
  "FRT_PAY_CODE" NVARCHAR2(6) NULL,
  "MIN_MINS_BEFORE_DEPART" NUMBER(4) NULL,
  "MSG_FLG" NVARCHAR2(36) NULL,
  "ORD_PRIO" NVARCHAR2(6) NULL,
  "ORD_TYPE" NVARCHAR2(6) NULL,
  "PROC_SEQ_NO" NUMBER(9) NULL,
  "PROD_NO" NUMBER(9) NULL,
  "RULE_ID" VARCHAR2(90)    -----> NULL,
  "RULE_TYPE" NVARCHAR2(3) NULL,
  "SCHEDULE_CODE" NVARCHAR2(1) NULL,
  "SINGLE_CONSOL_FLG" NVARCHAR2(3) NULL,


where my collumn has:

Code: Select all
SQL> desc destdett
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CARR_TYPE                                        NVARCHAR2(9)
 COMP_CODE                                      NVARCHAR2(6)
 CUST_NAME                                       NVARCHAR2(90)
 CUST_NO                                           NVARCHAR2(90)
 CUST_ZONE                                       NVARCHAR2(9)
 FRT_PAY_CODE                                  NVARCHAR2(6)
 MIN_MINS_BEFORE_DEPART                NUMBER(4)
 MSG_FLG                                           NVARCHAR2(36)
 ORD_PRIO                                         NVARCHAR2(6)
 ORD_TYPE                                         NVARCHAR2(6)
 PROC_SEQ_NO                                   NUMBER(9)
 PROD_NO                                          NUMBER(9)
 RULE_ID                     ---->   NOT NULL  VARCHAR2(90)
 RULE_TYPE                                          NVARCHAR2(3)
 SCHEDULE_CODE                               NVARCHAR2(1)
 SINGLE_CONSOL_FLG                         NVARCHAR2(3)


Is this a bug?
Maarten
 
Posts: 4
Joined: Mon Feb 07, 2011 1:09 pm

Postby Tom Harris » Tue Feb 22, 2011 3:02 pm

Hi there,

apologies for the delay in getting back to you. This one had us bamboozled for a while. Our theory is that the constraint is probably DEFERRED which means you can insert nulls into that column as long as they are not there when the transaction finishes. This causes Oracle to report that the column can accept nulls. Could you please run the following SQL to confirm if this is what is happening?

Regards, Tom

Tom Harris, Red Gate Software

SELECT
c.owner,
c.table_name,
c.qualified_col_name AS column_name,
c.nullable
FROM all_tab_cols c
WHERE c.owner = 'MARC' AND c.table_name = 'DESTDETT'
ORDER BY owner, c.table_name, c.column_id;


SELECT
c.owner,
c.constraint_name,
c.table_name,
cc.column_name,
c.search_condition,
c.deferrable,
c.deferred
FROM
all_constraints c
LEFT JOIN all_cons_columns cc ON c.owner = cc.owner
AND c.constraint_name = cc.constraint_name
WHERE c.owner = 'MARC' AND c.table_name = 'DESTDETT'
AND c.constraint_type IN ('P', 'U', 'C', 'V', 'O')
ORDER BY owner, c.constraint_name, cc.position, cc.column_name
Tom Harris
 
Posts: 308
Joined: Wed Oct 06, 2004 3:45 pm

Postby Maarten » Sat Mar 19, 2011 2:09 am

First query:
Code: Select all
OWNER TABLE_NAME COLUMN_NAME NULLABLE
MARC DESTDETT CARR_TYPE Y
MARC DESTDETT COMP_CODE Y
MARC DESTDETT CUST_NAME Y
MARC DESTDETT CUST_NO Y
MARC DESTDETT CUST_ZONE Y
MARC DESTDETT FRT_PAY_CODE Y
MARC DESTDETT MIN_MINS_BEFORE_DEPART Y
MARC DESTDETT MSG_FLG Y
MARC DESTDETT ORD_PRIO Y
MARC DESTDETT ORD_TYPE Y
MARC DESTDETT PROC_SEQ_NO Y
MARC DESTDETT PROD_NO Y
MARC DESTDETT RULE_ID N
MARC DESTDETT RULE_TYPE Y
MARC DESTDETT SCHEDULE_CODE Y
MARC DESTDETT SINGLE_CONSOL_FLG Y


Second query:
Code: Select all
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME SEARCH_CONDITION DEFERRABLE DEFERRED
MARC DESTDETT_PRIM DESTDETT RULE_ID <null>NOT DEFERRABLE IMMEDIATE
Maarten
 
Posts: 4
Joined: Mon Feb 07, 2011 1:09 pm

Postby Tom Harris » Mon Mar 28, 2011 12:22 pm

Hi there,

we believe that we have now fixed this issue. Please drop an email to productsupport@redgate.com so that we can send you out a new build to try.

Kind regards, Tom

Tom Harris, Red Gate Software
Tom Harris
 
Posts: 308
Joined: Wed Oct 06, 2004 3:45 pm

Postby Maarten » Tue Mar 29, 2011 8:26 am

Hi Tom,

I've send you an email
Maarten
 
Posts: 4
Joined: Mon Feb 07, 2011 1:09 pm

Postby Tom Harris » Thu Mar 31, 2011 11:51 am

Hi there,

the fix for the issue is now incorporated into the Red Gate Oracle tools download. You can grab the latest version from

http://www.red-gate.com/products/oracle-development/

Kind regards, Tom

Tom Harris, Red Gate
Tom Harris
 
Posts: 308
Joined: Wed Oct 06, 2004 3:45 pm


Return to Schema Compare for Oracle

Who is online

Users browsing this forum: No registered users and 0 guests