Thread: Adding constraint

I added a column to a table with
 ALTER TABLE company ADD varchar(8);

I had really wanted to have this set to not null but now I can't figure out
how to do that.  At first I thought that I could just drop the column... OK
that doesn't seem to be the way.

There is a fair amount of data in the tables and I could dump them and
rebuild it, but I just get the feeling that there is a easier way.  Am I
mad?  So here are the questions:

Can you alter a table and drop a column?

Can you add 'not null' to an existing column?

Or... I don't know... is there a better way?

Hi Patrick

> Can you alter a table and drop a column?
Not with an ALTER TABLE statement. You have to create a new table without
this column, fill it with the existing data, drop the original table and
rename the newly created table to the original name.

CREATE TABLE newname AS SELECT <fieldlist> FROM originaltable ...
creates and fills a new table with the results of a SELECT.

> Can you add 'not null' to an existing column?
The 7.2 Documentation says:
In the current implementation of ADD COLUMN, default and NOT NULL clauses
for the new column are not supported.

Regards
Conni