# SQL Tutorial - How to drop a column from a table

## Метаданные

- **Канал:** BeardedDev
- **YouTube:** https://www.youtube.com/watch?v=vk0rHzFa1Sw
- **Источник:** https://ekstraktznaniy.ru/video/44478

## Транскрипт

### Segment 1 (00:00 - 05:00) []

hello and welcome to my channel i am beardeddev and in this video we're going to be covering dropping columns from a table we're going to be answering the following questions so can we drop multiple columns from a table within the same execution can we drop a column that is referenced in a view and what the impact of that would be can we drop a computed column can we drop a column used within an index foreign key or primary key so let's start off so i have a simple create table statement and as always if you would like to follow along this is available in the description so we're going to be focusing on the order details table so i'm just going to execute that to create the tables i want to work with and then we'll have a look at those results now the first thing to say before dropping any columns it's always important to consider the impact namely data loss once we drop a column we're not going to be able to recover that data without restoring backups so it's always best to get that double checked now i've never or i can't remember an instance where i've needed to drop a column within a productionized solution but often when i'm testing data models that i've put together or actually just developing solutions then often i will just drop columns from a table perhaps i've put them in the wrong object and they need to be moved so that's typically when i'd use a drop column statement so we have here an order details table we can see we've got an id an order id related to sort of a higher level table a product category id quantity price and total excluding vat so typically let's imagine a scenario let's say we've got the product id within this column and category it doesn't really belong here because it's something we should be retrieving from the product table or another table along that chain it's not something we would need in here it's not a fully normalized solution so let's start off with a simple example by dropping the product category so all we're going to do is the columns belong to a table so we're going to have to alter the table dbo order details and then we're just going to write drop and the drop column and the column name of product category and if we just run that statement there you can see that's actually executed successfully and if we look at our order details we can see that has been safely removed that's the only column that's been removed so the syntax is just alter the table name drop column and the column so you can see how easily is to actually drop columns providing you've got the correct permissions you will need a certain level of permissions to be able to drop columns for a table and that's all to do with protecting the database we don't want consumers of data to have those kind of permissions now let's have a look at if we wanted to remove multiple columns from the same table so i'm just going to run the create table again just to get us back our previous position now let's imagine we want to remove the product category id and the product id so can we simply just instruct sql server to drop multiple columns within the same statement so if i just put a comma after product category and then the product id column and execute that query and have a look at the results we can see we've successfully removed multiple columns within the same statement okay so we've been able to remove multiple columns from our table and we can see the syntax is quite straightforward and again always go back to the impact of removing data so we've lost product id and product category from this table that the only way we'd be able to restore would be to risk would be to restore a backup so it's always important to think of the implications we'll now move on to having a look at when those columns actually mean something so perhaps they're within another object such as a view or an index so again let's start off by just restoring the table which is quite straightforward for me because it's only a small table we're using here as an example um and we're going to be creating a view

### Segment 2 (05:00 - 10:00) [5:00]

called vw orders and it's going to be combining the orders id and the order details table on order id picking up some attributes from each table so if i create that view now and we can see that product category is actually part of that view now if i go back here and i actually want to first of all let's just have a look at what that view looks like that's vw orders so you can see we've got those columns there and we've got product category now we couldn't alter the view here so we can't have a syntax of alter view just change this to the view name and drop column so that syntax wouldn't work we'd have to alter view and write out our table definition again as we've got here we'd have to do an alter view up here and then just remove that column but let's imagine we aren't aware of all the views that contain this column so we're simply focusing on the table and we're going to be dropping that column from the table and what impact that would have on the view now if you know what impact this is going to have let me know in the comments below before i go through this demonstration and see if you get this right so is that is the column going to be actually dropped from the table or are we going to get an error so we're going to go back to our original we're altering the table and we're dropping the column product and we know that product category is going to be used within that view so if we alter this table now and drop that column we can see that's been executed successfully and if we have a look in our order details table that's not a problem we can query that table fine but remember that column is actually referenced within this view so if we have a look at running this query now we'll see we now get an error because the view definition contains a column that no longer exists in the table now if we want to add a layer of protection to our views so we don't end up in these kind of situations there is a thing called schema binding which we can add to our views which would prevent this from actually being dropped and i do have a video on that available on my channel i'll put a link in the top right coming up now where you can go and check that out so the moral of removing columns from tables that could be in views is it's better to check you can add that layer of protection with schema binding to prevent this kind of things happening but if you see this error to say there's an invalid column name within a view the likely causes is because one of the underlying columns has been dropped now again let's go back and restore our table and now we're going to have a look at computed columns so if you're not familiar with computed columns it's a column as an expression so we can see here we've got this column called total excluding vat and how that's made up of is quantity multiplied by price that's good because it saves us having to do that work within the database itself so if i was to update the quantity column or the price column that value is going to automatically compute for us there's a bit more detail to it to that but hopefully you get that idea so this column effectively has a dependency on two other columns let's have a look at if we can drop our total excluding that column and if we get any errors so we've got the table we're just going to change out our drop column this time total excluding that and if we execute this now that's been executed successfully and we can see uh order details so again there's nothing there protecting that column from being dropped again let's restore and then let's take a look at indexes so i'm just going to create a very simple index non-clustered index on the product category

### Segment 3 (10:00 - 13:00) [10:00]

column just go ahead and create that that's been done and now let's have a look if that prevents us from actually dropping this column so again let's try and execute this query now we're going to get errors so indexes are important a part of database relational management systems and they need to be well protected so you can't actually drop columns that are part of indexes to drop this column we'd actually need to remove or alter this index first of all and then we'd be able to drop that column because then there'd be no dependency there so any column that is used within an index cannot be dropped it is protected now let's have a look at our constraints so first of all foreign keys so just have a look at create table statement and we can see here we've got order id which is a foreign key to our orders table so let's have a look what happens if we were to try and drop order id from our order details table and again we'd get an error to say there's a dependency here you cannot complete this drop column because one or more objects access this column and foreign keys are interesting during a development stage of the database you'll often see um dba sql developers or data engineers that are building the objects once they're going through the development phase if you put in foreign keys too early you'll often see them pulling their hair out exactly because of this reason you cannot you've created a dependency between two tables so you can't drop that table you can't drop any columns until that dependency chain has broken to protect referential integrity and last of all we have the order details id which is the primary key column on the order details table and if we try and drop that column i'm sure you know by now again this is going to give us an error similar to the error we have with the foreign key just the name of the primary key object instead really hope you have enjoyed that video let me know your thoughts in the comments and if there's any other videos on data engineering or data analysis you'd like to see please do get in contact the key takeaway from this video is not now that you know how to drop columns you go and do it but you understand the impact so when you the need arises for you to drop a column you've got that syntax available and you understand the impacts of other objects thanks a lot for watching