Problem solve Get help with specific problems with your technologies, process and projects.

Why are there and what is the effect of having blank rows in an Oracle table?

Why are there rows with no records in an Oracle table, and what is the effect on the integrity of the databases if there are blank rows? Is it necessary to maintain three or four tables for just making changes in the database?

The only way that I've seen rows in a table that seemed to have no records is when someone inserted all NULL values into the table. For instance, look at the following example:

   SQL> create table test (
     2  id number,
     3  name varchar2(20));

   Table created.

   SQL> insert into test values (NULL,NULL);

   1 row created.

   SQL> commit;

   Commit complete.

   SQL> select * from test;

           ID NAME
   ---------- --------------------

   SQL> select count(*) from test;


As you can see, it appears that I have one record in this table which is completely empty.

The effect of the integrity on the database is really determined by your business rules. The database has no problems holding these "blank" rows. But is this good for your business and the application that runs against the database? For instance, let's suppose that I have a table of invoices. And this table holds 100 "real" invoices and 10 of these blank rows. If my application needs to know how many invoices are out there and I do a SELECT COUNT(*) FROM INVOICES;, it will return 110 rows. When in fact, I really have only 100 invoices. So my application could return erroneous information because of my business rules.

Is it necessary to maintain three or four tables for just making changes in the database? No. You should just need the tables that will hold your data. For the most part, you may not need any tables just for the sole pupose of holding your changes. But again, it is all determined by your business rules.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.