Hi, welcome back. In this lesson, we understand how to alter the structure of a table. In the previous lesson, we created tables. We took all important decisions, but we live in a dynamic world where you may have to change the structure of the table because of some compelling reasons. So what if you want to add a new column to an existing table? Or what if you want to delete an existing column?

What if you want to increase the width of a column and there are many such requirements? Now, we will see how to deal with all that in this particular lesson. But before that, I want you to understand there is something called data dictionary. data dictionary is part of your database. It is a collection of tables. And in those tables or achill, stores information about the database, like what tables you have, what columns you have, and what constraints you have for your columns and tables.

And a lot of information about database is part of database itself. That means the data dictionary stores the data about database. It's also known as method data, data about the data. And the best part of it is Oracle takes care of data dictionary on its own. You never need to modify anything about the data dictionary. It's called active data dictionary.

So the DBMS is automatically updating data dictionary whenever it is needed, then can we access this Data Dictionary. Yes, you can access data dictionary by using some views. Well, don't worry if the term view is not known to you. We will get into all the details later in this course. But a view is very much like a table. And it is created so that you get access to an existing table.

And you get only what you want from the table. So data dictionary views are created by Oracle so that users can access data that is relevant to them. Any view that starts with the user is containing data related to user. Now, let's see a few things. So here we are. And I want to know what are the tables I have, so I can just give something like Select star from tab.

It's a kind of data dictionary and it is providing information about all the tables you have, don't worry about those tables. They are the tables representing the deleted tables, don't worry, for now, these are the tables I have. And you can see the students and courses which we created in previous lesson. Then we can also get more information about for example constraints. So we can say user constraints and that is another data dictionary view. It provides you access to data dictionary and these are user constraints.

So the constraints are having some meaningful names and the constraints are of different types, like some of them are check constraints, some of them are foreign keys, and all that. So these are the constraints we have. Especially if you look at these constraints. They belong to Do our tables which we created in the previous lesson, like courses table and students table. But what if you want to get constraints only for a particular table? Yes, we can.

There you can see a column called table name. So where you say table name is equal to, I'm interested in constraints related to students. So I give this let's try. Well, it is going to give me nothing. Wow. We do have some constraints and we have seen them already.

Well, the reason why we don't get anything here is there is no table with the names students. I mean, there is no table with the name, lower case students, because when the data is stored in data dictionary, the object names are always in uppercase. So don't forget that you must always Ways give it in uppercase, and then it works. So now you get all the constraints related to students table, or any other table for that matter, for example, jobs table. And we have only two constraints for jobs table, and so on. So coming back.

So that's about the user constraints. And don't forget to give it in uppercase, because object names are always stored in uppercase in data dictionary. And then this is the command we use to alter the structure of the table. ALTER TABLE is what we use to add new columns to existing columns and modify some characteristics of existing column and dropping a column. Let us see how to deal with them. So this is the command and this is the syntax, like add modified drop You can also enable and disable constraints.

So for the time being, you can disable a constraint so that it is inactive. Later, you can enable it, or you can even drop it. We'll see. So here are some of the examples. To start to wait. I would like to add a new column to courses table.

It's called description and it is of type where character to and this is the way you add a new column to courses table. So this will add third column to courses table and this is going to be the type of that column and then you can modify you felt the name of the course is to be 40 characters, not 30. So you can increase the width of the column anytime you want. But if you want to decrease the width of the column Maybe you're trying to decrease it from 30 to 20. Well, that is possible provided the data in the column is not truncated. So if all the values in that column have less than 20 characters, then you can decrease it to 20.

Otherwise, you're not allowed to do that, because that would result in loss of data, then we can drop a constraint. If I don't want a constraint anymore, I can say drop the constraint. And this is the constraint name. So give a meaningful name to constraint so that you can easily drop the constraint or refer to constraint by its name. Even when we tried to enable disable. We give the constraint name.

So remember the name. Of course, you can always get it from user constraints. The table which I'm talking about right, and then this is about dropping a column, you can drop a column all together. But this is generally very lengthy process. So you are strongly recommended to do this only when the system is relatively free. So don't drop columns when system is busy, because it could really take a lot of time.

So this is about how you can drop a column a constraint, or add a new column to the existing table, and then modify an existing column. You can do a lot of things but there are ifs and buts. So in the beginning, I don't recommend you worry too much about other possibilities. But anyway, For more information, please refer to alter table command in Oracle documentation. And it is the largest command you have Hell a lot of stuff with ALTER TABLE. But to know all those options, you need to know a lot about Oracle.

But as a beginner, I don't recommend you worry too much but do understand these commands. Well, that's the way we alter the structure of the table. And now this is about dropping a constraint, you cannot drop a primary key or a unique constraint if there is any dependent foreign key constraint. So the remedy for that is use cascade constraints, which is an option at the time of dropping the constraint. So what you need to do is, as you can see in the syntax, while you're dropping the constraint, you can give cascade constraints. So what it does, look at this diagram in this the students take Trouble is referencing courses table.

So course is the foreign key that is pointing to the course code. Now what if you're trying to remove the primary key constraint from this course code. Once you remove this primary key constraint, the column can be duplicated, it is no longer unique. But the rule for foreign key says foreign key can refer only to a column, which is unique. So either eight must be primary key or eight must be unique. Those are the options you have.

But when you're removing a primary key or unique constraint from the parent column, it cannot be any longer parent for foreign key. So in such cases, you have to tell a racquel to remove the foreign key constraint event from the dependent columns. So, cascade constraints is saying delete all dependent constraints on the column that is right now being made non unique or non primary key, I mean, if you delete primary key or unique constraint from the code, then I want Oracle to go to students remove foreign key constraint from the course column of students table. So, this is mandatory without you giving cascade constraints or racquel cannot delete the primary key or unique constraint of parent table. Well, the same you have to give even in some other commands. drop table is the next command where we can drop the table all together.

It is going to remove the whole table, the data, the structure, everything is good. Again, if you have any dependent tables with constraints referring to the columns in the table being dropped, you have to give cascade constraints only then the constraints will be gone in those dependent tables and the parent table can be dropped. This purge is saying, Do not take the table to recycle bin. So if you're not using this purge option, or racquel is going to put this table in Recycle Bin and it allows you to restart the table using a command called flash you will be able to restore the table at a later stage. Well, if you give purge, then Oracle will not take the table into the recycle bin. So you will not have an option to get the table back at a later stage.

So If you are serious about dropping the table without ever getting it back, you don't want this to occupy any space in the database do give purge, otherwise, leave the option open. So this is about drop table and alter table. Those are the measure commands discussed in this lesson. So we talked about what is data dictionary, how we can use some of the data dictionary views like user constraints, we do have lot more data dictionary views to provide details about the database. Then ALTER TABLE is used to alter the structure of the table, like adding new columns, dropping existing columns, modifying some characteristics of existing columns. cascade constraint is used to remove all the dependent foreign key constraints.

When you are deleting a constraint, like a unique or primary key, or it is also an option we use in the context of DROP TABLE command drop table is to drop the table is to get rid of everything about the table. But you may want to restore the table if you like. In that case just gave DROP TABLE without purge. But if you give purge option, the table is dropped and you can never restore it. Well, that's about how we can alter the structure of a table or drop a table. We will learn more about how to deal with the various other concepts related to data retrieval in the coming lessons.

See you in the next lesson.