a special case when modifing the database

sometimes i face the following case in my database design,, i wanna to know what is the best practice to handle this case:::

for example i have a specific table and after a while ,, when the database in operation and some real data are already entered.. i need to add some required fields (that supposed not to accept null)..

what is the best practice in this situation..

make the field accept null as (some data already entered in the table ,, and scarify the important constraint )and try to force the user to enter this field through some validation in the code..

truncate all the entered data and reentered them again (tedious work)..

any other suggestions about this issue...

It depends on requirements. If the data to populate existing rows for the new column isn't available immediately then I would generally prefer to create a new table and just populate new rows when the data exists. If and when you have all the data for every row then put the new column into the original table.

If possible i would set a default value for the new column.

e.g. For Varchar

alter table table_name 
    add column_name varchar(10) not null 
    constraint column_name_default default ('Test') 

After you have updated you could then drop the default

alter table table_name 
drop constraint column_name_default

A lot will come down to your requirements.

It depends on your application, your database scheme, your entities.

The best way to go about it is to truncate the data and re - enter it again, but it need not be too tedious an item. Temporary tables and table variables could assist a great deal with this issue. A simple procedure comes to mind to go about it:

1. In SQL Server Management Studio, Right - click on the table you wish to modify and select Script Table As > CREATE To > New Query Editor Window.

2. Add a # in front of the table name in the CREATE statement.

3. Move all records into the temporary table, using something to the effect of:

   INSERT INTO #temp SELECT * FROM original

4. Then run the script to keep all your records into the temporary table.

5. Truncate your original table, and make any changes necessary.

6. Right - click on the table and select Script Table As > INSERT To > Clipboard, paste it into your query editor window and modify it to read records from the temporary table, using INSERT .. SELECT.

That's it. Admittedly not quite straightforward, but a well - kept database is almost always worth a slight hassle.