|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/9/2008 8:29:31 PM
Posts: 3,
Visits: 6
|
|
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.
- Chitwan
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:13:33 PM
Posts: 133,
Visits: 359
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/9/2008 8:29:31 PM
Posts: 3,
Visits: 6
|
|
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
- Chitwan
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:13:33 PM
Posts: 133,
Visits: 359
|
|
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
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/9/2008 8:29:31 PM
Posts: 3,
Visits: 6
|
|
No, I want to modify the existing column only. I will not create any new column. No no ADD COLUMN will be there.
- Chitwan
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/25/2008 5:46:43 PM
Posts: 23,
Visits: 73
|
|
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.
Ivy
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/25/2008 5:46:43 PM
Posts: 23,
Visits: 73
|
|
Need to correct myslef. 'Alter table' can dropped a non index field. And 'ALTER TABLE ABC ADD ID NOT NULL;' works fine....
Ivy
|
|
|
|