Home » SQL & PL/SQL » SQL & PL/SQL » Inserting null or space in DATE datatype
Inserting null or space in DATE datatype [message #235454] Sun, 06 May 2007 04:19 Go to next message
ataufique
Messages: 79
Registered: November 2006
Member
Hi,

I have a column with date as datatype and not null,
With number i can insert 0 , with varchar i can insert
space, what to be inserted if i want to go ahead
without inserting date ..

Thanks
Re: Inserting null or space in DATE datatype [message #235456 is a reply to message #235454] Sun, 06 May 2007 04:55 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

the best way would be to remove NOT NULL constraint from such fields generally.
Are you aware, for eg. numeric field, you are unable to distinguish between zero value and not filled (calculated) value. For example, there is a big difference between zero debt and a debt which was not calculated yet.

If you want to bypass this restriction, you can choose any date in the past your application does not use in valid fields. It is up to the application to treat it right then. Just for information, Oracle DATE datatype starts with January 1, 4712 BC.

However once again, I do not recommend this approach, as it is just confusing and needs special treating from the application.
Re: Inserting null or space in DATE datatype [message #235457 is a reply to message #235456] Sun, 06 May 2007 05:16 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
Thanks,

It is like this that i need to insert the pk of the table (Master)
containing the said date column,
Then other independent tables are inserted and if successful
then values are updated for the master tables, so the date
column should have value but not at first insert.

Can't we insert something like space for a date datatype


Re: Inserting null or space in DATE datatype [message #235469 is a reply to message #235454] Sun, 06 May 2007 11:24 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You can insert ANYTHING you do not use as "normal" value; however you have to distinguish it in your application and treat it as "empty" date. Eg. UNIX time starts with January 1, 1970, Oracle DATE starts with January 1, 4712 BC. There is low probability your application will work with this date, so you can use it as "blank" (although I do not recommend it).

It is the same way as you work with "empty" VARCHAR. Really, why do you use space as "empty" VARCHAR? Why is it not eg. a tab character (CHR(9)) or any other whitespace character(s)? You have chosen space, your application knows it and treats it as "empty" VARCHAR then.

Maybe, if this inserting is in one transaction, you should change the order of inserts to fill the Master as last. Maybe you should ask whether the primary key containing this DATE column will not lead to this issue later and decide to use surrogate key instead (with changing the DATE column to NULL).
Re: Inserting null or space in DATE datatype [message #235479 is a reply to message #235457] Sun, 06 May 2007 14:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ataufique wrote on Sun, 06 May 2007 12:16
Thanks,

It is like this that i need to insert the pk of the table (Master)
containing the said date column,
Then other independent tables are inserted and if successful
then values are updated for the master tables, so the date
column should have value but not at first insert.

Can't we insert something like space for a date datatype




Using dummy values is a VERY bad idea, as others already told you.
You should use a deferrable constraint for this:
SQL> create table faq(id number, my_date date);

Table created.

SQL> alter table faq add constraint faq_date_ck check (my_date is not null) initially deferred deferrable;

Table altered.

SQL>
SQL> insert into faq values (1, null);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SCOTT.FAQ_DATE_CK) violated


SQL> insert into faq values (1, null);

1 row created.

SQL> update faq set my_date = trunc(sysdate);

1 row updated.

SQL> commit;

Commit complete.

SQL> spool off

[Updated on: Sun, 06 May 2007 14:28]

Report message to a moderator

Re: Inserting null or space in DATE datatype [message #235854 is a reply to message #235479] Tue, 08 May 2007 03:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I am in total agreement with Frank here.
Using Dummy, or 'Magic' values (ie values which have a special meaning not derivable from the data) is a bad idea.

Don't do it.
Re: Inserting null or space in DATE datatype [message #236142 is a reply to message #235854] Wed, 09 May 2007 03:10 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
thanks all.. i am also in agreement with u all..
but in my scenerio the process is like this..
Insert in the master file (PK), This ensures that file is
register..
Then do all processes with other table, if success then update
other columns of the master(all reqd) else
call deletion program and based on master delete the incomplete data from other tables...for deletion program to work it requires a entry in the master and so on........
Re: Inserting null or space in DATE datatype [message #236187 is a reply to message #236142] Wed, 09 May 2007 04:34 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
or do it like this:
Make your constraints deferrable

Insert in the master
process details
if all is well, update master and commit
else rollback

THIS is how transactions are used.
Previous Topic: DBMS_JOB Problem
Next Topic: gv$session vs v$session
Goto Forum:
  


Current Time: Sat Dec 03 15:53:48 CST 2016

Total time taken to generate the page: 0.06662 seconds