Create or Replace Table

Meeuwtje

A table you first have to create it.

Then you can alter it.

But in a development/test/production environment this is not always practical.

Since you may want to change something of a new table before it goes to production.

You can use PL/SQL to avoid installation errors:

If the table doesn't exists, then do create.

If it does exits, then do an alter.

It makes a table script more complex.

So it would make our developers life much easier if it was possible to have the table syntax:

create or replace table XXXX

(...);

William Robertson

I've often thought this would be good, but the logic around applying differences would need to be worked out. What if you intended to rename a column? A "create or replace" operation could only add and drop them. Constraints, indexes, storage, partitioning etc would all have to work safely and predictably. I'm voting up, but I'm not sure how these challenges could be overcome.

BPeaslandDBA

I voted down because of the issues I foresee this creating with dependent objects. No database object has more dependents than a table. I'm afraid that replacing a table would invalidate too many dependent objects. For example, a table gets recreated without COLUMN_X and a view or a stored procedure that referenced COLUMN_X is now broken. The same could be said of other objects that allow CREATE OR REPLACE, but I guess I just see tables as being more of an issue.

Cheers,
Brian

GregV

I voted down because of the issues I foresee this creating with dependent objects. No database object has more dependents than a table. I'm afraid that replacing a table would invalidate too many dependent objects. For example, a table gets recreated without COLUMN_X and a view or a stored procedure that referenced COLUMN_X is now broken. The same could be said of other objects that allow CREATE OR REPLACE, but I guess I just see tables as being more of an issue.

Cheers,
Brian

I agree with Brian. A table may have too many dependencies to deal with. Columns involved in PKs, FKs, based on nested table types, etc.

I guess the workload behind the "replace" logic would be too demanding and not worth it.

William Robertson

I voted down because of the issues I foresee this creating with dependent objects. No database object has more dependents than a table. I'm afraid that replacing a table would invalidate too many dependent objects. For example, a table gets recreated without COLUMN_X and a view or a stored procedure that referenced COLUMN_X is now broken. The same could be said of other objects that allow CREATE OR REPLACE, but I guess I just see tables as being more of an issue.

Cheers,
Brian

Presumably it could be implemented as create if it does not exist + alter if it does and is different, so dependent objects would not necessarily be invalidated (any more than create or replace view or package has the potential to invalidate other objects). It would have to leave things in place that were not specified in the CREATE statement. I agree though that it seems unlikely to cover every possible scenario and there is a risk it would create more problems than it solved.

Gbenga Ajakaye

Replace is basically a drop and recreate. If the table has data, that could mean serious trouble. In theory, it sounds like it might be a great idea, but I'm not sure how this can be implemented and account of all the risks factors that have been mentioned above.

Russ Cannon

This makes no sense.  "and replace" is not provided for any objects that could have allocated segments, but it's not just the segments.  There are various other objects attached to tables like indexes, constraints, triggers, etc.  A replace of, say, a procedure is reasonable because the result is just a different version of the same thing.  A replace of a table is totally different.  You do not have a different version of the same thing.  You essentially have a new definition of the object stripped of everything else.  There is no form of a replace statement that could possibly update a table to a different version with all the data, indexes, triggers, etc. intact.

Think about it.  What if your replace changed a column type in an incompatible way?  How could the data possibly be retained across the transition?

Cheers,

Russ

Sven W.

Replace is basically a drop and recreate. If the table has data, that could mean serious trouble. In theory, it sounds like it might be a great idea, but I'm not sure how this can be implemented and account of all the risks factors that have been mentioned above.

Gbenga Ajakaye wrote:Replace is basically a drop and recreate. If the table has data, that could mean serious trouble. In theory, it sounds like it might be a great idea, but I'm not sure how this can be implemented and account of all the risks factors that have been mentioned above.

No it's not. If you read the suggestion carefully enough, then the REPLACE would do an ALTER (and add the missing columns for example).

Sven W.

This makes no sense.  "and replace" is not provided for any objects that could have allocated segments, but it's not just the segments.  There are various other objects attached to tables like indexes, constraints, triggers, etc.  A replace of, say, a procedure is reasonable because the result is just a different version of the same thing.  A replace of a table is totally different.  You do not have a different version of the same thing.  You essentially have a new definition of the object stripped of everything else.  There is no form of a replace statement that could possibly update a table to a different version with all the data, indexes, triggers, etc. intact.

Think about it.  What if your replace changed a column type in an incompatible way?  How could the data possibly be retained across the transition?

Cheers,

Russ

Russ Cannon wrote:This makes no sense. "and replace" is not provided for any objects that could have allocated segments, but it's not just the segments. ...What if your replace changed a column type in an incompatible way? How could the data possibly be retained across the transition?Cheers,Russ

If the change is incompatible then we would get the same error message than what we currently get with an ALTER statement.

I don't think this suggestion is easy to do for oracle. but that is their task to figure out how to implement such a "or replace" logic.

As a developer I see the potential if this would be a possible syntax.

A certain set of changes seems easily possible (add columns). A certain set of changes seems almost impossible to do (like renaming a column).

Maybe we get at least the easy to do changes and get an error message for example if a column disappears from the new table definition.

tvCa

It would be very hard indeed, but I still think it isn't that impossible either. Obviously something needs to compare the existing versus the requested, but I see the options as not that long of a list: - adding a column - expanding the width of the datatype of a column - dropping a column - or even making a datatype smaller There's much more rules involved in case of dropping or reducing, but if they all apply, it can be done. Note that many changes to a table are covered in the ALTER TABLE statement already. Maybe instead a statement could be made to: "add a column to a table, ONLY IF that column does not exist" A lot of requests to redefine a table could be covered by that. In fact, the ALTER TABLE does that, but without the existing condition ... no ?

tvCa

This makes no sense.  "and replace" is not provided for any objects that could have allocated segments, but it's not just the segments.  There are various other objects attached to tables like indexes, constraints, triggers, etc.  A replace of, say, a procedure is reasonable because the result is just a different version of the same thing.  A replace of a table is totally different.  You do not have a different version of the same thing.  You essentially have a new definition of the object stripped of everything else.  There is no form of a replace statement that could possibly update a table to a different version with all the data, indexes, triggers, etc. intact.

Think about it.  What if your replace changed a column type in an incompatible way?  How could the data possibly be retained across the transition?

Cheers,

Russ

The statement to "replace" could limit all kind of scenarios, like anything that involves changing the primary key. If you want to do that (I have never had such an idea, but assume somebody does), the normal action is to drop the table anyway. This would mean you are working in early design phase ..