NOT NULL in Table creation
Maarten
Posts: 6
After comparing schemes I get this output:
where my collumn has:
Is this a bug?
-- 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:
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?
Comments
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
Second query:
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
I've send you an email
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