Adding column to existing table in a database

25 pts.
Tags:
Data Tables
Database management
Sunrise
When I add column in existing table. it contains null values in older records. it is possible to assign a value when add column so older records are modified with value

Answer Wiki

Thanks. We'll let you know when a new response is added.

See discussion below for more details.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JimmyIT
    what type of database?
    1,725 pointsBadges:
    report
  • carlosdl
    Most likely not. You would have to alter the table and update previous records.
    69,835 pointsBadges:
    report
  • carlosdl
    "You can do it if you'll determine a DEFAULT value for a new column added" Yes, that could be an option, but depending on the DBMS, the DEFAULT clause may only apply when INSERTING new rows.
    69,835 pointsBadges:
    report
  • msi77
    "but depending on the DBMS" But this is Standard behaviour. :-) If you know this odd DBMS, what it'll return when running the following alter table T add f1 int not null default 10; ?
    1,670 pointsBadges:
    report
  • carlosdl
    "But this is Standard behaviour" It depends on what you mean by "this". In your example, you made the column non nullable, but you didn't mention the 'not null' part in the original answer (nor did the OP state that he/she wanted the column to be 'not null'), and you can certainly add a new column with a DEFAULT value without specifying the NOT NULL constraint. Run the following command on an Oracle database, and run it on a MS SQL Server one, and you will see that it is not 'standard': alter table T add f1 int default 10; ? So, as it is not standard (well, it could even be 'standard' but that doesn't mean all vendors will adhere to it), how it will behave depends on the DBMS. However, if the new column will be non nullable then the DEFAULT clause will work. If not, you could add the new column as non nullable, and then make it nullable again, but, depending on the DBMS (and the available indexes), it could be less efficient than adding the column and updating the needed rows afther that. I realize, however, that my first comment ('Most likely not...'), without more clarification, was misleading.
    69,835 pointsBadges:
    report
  • msi77
    It depends on what you mean by “this”. From SQL:2003 Standard: Let C be the column added to Table. i) If C is an identity column, then for each row ... ii) Otherwise, every value in C is the default value for C. but you didn’t mention the ‘not null’ part in the original answer Because NULL is DEFAULT value by default :-), i.e. add f1 int implies add f1 int default null So this example is less presentable.
    1,670 pointsBadges:
    report
  • carlosdl
    I guess you didn't run the test I suggested, did you ?
    alter table T
    add f1 int default 10;
    If you run this on Oracle (with the appropriate type change), old records will be updated with '10' on the new field (without the need to specify the NOT NULL constraint). If you run it on SQL Server, the new column will be left null for all previous records (unless a NOT NULL constraint is added). So, the only way to make sure it will work as desired on all platforms is adding a NOT NULL constraint, and that's why I thought it was important to note it. "Because NULL is DEFAULT value by default" That's true, but a default value is something different than a NOT NULL constraint. If a NOT NULL constraint is specified then obviously a default value must be defined, but not vice versa. In the example above a DEFAULT value was specified, so the NULL default value doesn't apply, but the column can still be left null for previous records (again, depending on the DBMS). Anyway, it would be interesting to take a look at the SQL 2003 standard. Do you have a link ?
    69,835 pointsBadges:
    report
  • msi77
    If you run it on SQL Server, the new column will be left null for all previous records (unless a NOT NULL constraint is added). Indeed. Agreed. Do you have a link ? I'd prefer to give it via email, but I can't find your e-address in profile. You can write me on smois77@gmail[dot]com.
    1,670 pointsBadges:
    report
  • msi77
    Carlosdl, So, the only way to make sure it will work as desired on all platforms is adding a NOT NULL constraint, and that’s why I thought it was important to note it. The following syntax (SQL Server) solves the issue:
    alter table T
    add f1 int default 10 with values;
    1,670 pointsBadges:
    report
  • carlosdl
    Great. No more debate ;-)
    69,835 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following