Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Abid
110 Posts |
 Posted - 2014-01-16 : 07:30:16
|
| Hi. I have a table SaleMaster that is :[CODE]SaleID, SaleTotalAmount, SalePaidAmount, SaleDisocunt, SaleNetBal, SaleDate[/CODE]and SaleDetail table.:[CODE]SaleID, ProdID, SaleUnitPrice, SaleQty, Saletotal[/CODE]SaleID, ProdID will be composite keyNow i want to add a new field called: CustomerID (customerInfo is a separate table) as a foreign key so my new table should like this:[CODE]SaleID, ProdID, CustomerID, SaleUnitPrice, SaleQty, SaleTotal[/CODE]SaleID, ProdID and CustomerID will be composite key. But when i insert the new column so it gives an error (Saving Changes is not permitted. The changes you have made). Please guide me that how do i insert it now. |
|
|
nagino
Yak Posting Veteran
75 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-17 : 01:54:28
|
quote:
Originally posted by Abid
Hi. I have a table SaleMaster that is :[CODE]SaleID, SaleTotalAmount, SalePaidAmount, SaleDisocunt, SaleNetBal, SaleDate[/CODE]and SaleDetail table.:[CODE]SaleID, ProdID, SaleUnitPrice, SaleQty, Saletotal[/CODE]SaleID, ProdID will be composite keyNow i want to add a new field called: CustomerID (customerInfo is a separate table) as a foreign key so my new table should like this:[CODE]SaleID, ProdID, CustomerID, SaleUnitPrice, SaleQty, SaleTotal[/CODE]SaleID, ProdID and CustomerID will be composite key. But when i insert the new column so it gives an error (Saving Changes is not permitted. The changes you have made). Please guide me that how do i insert it now.
seehttp://visakhm.blogspot.in/2014/01/issues-in-altering-objects-through-ssms.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
Abid
110 Posts |
Posted - 2014-01-18 : 09:33:47
|
| Hi. I've seen similar sites on google but I read a post in a forum in which it is mentioned that Unchecking this option is not safe and may cause problem in future in DB. What about that. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-19 : 02:21:19
|
quote:
Originally posted by Abid
Hi. I've seen similar sites on google but I read a post in a forum in which it is mentioned that Unchecking this option is not safe and may cause problem in future in DB. What about that.
Nope thats wrongUnchecking this option has no effect on db as such. It is just an extra restriction put by designer. So far as you've access and are supposed to change the objects you can go ahead and do it.You will be able to complete the change only if you're given access to modify the objects.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2014-01-20 : 08:41:03
|
quote:
Originally posted by Abid
Hi. I've seen similar sites on google but I read a post in a forum in which it is mentioned that Unchecking this option is not safe and may cause problem in future in DB. What about that.
When you insert a new column amongst others (i.e. not "at the end"), or change a column in certain ways, then the Script that SQL generates does this:Drop constraintsCreate new temporary tableRecreate some of the constrains on the temporary table (e.g. Defaults)Copy all the data from Old table to Temporary tableDrop old tableRename Temp table to original nameRecreate remainder of constraints (e.g. Foreign Keys)If the table has a lot of rows then creating the new temporary table, and copying the data into it, can take a significant amount of time, and disk space, including perhaps extending the data file, and maybe filling up the disk ...It is for that reason that it is not allowed normally, and you have to explicitly set to option to allow it.Take a backup of your database first (always good practice  ), and make sure noone is actually trying to update data in the table when you make the change. |
|
|
|
Abid
110 Posts |
Posted - 2014-01-23 : 13:48:58
|
| @ Visakh, Bundles of thanks for making my mind clear for this ambiguity. Thank you so much, you really enhanced my knowledge.@ Kristen, what an adorable explanation you provided. It really made me out of vague ideas, cause in the same website, where i read that this option may harm your DB, was also written there that when it creates Temporarily table then you will have to reset the temporarily table by yourself and a lengthy way was provided. But after reading yours and Visakh reply, i am now very clear about this thing.Thank you very much for your affectionate participation in my thread. :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-24 : 01:03:51
|
quote:
Originally posted by Abid
@ Visakh, Bundles of thanks for making my mind clear for this ambiguity. Thank you so much, you really enhanced my knowledge.@ Kristen, what an adorable explanation you provided. It really made me out of vague ideas, cause in the same website, where i read that this option may harm your DB, was also written there that when it creates Temporarily table then you will have to reset the temporarily table by yourself and a lengthy way was provided. But after reading yours and Visakh reply, i am now very clear about this thing.Thank you very much for your affectionate participation in my thread. :)
No problem. Glad that I could help you out.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
How to insert new column in an old table