I have two tables. I need to populate one column in table B from a concatenation of two columns in table A. The problem is that in table B there are two columns that are labeled NOT NULL. At this time I cannot populate the second column with any valid data. Is there a way to populate the one column without the other in table B?
Let's assume that tableB has been declared like this --
create table tableB ( col1 varchar(20) not null , col2 varchar(20) not null )
When you do any type of insert, the NOT NULL constraint will be applied to each column. So, obviously, you cannot just populate col1, because every row has to have a value for col2. There are two ways to look at this problem.
The first approach acknowledges your situation as being valid. In other words, the design of the table is wrong, and the NOT NULL constraint should not have been declared for col2. Change the design and you will be able to populate tableB like this --
insert into tableB ( col1 ) select colx||coly from tableA
The two columns from tableA, colx and coly, are concatenated and the result inserted into col1 of tableB. On each row, the value of col2 in tableB is set to NULL.
If you cannot get the definition of col2 changed to NULL, perhaps you can get it changed to declare a default value.
create table tableB ( col1 varchar(20) not null , col2 varchar(20) not null default '')
This means that whenever no value is specified (such as the query above), col2 is assigned its default. It's important to note that a zero-length string is not the same as a null value.
The second approach, which is required if you cannot get the definition of tableB changed at all, is simply to insert something into col2 yourself. It cannot be a NULL, but it can be a "special" value instead.
insert into tableB ( col1 , col2 ) select colx||coly , '' from tableA
This isn't the best solution because it muddies the water -- whoever designed tableB and said col2 had to be NOT NULL must've had a reason. If you insert a bunch of zero-length strings (assuming the column was declared VARCHAR) or blank strings (assuming the column was declared CHAR), these "special" values will be difficult to distinguish from "real" values. This is usually harmless when it comes to character columns but if the column is numeric, you will typically be entering a value of 0 or perhaps -1 or 9999 or some other number to indicate a value that isn't really there -- which is what NULLs are supposed to do! Furthermore, you'll have problems with aggregate functions like COUNT(col2) and AVG(col2) which will give incorrect results.
I have seen real life examples where NOT NULL was declared to get around a programming problem (where separate programming variables had to be declared for each field that could be null) or to avoid having to test for NULLs (a naïve attempt to prevent the need for OUTER JOINs). I am firmly in favour of declaring a column NULL when that is indeed a possibility.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.