Modify a column defination with NOT NULL of a Table

Database

Modify a column defination with NOT NULL of a Table

Hi Experts,

There is one table creating by my application and I want to modify it with it's column. The difference will be only that that column will becomes NOT NULL. I am able write a constraint on my table for NOT NULL of the column but it does not apear as column level.. he help me out with the correct syntex.

CREATE TABLE ABC (ID INTEGER);

Attemp to
ALTER TABLE ABC MODIFY check (Customer_ID integer not null);

The above syntext is in correct. Please write me with correct syntex.

Thanks in advance,
Chitwan Humad
Pune.
8 REPLIES
Teradata Employee

Re: Modify a column defination with NOT NULL of a Table

Hello,

You can use it as follows:

ALTER TABLE ABC ADD ID NOT NULL;

You can only do that in non-indexed columns. The DDL you provided will not allow you to add this constraint. Following is the example with changed DDL and added constraint:

CREATE TABLE ABC2 (ID INTEGER, ID1 INTEGER, ID2 INTEGER);
SHOW TABLE ABC2;
ALTER TABLE ABC2 ADD ID1 NOT NULL;
SHOW TABLE ABC2;

HTH.

Regards,

Adeel

Re: Modify a column defination with NOT NULL of a Table

Hi Adeel,

I can not add column after table is created. rather I would say the table has been created by my application is very simple table and now I want to ALTER it with adding NOT NULL key to a column. You can understand it as that is the flow of handeling my table column. Please assist me on the same.

- Chitwan
Teradata Employee

Re: Modify a column defination with NOT NULL of a Table

The way i understand it now is, you need to add NOT NULL column in a table after its creation using ALTER TABLE command. For that you can use following:

ALTER TABLE ABC ADD Column2 INTEGER NOT NULL;

Regards,

Adeel

Re: Modify a column defination with NOT NULL of a Table

No, I want to modify the existing column only. I will not create any new column. No no ADD COLUMN will be there.
Enthusiast

Re: Modify a column defination with NOT NULL of a Table

I don't think you can modify the property of the columns unless you rebuild the table with the new column type. Correct me if I am wrong. So far as I know 'Alter table' can only add a new column but not drop, neither modify columns.
Enthusiast

Re: Modify a column defination with NOT NULL of a Table

Need to correct myslef. 'Alter table' can dropped a non index field. And 'ALTER TABLE ABC ADD ID NOT NULL;' works fine....

Re: Modify a column defination with NOT NULL of a Table

Hi,

Just to know a small information

first i had a table and i had added a new column to that table now as below

alter table [tablename] add columnname INTEGER Not NULL DEFAULT 0

now i need to modify the above mentioned column to NOT NULL alone now it should not have the default value

so can this be done

if done could tell me the query for this

Supporter

Re: Modify a column defination with NOT NULL of a Table

funny - seems to be difficult the get ride of the default value

never the less

alter table [tablename] add columnname DEFAULT NULL;

might solve the issue.

As the column is defined as not null a missing value for B will cause a "Column 'b' is NOT NULL.  Give the column a value."