Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4693
  • Last Modified:

how to remove "not null" from an column?

hello friends, I have a problem and hope u can help me.
Its probably quite easy so the point are not that high.
here we go:
(my english vacabulary is not the best but I hope youll understand)


I have a table filled with data.
one of the column is declared as "not null" and now I want to remove the "not null"-thingy.
I haved tryed the following but it didnt work.

ALTER TABLE my_table ALTER COLUMN my_column DROP not null;

what do I do wrong.
/evikekka
0
evikekka
Asked:
evikekka
  • 3
  • 2
1 Solution
 
Trekker72Commented:
try

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = NULL
where (RDB$FIELD_NAME = 'my_column') and (RDB$RELATION_NAME = 'my_table');
0
 
kacorretiredCommented:
Hi evikekka,
try as follows:
1. insert a new column with the same properties except NOT NULL
2. copy into the new column the data from the old column
3. delete the old column
4. rename the new column to the old column name

with best regards

Janos
0
 
evikekkaAuthor Commented:
excelent Trekker72.
ty vm.

and Janos,
thank you to.
I found that solution myself on borlands homepage,  the problem was that I didnt know how to copy data;)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
kacorretiredCommented:
Hi evikekka,

sorry, I made a mistake.

To modify an existing column while retaining the data in that column, you need to perform a six step process:
1. Use ALTER TABLE to add a new column to the table.
2. Perform an UPDATE query to copy the data from the existing
column to the new column.
3. Use ALTER TABLE to drop the existing column.
4. Use ALTER TABLE to add a new column with the revised settings, using the same name as the column just dropped.
5. Perform another UPDATE query to copy the data from the temporary column to the newly created column.
6. Finally, use ALTER TABLE to drop the temporary column at the
end of the table.

This sequence of steps is required if you need to make a change that is not supported by the ALTER TABLE statement, such as changing the datatype of a column. To change the name of a column, perform steps 1 through 3.
0
 
kacorretiredCommented:
The solution of Trekker72 is great.

wbr Janos
0
 
evikekkaAuthor Commented:
yes it is.
it worked fine, but thanks anyway for your time and help
/evikekka
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now