NOT NULL in Table creation

Comments

• Options

  Tom Harris Posts: 318

  February 22, 2011 9:02AM

  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

  0 · Share on Twitter
• Options

  Maarten Posts: 6

  March 18, 2011 9:09PM

  First query:

  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:

  OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME SEARCH_CONDITION DEFERRABLE DEFERRED 
  MARC DESTDETT_PRIM DESTDETT RULE_ID <null>NOT DEFERRABLE IMMEDIATE
  

  0 · Share on Twitter
• Options

  Tom Harris Posts: 318

  March 28, 2011 7:22AM

  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

  0 · Share on Twitter
• Options

  Maarten Posts: 6

  March 29, 2011 3:26AM

  Hi Tom,
  
  I've send you an email

  0 · Share on Twitter
• Options

  Tom Harris Posts: 318

  March 31, 2011 6:51AM

  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

  0 · Share on Twitter