Solved

how to remove "not null" from an column?

Posted on 2003-11-05
6
4,689 Views
Last Modified: 2013-12-09
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
Comment
Question by:evikekka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 2

Accepted Solution

by:
Trekker72 earned 25 total points
ID: 9691402
try

update RDB$RELATION_FIELDS set RDB$NULL_FLAG = NULL
where (RDB$FIELD_NAME = 'my_column') and (RDB$RELATION_NAME = 'my_table');
0
 
LVL 10

Expert Comment

by:kacor
ID: 9692379
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
 

Author Comment

by:evikekka
ID: 9692716
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 10

Expert Comment

by:kacor
ID: 9693406
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
 
LVL 10

Expert Comment

by:kacor
ID: 9693417
The solution of Trekker72 is great.

wbr Janos
0
 

Author Comment

by:evikekka
ID: 9693424
yes it is.
it worked fine, but thanks anyway for your time and help
/evikekka
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question