Schema manipulation in SQL follows a template similar to that of database manipulation.
Statements and the overall syntax are nearly identical, with the obvious difference that you’ll be targeting schemas instead of databases.
Let’s start by creating a second (temporary) schema in our playground database. The listing below demonstrates the syntax for schema creation.
CREATE SCHEMA { schema_name };
The syntax is very intuitive and very similar to that for database creation. The CREATE SCHEMA command declares our intention to create a new schema, and it concludes with the name of the schema. Below demonstrates this statement in action.
CREATE SCHEMA public2;
If you try to execute this statement, a new schema will be created in the database you’re working on—which should be a playground database while learning. Once you refresh the pgAdmin GUI, you should see the new schema under Schemas, as shown in this figure.
Although this schema is empty at the moment, it can contain data structures, such as tables and views—just like any other schema!
Mind you that relational databases don’t allow namesake objects—if you already have a schema called public2, you won’t be able to create a second one. In such a case, you should pick a new name for your new schema or carefully rename the old one if you must—which is, coincidentally, the subject of our next topic!
We’re going to follow a path similar to database modification in this section. The main difference is that instead of using the ALTER DATABASE command, we’ll use the ALTER SCHEMA command. Because we’re targeting a schema, it’s natural for the command to contain the SCHEMA keyword as well.
Mind you that like database modification, schema modification is not a common daily task. It occurs in relatively rare cases where a fundamental change is needed, such as migration, product retirement, and system redesign.
We were recently talking about renaming schemas. Now is the time to learn the SQL syntax for that. Check out this code listing.
ALTER SCHEMA { existing_name } RENAME TO { new_name };
The syntax should be obvious to someone who can speak English and has gone over SQL statements regarding database manipulation. It’s nearly the same syntax, but we use the SCHEMA keyword instead of DATABASE.
Let’s see it in action! Below demonstrates the SQL statement we use to rename our example schema.
ALTER SCHEMA public2 RENAME TO public3;
Once we execute this statement, the existing public2 schema will be renamed to public3, as shown in this figure.
Our aforementioned rule of namesake components is valid once again: the new name of the schema must be a unique name under the same database, and you can’t have two namesake schemas.
As with databases, each schema has an owner. The owner of the schema differs from other users by having special privileges; some of the significant ones are listed in this table.
Mind you that we have a similar list of privileges for database owners. The natural difference is that while the privileges of the database owner are valid throughout the entire database, including all schemas within it, the privileges of the schema owner are valid within that particular schema only. The figure below contrasts this difference visually.
Back to the topic at hand… To change the owner of an existing schema, we can do that with a simple SQL statement. The syntax can be seen in this code.
ALTER SCHEMA { schema_name } OWNER TO { new_owner };
Evaluating the intuitive syntax; ALTER SCHEMA declares our intention to make a schemalevel change, and OWNER TO declares a change of ownership. Let’s see this command in action by changing the owner of the sample public3 schema.
You’re going to need a second user to hand the ownership to, and to keep this section self-contained, you’re going to create a new user for this purpose. Execute the statement in the listing below, and don’t worry about understanding the statement beyond the fact that it creates a new user called kerems.
CREATE USER kerems WITH PASSWORD '1234';
Now that you have an alternative user, you need to hand the ownership of the schema public3 to them. Below contains the statement for doing just that.
ALTER SCHEMA public3 OWNER TO kerems;
After executing this statement, right-click public3 in pgAdmin and select Properties, as demonstrated in this figure.
If everything goes well, kerems should be displayed as the new database owner, as in this figure.
You might need to refresh pgAdmin to see the change. From now on, kerems will be assumed as the schema owner, and it will own all privileges that come with that title.
Straightforward logic tells us that if we can create or modify a schema, we should be able to delete it as well.
There are risks of deleting a schema. For our brave readers, the listing below showcases the syntax for that purpose.
DROP SCHEMA { schema_name } [ CASCADE | RESTRICT ];
A statement for deleting the schema would start with the command DROP SCHEMA, followed by the name of the schema. So far, so good! If we simply execute this statement for our sample public3 schema, we’ll surely get rid of it. Below demonstrates the appropriate statement for that, which you can try for yourself.
DROP SCHEMA public3;
Once you execute this statement, the example schema will disappear from the schema list in pgAdmin, as demonstrated in this figure.
Let’s take a look a closer look at the syntax in the following sections.
You may have noticed that the syntax from earlier has an optional keyword: CASCADE. What is this mysterious keyword good for?
A relational database is a structure of interdependent components, and even in the most basic sense, tables refer to each other by using foreign keys. Ignoring recommendations and best practices, it’s technically possible for tables from two different schemas to have a foreign key relationship.
The figure below contains an ERD with two distinct schemas: hr is the schema of the human resources department, and canteen is the schema of the company canteen.
The hr.employee table contains employee master data, and the canteen.order table contains the order history of employees. Although they belong to different schemas, it’s natural that canteen.order.employee_id refers to hr.employee.id as its foreign key.
We don’t intend to assert that having relationships between tables in different schemas is a good practice. It’s merely a technical possibility, and in many cases, you may want to keep schemas completely isolated.
Now, let’s assume that the HR department will start using another software system and we don’t need the hr schema any longer. You’ve been instructed to delete this schema, so naturally, you’ll apply the SQL commands you recently learned and execute this statement:
DROP SCHEMA hr;
And you’ll be unsuccessful. The database will return an error message instead, indicating that the schema has dependencies and can’t be deleted—and it’ll be right! There’s a dependency between hr.employee.id and canteen.order.employee_id, so if you simply delete the hr schema, this dependency will become inconsistent.
This may look like an obstacle to you, but actually, it’s a beautiful security feature! Relational databases don’t let us simply delete stuff at will if there are such dependencies. This reduces human mistakes and ensures database-wide consistency.
But is it impossible to delete the hr schema, then? Are we stuck with it forever? Of course not!
We have two alternatives for getting rid of hr. The first alternative is to follow a two-step plan:
This may seem simple in such a basic scenario, but imagine a schema with dozens of tables and relationships! Manual removal of all relationships would be a cumbersome task and take a long time.
Thus, we have the second alternative. We can simply delete the schema with the optional CASCADE keyword, as in this listing:
DROP SCHEMA hr CASCADE;
Having the CASCADE keyword in the statement tells the database to delete all dependencies automatically and then delete the schema as well. Practical, eh?
No matter which deletion method you pick, you need to be mindful of orphan data. After you delete the hr schema, rows in canteen.order will become orphans because canteen.order.employee_id doesn’t refer to a table any longer. Those are just numbers instead of meaningful employee IDs now.
If this were a migration to a new schema, canteen.order.employee_id would have to point to the new schema.table at some point very soon.
Our syntax earlier has a second optional keyword, which we ignored momentarily: RESTRICT. This keyword is actually the default behavior of the command. In other words, the statements in this listing are identical and would execute in exactly the same way.
DROP SCHEMA hr;
DROP SCHEMA hr RESTRICT;
But if you’re using another database product, the default behavior may be different. So, to be on the safe side and to express your intention more clearly, it’s advisable to add the optional RESTRICT or CASCADE keyword explicitly when coding SQL in a production environment.
Editor’s note: This post has been adapted from a section of the book SQL: The Practical Guide by Kerem Koseoglu. Dr. Koseoglu is a seasoned software engineer, author, and educator with extensive experience in global software development projects. In addition to his expertise in ABAP, he is also proficient in database-driven development using Python and Swift. He is the author of Design Patterns in ABAP Objects (SAP PRESS), as well as several best-selling technical books in Turkey. He has a Ph.D. in organizational behavior.
This post was originally published 4/2025.