Hey, everyone. Welcome to part four of topic three in our database class. And in this video, I'm going to provide you with an overview of this SQL alter statement. So let's take a look at our next DDL statement. And instead of doing create, we're now going to focus on alter. Okay. So when we use the alter statement, what we're doing is we're telling the database we want to change the structure of an existing database object. So we're no longer creating a new database object. We are changing the structure of an existing object, not its data, but it's structured. So let's imagine, for example, that we had an employee table that we created and that we forgot to establish a primary key. Well, we could just write another SQL statement, say alter table employee, and then we could add this new constraint. So the pattern is very similar. Right? It's the type of thing that we want to do. Previously, it was create. In this case, it's alter, the type of object that we want to alter, in this case, a table. And then the name of that object. And we're already familiar with this syntax. There's nothing different here. We use exactly the same thing when we want to establish a primary key in a create table statement. And the difference here is that we're adding it after the fact. So we're assuming that employee does not currently have a primary key. We are going to add one and we do that by repeating what we already know about this pattern for primary keys. The only addition is that we pretend that with an ad keyword. And the reason why is we'll learn a little later is that we can use exactly the same syntax with the word drop. Instead of add in order to get rid of an existing constraint. So for example, if I had say that I had an employee table that had a primary key, I wanted to get rid of that, I could use this drop constraint method. Right. And in that case, it would tell it to get rid of the constraint. Of course, in that scenario, I wouldn't need to specify this. I just need to provide the name of the constraint that I want to drop. So I would say alter table employee drop constraint, pk employee. And that would get rid of the existing primary key in the employee table. However, if we just need to add a primary key to an existing table that does not have one, something like what you see here, we'll do the job ad verbally. I'll just add a new constraint, no big deal. And as you can imagine, expanding on this, we want to add a composite primary key. It's exactly the same thing, except that we use a comma separated list of the columns in the existing table that we would like to use as the primary key. So in this case, we're creating a composite primary key that consists of employee ID and skill ID. And the syntax is exactly the same as what we saw previously. It's just that we use a comma separated list of columns instead of just one. So in its entirety, what we're telling the database is okay, database, I need you to make a change to the structure of the employee skill table. And specifically, I need you to create a composite primary key, consisting of the employee ID and the skill ID. And we're going to call that pk employee skill. So that's what we're telling the database to do here. But otherwise, the syntax is exactly the same as that we saw here, just adding another column to the list of columns inside the primary key parentheses. All right. What if we want to add a new column to a table rather than a new constraint, like a primary key? Well, we want to do that. All we have to do is again, use our alter table statement. We then specify the name of the table whose structure we want to alter. So in this case, telling the database, we want to change something about the structure of the employee table. And then what do we want to do? Well, we're going to add. And in this case, a new column. So we provide those three pieces of information that are required to define a new column. That is the name of the column. It's data type and whether or not null values are allowed. So as a little piece of practical advice, if you ever find yourself in a situation, where you need to add a new column to an existing table that has a bunch of data in it, you will almost certainly need to specify that null values are allowed. Because think about it, if I have this employee table that already has 100 rows of data in it, and I'm going to add a new column to that table called department ID, unless I allow null values, this won't work. Because I need some kind of value to insert in that new column. Right. So if I have something that looks like this, let's say that my employee table consists of an employee ID name, I have some values out there for that. So one, two, three, I think whatever the names are, like Dan Bob. Let's do Dan, Janet, whatever blah, blah, blah, blah. And then if we're trying to add, so we have existing data in this table, and if we go and try to add a new column out here, in this case, department ID, the only way that this will work is by allowing all values. And because naturally, when I add the new column, these three cells over here, in this case would not have a value supplied for them. So I must allow null values for the department ID, at least until I can put some data in there. So you ever find yourself in this situation where you're modifying a table after the fact and trying to add a new column, you will probably need to set the null status to allow null values. Otherwise, the database will throw an error when you try to run this statement as there will already be data in there and it won't have any values to insert for those existing rows. All right. So that's how we add new columns with our alter table state. Of course, we can add foreign keys as well as primary keys. The basic pattern is the same as what we saw with primary keys. So alter table, the name of the table we want to alter, what do we want to do? We want to add a constraint. Okay. The name of that constraint. And then the same pattern for a typical foreign key constraint. So we say this is going to be a foreign key constraint. And I want you to link department ID in my current table. To department ID over here in the department table. And that will then establish the relationship between the employee and department tables, assuming that one already did not exist. So if we forgot to create this relationship when we were first creating the tables, we can add it in a little later. Just with a few key strokes by typing out a SQL command like this one. So this is functionally the same. As what you've already done in the class when you were working with the diagrams in SQL Server Management Studio, if you established a relationship by dragging and dropping, dragging the name of one column in one table on top of the name of another column in another table. If you did that to establish a relationship, what's happening in the background is exactly what you see here. Right. When you make that change visually by dragging and dropping to establish a relationship between tables, what SQL Server Management Studio is doing is it's automatically generating a SQL statement exactly like this one and running it for you in the background to build that relationship.