Red Gate forums :: View topic - NOT NULL in Table creation
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Schema Compare for Oracle
Schema Compare for Oracle forum

NOT NULL in Table creation

Search in Schema Compare for Oracle forum
Post new topic   Reply to topic
Jump to:  
Author Message
Maarten



Joined: 07 Feb 2011
Posts: 4

PostPosted: Mon Feb 07, 2011 2:37 pm    Post subject: NOT NULL in Table creation Reply with quote

After comparing schemes I get this output:
Code:
-- 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:

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?
Back to top
View user's profile Send private message
Tom Harris



Joined: 06 Oct 2004
Posts: 308

PostPosted: Tue Feb 22, 2011 3:02 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Maarten



Joined: 07 Feb 2011
Posts: 4

PostPosted: Sat Mar 19, 2011 2:09 am    Post subject: Reply with quote

First query:
Code:
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:
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME SEARCH_CONDITION DEFERRABLE DEFERRED
MARC DESTDETT_PRIM DESTDETT RULE_ID <null>NOT DEFERRABLE IMMEDIATE
Back to top
View user's profile Send private message
Tom Harris



Joined: 06 Oct 2004
Posts: 308

PostPosted: Mon Mar 28, 2011 12:22 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Maarten



Joined: 07 Feb 2011
Posts: 4

PostPosted: Tue Mar 29, 2011 8:26 am    Post subject: Reply with quote

Hi Tom,

I've send you an email
Back to top
View user's profile Send private message
Tom Harris



Joined: 06 Oct 2004
Posts: 308

PostPosted: Thu Mar 31, 2011 11:51 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group