Как изменить primary key oracle

In this Oracle tutorial, we will learn how to add the primary key in oracle 19c and 21c. Also, we will cover the following topics in this tutorial.

• Oracle how to add primary key
• Oracle add primary key syntax
• Oracle how to add column to primary key
• Oracle how to change primary key
• Oracle add primary key using index
• Oracle add primary key sequence
• Oracle add primary key to existing table with data
• Oracle add constraint primary key novalidate
• Oracle add composite primary key
• Oracle add primary key constraint multiple columns
• Oracle add primary key to materialized view

A primary key in Oracle identifies each row uniquely in the table. There are two major properties of primary key:

• Primary key can’t be empty
• Primary key must be unique

While fetching information from the table, an algorithm searches each row and column to find the provided input from the table which is an inefficient way as it takes time and resouces.

The primary key identifies each row uniquely when it is included in the search query then the algorithm knows in which row it has to look for. This way it takes less time and resources.

Oracle add primary key syntax

In this section, we will see the syntax for adding the primary key in the oracle table. Primary key can be added in two ways:-

• Add Primary Key Inline
• Add Primary Key using Constraint

Add Primary Key inline :-

While creating a table, mention the primary key constraint infront of the column that you want to create as a primary key column. Professionals prefer to use other options but this method is great for those who are learning.

Below is the example to add a primary key in a table using an inline method in oracle databases 19c and 21c.

-- Inline 
CREATE TABLE employee(
    emp_id INTEGER PRIMARY KEY,
    emp_name VARCHAR2(50),
    emp_sal FLOAT);

Add Primary Key using Constraint :-

This method is most preferable by the professionals due to the feature of creating a name for this primary key PK_EMP_TABLE in this case.

-- Using constraint
CREATE TABLE employee(
    emp_id INTEGER,
    emp_name VARCHAR2(50),
    emp_sal FLOAT,
    CONSTRAINT PK_EMP_TABLE PRIMARY KEY (COLN_1));

Oracle how to add column to primary key example

In this section, we have created a table CountryEvents, where we have displayed names of festivals, events, and their origin country name. We have created fest_id as the primary key in a table.

-- CREATE TABLE
CREATE TABLE COUNTRY_EVENTS(
    FEST_ID INTEGER,
    FEST_NAME VARCHAR2(100),
    ORIGIN_COUNTRY VARCHAR2(100),
    Constraint PK_CO_EVE PRIMARY KEY (FEST_ID)


);

-- INSERT DATA 
INSERT INTO COUNTRY_EVENTS 
VALUES(101, 'New Orleans Mardi Gras', 'UNITED STATES OF AMERICA');

INSERT INTO COUNTRY_EVENTS 
VALUES(102, ' Aloha Festival', 'UNITED STATES OF AMERICA');

INSERT INTO COUNTRY_EVENTS
VALUES(103, 'Hogmanay: New Year In Scotland', 'UNITED KINGDOM');

INSERT INTO COUNTRY_EVENTS
VALUES(104, ' Oktoberfest', 'GERMANY');

INSERT INTO COUNTRY_EVENTS
VALUES(105, 'FANTASY FEST', 'UNITED KINGDOM');

INSERT INTO COUNTRY_EVENTS
VALUES(106, 'Jorvik Viking Festival', 'GERMANY');


-- COMMIT 
COMMIT;

-- VIEW DATA 
SELECT * FROM COUNTRY_EVENTS;

Output:

In the below output, a table is created with FEST_ID as a primary key.

Also, check: How to create table in Oracle

Oracle how to change primary key

The primary key shouldn’t be changed in the oracle database because if it changes in one table then it has to be changed in other tables as well.

It is recommended to add a primary key on a column that doesn’t have any meaning so that it can be used only for identifying a row in a table.

• Suppose there is a requirement of adding a new column to an existing table and now we have to shift the primary key from previous column to the new column.
• Before changing take a note of all the columns in which current primary key is used as a foreign key.
• In oracle database 19c & 21c using Alter Table command we can change primary key in the table.
• In the previous section – Oracle how to add column to primary key example we have created a table and assigned a primary key constraint with the name PK_CO_EVE. We will use the same constraint name in our below examples.

Example:

In this example, first, we have shared the script using which you can remove the primary key constraint from the table. Here PK_CO_EVE is the name of the primary key constraint that we created in the previous section.

ALTER TABLE COUNTRY_EVENTS 
DROP CONSTRAINT PK_CO_EVE

The next script shows how to add more columns as the primary key in the oracle database. You can use this code to change the primary key as well. We have used a few imaginary columns (Fid, country_id) on which we are going to add a primary key constraint.

ALTER TABLE COUNTRY_EVENTS
ADD CONSTRAINT PK_CO_EVE PRIMARY KEY (FID, COUNTRY_ID)

Read: Alter Table Add Column Oracle

Oracle add primary key using index

Every time a primary key constraint is added to the column, an index is automatically created in oracle database 19c. One primary key can have multiple indexes but only one can be set to visible at a time.

Use the below command to view the index name and visibility on the primary key in the oracle database. Instead of Players put in the name of your table name.

SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
WHERE TABLE_NAME='PLAYERS';

More indexes can be created for the same primary key table but it is important to set the previous index to invisible. Use Alter command to change the visibility of the primary key column. In the below code, PK_PLAYERS is the index name.

ALTER INDEX PK_PLAYERS INVISIBLE;

Use CREATE INDEX statement in oracle to create a new index for the specified column. The statement requires the index and column name of the table. In the below code, NEW_INDEX is the name of the index and Id is the name of the primary key column.

CREATE INDEX NEW_INDEX ON PLAYERS(ID);

Example:

In the below example, we have created a table Players with a primary key on the ID column. The scripts to view the constraints and indexes information is shared below. We have created a new index on the primary key column. Read the inline comments for clarification.

-- CREATE TABLE 
CREATE TABLE PLAYERS(
    ID INTEGER,
    NAME VARCHAR2(50),
    RANK INTEGER,
    CONSTRAINT PK_PLAYERS PRIMARY KEY(ID)
);

-- VIEW CONSTRAINT INFORMATION
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME='PLAYERS';

-- VIEW INDEXES & VISIBILITY
SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
WHERE TABLE_NAME='PLAYERS';

-- CHANGE VISIBILITY 
ALTER INDEX PK_PLAYERS INVISIBLE;

-- CREATE NEW INDEX
CREATE INDEX NEW_INDEX ON PLAYERS(ID);

Read: Oracle Database vs MySQL Database

Oracle add primary key sequence

It becomes inconvenient to fill the data every time for a primary key column. As you have to look for the previous value first then fill the next value in an oracle database table.

Using the oracle primary key sequence feature you can automate this process. Next time while inserting data you no need to worry about the primary key column as that will be automatically inserted.

Here is the syntax for creating a sequence in oracle database 19c & 21c. It is not mandatory to provide all the available options in sequence.

-- create sequence
CREATE SEQUENCE <SEQ_NAME>
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99999
CYCLE/NOCYCLE
CACHE 20;


-- delete sequence
DROP SEQUENCE <SEQ_NAME>

• Sequence can’t be created with CREATE SEQUENCE OR REPLACE <SEQ_NAME> , CREATE SEQUENCE OR ALTER <SEQ_NAME>, etc.
• It can be created and deleted only with the above mentioned syntax in oracle database 19c & 21c.
• .NEXTVAL method is used to retrieve the next number in the sequence.
• <SEQ_NAME>.NEXTVAL don’t get affected by the ROLLBACK command in oracle database 19c & 21c.If at the time of rolling back the sequence value was 7 so even after rollback it will continue from 7 which means next value will be 8.
• Use <SEQ_NAME>.NEXTVAL with the default to automatically generate values for that column.
• You can provide Null values during the sequence by manually inserting the null value.

Refer to our blog – Oracle Create Table with Sequence Primary Key

Oracle add primary key to existing table with data

Using the oracle Alter Table command primary key can be added to an existing table with data. But while doing so you will get the below error.

Error report -
ORA-01758: table must be empty to add mandatory (NOT NULL) column
01758. 00000 -  "table must be empty to add mandatory (NOT NULL) column"

The reason for this error is since the table already has data now when you are adding a column with a primary key constraint then these rows will be null but the primary key doesn’t allow null values.

Follow these steps to add primary key to existing table with data:

Step 1: Add a new column using Alter Table statement but without primary key constraint.

Step 2: Add data to a newly created column. Use UPDATE AGENT set AGENT_ID = ROWNUM; if you want to assign a row number.

Step 3: Add a primary key constraint to the column. Use Alter Table statement either with Add Constraint option or with Modify.

Example:

In the below example, we have created a table, inserted data, and then added a new column with data, and then converted the new column to primary key constraint.

-- CREATE TABLE
CREATE TABLE AGENT(
    AGENT_NAME VARCHAR2(100)
)

-- INSERTING DATA
INSERT INTO AGENT VALUES('JAMES BOND');
INSERT INTO AGENT VALUES('Julius');
INSERT INTO AGENT VALUES('Ethel Rosenberg');
INSERT INTO AGENT VALUES('Jonathan');

-- VIEW DATA
SELECT * FROM AGENT;

-- ADD PRIMARY KEY COLUMN
ALTER TABLE AGENT
ADD AGENT_ID INTEGER;

-- ADD DATA 
ALTER TABLE AGENT ADD AGENT_ID INTEGER;

-- UPDATE 
UPDATE AGENT set AGENT_ID = ROWNUM;

-- ADD PRIMARY KEY CONSTRAINT
ALTER TABLE AGENT
ADD CONSTRAINT PK_AGENT PRIMARY KEY(AGENT_ID);

-- VIEW DATA 
SELECT * FROM AGENT;

Read: Number Datatype in Oracle Database

Oracle add constraint primary key novalidate

Enable Novalidate method in oracle database 19c & 21c ignores the violation and responds normally to the query. This function understands that you are aware of the possible error and you will fix it in the future.

• Suppose you are giving presentation to the client, though you are confident about the code and already fixed all the bugs, errors and loose ends.
• Still to stay on the safer side novalidate option in the code.
• This way, no unexpected exception will occur and your program will run as per your expectations.

CREATE TABLE ADMIN(
    ADMIN_ID INTEGER,
    FIRST_NAME VARCHAR2(100),
    LAST_NAME VARCHAR(100),
    USERNAME VARCHAR(100) NOT NULL ENABLE NOVALIDATE,
    PASSWORD VARCHAR(100) NOT NULL ENABLE NOVALIDATE
);

Read: Connect to Oracle Database using Python

Oracle add composite primary key

In a table, multiple columns satisfy the eligibility criteria for a primary key. These columns are called composite key(s).

In other words, when in a table more than one column is assigned a primary key constraint then together these columns are called composite keys.

In this section, we will learn how to add composite primary keys in oracle databases 19c & 21c with an example.

In the below script, we have created a table America got talent and this table has the detail of all the participants. Columns P_ID, P_EMAIL, P_MOB are composite primary keys.

CREATE TABLE AMERICA_GOT_TALENT(
    P_ID INTEGER,
    P_NAME VARCHAR2(100),
    P_EMAIL VARCHAR2(100),
    P_MOB INTEGER,
    P_SCORE FLOAT,
    CONSTRAINT PK_COMPOSITE_KEY PRIMARY KEY (P_ID, P_EMAIL, P_MOB)
);

Read: How to create a database in Oracle 19c

Oracle add primary key constraint multiple columns

In a table, all the columns with the unique values are eligible to become a primary key but only one is called a primary key remaining are called a composite key.

Syntax:

Here is the syntax for adding primary key constraint on multiple columns in oracle database 19c & 21c:

CREATE TABLE <Table_name>(
    <col_1>  [data type],
    <col_2>  [data type],
    <col_3>  [data type],
    <col_4>  [data type],
    CONSTRAINT <constraint_name> PRIMARY KEY(col_1, col_2)
);

Example:

In our example, we have created a table Players which has 3 primary key columns (player_id, match_id, team_id). The bold line shows how to add a primary key to multiple columns in the oracle database.

CREATE TABLE PLAYERS(
    PLAYER_ID INTEGER,
    MATCH_ID INTEGER,
    TEAM_ID INTEGER,
    PLAYER_NAME VARCHAR2(50),
    COUNTRY VARCHAR2(50),
    GAME_NAME VARCHAR2(100),
    CONSTRAINT PK_PLAYERS PRIMARY KEY (PLAYER_ID, MATCH_ID, TEAM_ID)
);

Read: How to Fetch Data from Oracle Database in Python

Oracle how to add primary key to materialized view

A materialized view is a database object that stores query output and precomputed results. When there is a huge data then this process saves time involved in fetching the data.

There are mainly two reasons of using materialized view in oracle database 19c.

• To maintain the local copy for remote database object.
• To improve the performance of complex queries like join.

Below is the demonstration of oracle how to add a primary key to materialized view in oracle database 19c and 21c.

-- CREATE TABLE
CREATE TABLE EMP(
    deptno integer,
    empno integer, 
    ename varchar2(100),
    job varchar2(20)
);

-- CREATE TABLE
CREATE TABLE BACKUP AS
    SELECT deptno, empno, ename, job
    FROM EMP;


-- CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW MTV AS
    SELECT deptno, empno, ename, job
    FROM BACKUP 


-- ADD PRIMARY KEY TO MATERIALIZED VIEW
ALTER TABLE MTV 
ADD CONSTRAINT PK_MTV primary key (empno);

You may also like to read the following Oracle tutorials.

• Connect Excel to Oracle Database
• How to Check Oracle Database Version
• How to Get List all Tables in Oracle
• Oracle to_date Format [Complete Guide]

In this tutorial, we have learned how to add the primary key in oracle 19c and 21c. Also, we have covered these topics.

• Oracle add Primary key
• Oracle add primary key syntax
• Oracle how to add primary key
• Oracle how to change primary key
• Oracle how to add column to primary key
• Oracle add primary key using index
• Oracle add primary key sequence
• Oracle add primary key to existing table with data
• Oracle add constraint primary key novalidate
• Oracle add composite primary key
• Oracle add primary key constraint multiple columns
• How to add primary key to materialized view

The Oracle ALTER TABLE statement allows you to make changes to an existing table. Learn how to use it and see some examples in this guide.

This article applies to Oracle, SQL Server, MySQL, and PostgreSQL.

What Is The SQL ALTER TABLE Statement?

The SQL ALTER TABLE statement lets you change a table that has already been created.

Using the CREATE TABLE statement lets you create a table, but using ALTER TABLE lets you change it without dropping the table and recreating it.

What Can and Can’t You Do with the ALTER TABLE Statement?

There are many things you can do with the SQL ALTER TABLE statement and some that you can’t.

With this command, you can:

• Add one or more columns to a table
• Change the data type of one or more columns
• Add a constraint to a column
• Drop a column from a table
• Rename a column
• Rename a table
• Much more

For now, this article will focus on the most common uses for ALTER TABLE. Over time, I’ll update this article to add more information to it.

ALTER TABLE Syntax

The syntax of the SQL ALTER TABLE command is:

ALTER TABLE [schema.]tablename alter_table_clause;

There are many different clauses, which we’ll discuss in this article, along with some examples.

ALTER TABLE Add Column

Sometimes, you might want to add a column to a table. You can do this without dropping and creating the table by using the ALTER TABLE SQL statement.

The ALTER TABLE syntax to do this is:

ALTER TABLE table_name
ADD [COLUMN] column_name column_definition;

The parameters are:

• table_name: the name of the table you’re changing.
• column_name: the name of the new column you’re adding.
• column_definition: information about the new column you’re adding.

Some things to note about this command are:

• You don’t need to specify ADD COLUMN. Just the ADD word is enough.
• Column names need to be less than the maximum number of allowed characters, which is 128 for Oracle and SQL Server, 64 for MySQL, and 63 for PostgreSQL.

The column_definition is similar to that from the CREATE TABLE statement. It can contain a few things:

• The data type and precision of the new column
• Any constraints that apply to the table (e.g. NOT NULL)

Examples

Let’s see some examples of the SQL ALTER TABLE Add Column functionality.

This example adds a new column called favourite_subject to the student table.

ALTER TABLE student
ADD favourite_subject VARCHAR2(100);

ALTER TABLE student
ADD favourite_subject VARCHAR(100);

ALTER TABLE student
ADD favourite_subject VARCHAR(100);

ALTER TABLE student
ADD favourite_subject VARCHAR(100);

I’ve made it a VARCHAR (or VARCHAR2) column with 100 characters, so it can store a text value for a subject name here.

All existing values will have a value of NULL for this field.

Let’s see another example using a different data type and a constraint.

This example adds a new column called difficulty_rating.

ALTER TABLE subject
ADD difficulty_rating NUMBER(2) NOT NULL;

ALTER TABLE subject
ADD difficulty_rating INT NOT NULL;

ALTER TABLE subject
ADD difficulty_rating INT(2) NOT NULL;

ALTER TABLE subject
ADD difficulty_rating INTEGER NOT NULL;

This new column is a number data type. I’ve also marked it as NOT NULL, which means it needs a value.

What happens to the existing values in a table, if you add a column with NOT NULL?

You will get an exception because the existing rows will have a value of NULL.

To avoid this, specify a default value.

ALTER TABLE subject
ADD difficulty_rating NUMBER(2) NOT NULL DEFAULT 99;

ALTER TABLE subject
ADD difficulty_rating INT NOT NULL DEFAULT 99;

ALTER TABLE subject
ADD difficulty_rating INT(2) NOT NULL DEFAULT 99;

ALTER TABLE subject
ADD difficulty_rating INTEGER NOT NULL DEFAULT 99;

ALTER TABLE Add Multiple Columns

You can add multiple columns to a table using a single ALTER TABLE SQL command. This is better for performance than running separate statements. It works on Oracle, SQL Server, MySQL, and PostgreSQL.

To do this, just separate your columns with commas and include them in brackets.

The syntax for adding multiple columns to a table is:

ALTER TABLE table_name
ADD (column_name column_definition,
column_name2 column_definition2,
...
column_name_n, column_definition_n);

ALTER TABLE table_name
ADD column_name column_definition,
column_name2 column_definition2,
...
column_name_n, column_definition_n;

ALTER TABLE table_name
ADD column_name column_definition,
column_name2 column_definition2,
...
column_name_n, column_definition_n;

ALTER TABLE table_name
ADD column_name column_definition,
column_name2 column_definition2,
...
column_name_n, column_definition_n;

The syntax is the same between these four vendors, except for Oracle where brackets need to surround the column definitions.

This works in the same way as the ALTER TABLE command for a single column, except you can specify multiple column names, each with their own definition.

An example of this would be:

ALTER TABLE student
ADD (
  favourite_subject VARCHAR2(100),
  date_of_birth DATE,
  login_id NUMBER(6)
);

ALTER TABLE student
ADD (
  favourite_subject VARCHAR(100),
  date_of_birth DATE,
  login_id INT
);

ALTER TABLE student
ADD (
  favourite_subject VARCHAR(100),
  date_of_birth DATE,
  login_id INT(6)
);

ALTER TABLE student
ADD (
  favourite_subject VARCHAR(100),
  date_of_birth DATE,
  login_id INTEGER
);

ALTER TABLE DROP COLUMN

To remove a column from an existing table in SQL, you use the ALTER TABLE DROP COLUMN command. It’s part of the ALTER TABLE command and uses the standard “drop” word from removing something from an object.

The syntax for dropping a column is:

ALTER TABLE tablename
DROP [COLUMN] column_1 [, column_n] [CASCADE CONSTRAINTS] ;

ALTER TABLE tablename
DROP [COLUMN] column_1 [, column_n];

ALTER TABLE tablename
DROP [COLUMN] column_1 [, column_n] ;

ALTER TABLE tablename
DROP [COLUMN] column_1 [, column_n] [RESTRICT | CASCADE] ;

Some things to note about this syntax:

• After the tablename, you have the word DROP.
• The word COLUMN after the DROP is optional.
• If you specify the COLUMN keyword, you don’t need brackets for the column names.
• If you don’t specify the COLUMN keyword, you must specify brackets for the column names.
• You can specify more than one column to drop. Just use commas to separate the columns.

In Oracle, If there are columns that refer to the dropped columns using constraints, then you need to specify CASCADE CONSTRAINTS at the end of the statement. If this is not done, you will get an error. If you don’t have any columns that have these referential constraints, then you don’t need the CASCADE CONSTRAINTS clause.

In PostgreSQL, there are two parameters at the end of the statement:

• CASCADE: Automatically drop objects that depend on the table (such as views) and then all objects that depend on those objects.
• RESTRICT: Refuse to drop the table if any objects depend on it. This is the default.

Here are some examples of using the ALTER TABLE DROP COLUMN command to drop columns:

ALTER TABLE student DROP COLUMN favourite_subject;

ALTER TABLE student DROP (favourite_subject);

ALTER TABLE student DROP (favourite_subject, difficulty_rating);

ALTER TABLE student DROP COLUMN (favourite_subject, date_of_birth, login_id);

There are some restrictions on dropping columns from a table:

• You can’t drop a column that’s part of the partition key for a partitioned table.
• You can’t drop a column that’s part of the primary key

ALTER TABLE MODIFY COLUMN or ALTER COLUMN

You can also use the ALTER TABLE command to modify a column on a table.

The syntax for this is:

ALTER TABLE tablename MODIFY column_name data_type;

ALTER TABLE tablename MODIFY column_name data_type;

ALTER TABLE tablename MODIFY column_name data_type;

ALTER TABLE tablename ALTER column_name data_type;

Notice that PostgreSQL uses the ALTER keyword and other vendors use the MODIFY keyword.

You can also modify multiple columns in a single statement:

ALTER TABLE tablename MODIFY (
column_1 data_type_1
...
column_n data_type_n
);

The ALTER TABLE MODIFY COLUMN (or ALTER TABLE ALTER COLUMN) command allows you to make the following kinds of changes:

• Change the data type of an existing column
• Add constraints to existing columns (such as NOT NULL)
• Set the default value

To rename a column, you use the ALTER TABLE RENAME COLUMN command, which is detailed below.

Let’s see some examples of this.

ALTER TABLE student MODIFY COLUMN favourite_subject NUMBER(5);

ALTER TABLE student MODIFY COLUMN favourite_subject VARCHAR2(500);

ALTER TABLE student MODIFY COLUMN (
  favourite_subject NUMBER(10),
  date_of_birth VARCHAR2(8)
);

ALTER TABLE student MODIFY COLUMN favourite_subject NOT NULL;

ALTER TABLE RENAME COLUMN

If you want to rename a column, there is a specific command for that – ALTER TABLE RENAME COLUMN.

It’s a simple command to run, but it can be hard to remember.

The syntax for doing this is:

ALTER TABLE table_name RENAME COLUMN column1 TO column2;

Some examples of this would be:

ALTER TABLE student RENAME COLUMN favourite_subject TO favourite_subject_id;

ALTER TABLE student RENAME COLUMN last_name TO surname;

These two statements will rename the columns inside the student table.

This works for Oracle, MySQL, and PostgreSQL. In SQL Server, to rename a column you need to use the sp_rename function.

ALTER TABLE ADD PRIMARY KEY

Another thing you can do with the ALTER TABLE command is add primary keys.

You can’t add a primary key using the MODIFY COLUMN command because this is done using the ALTER TABLE ADD PRIMARY KEY syntax.

You can actually add all kinds of constraints using this syntax.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (columns) [parameters];

Some things to note about this syntax:

• You can add all five types of constraints using this syntax (Primary Key, Foreign Key, Not Null, Unique, and Check Constraint)
• The parameters at the end will depend on the type of constraint you’re adding
• You can add a constraint name which is recommended, as it makes it easier to work with constraints.

Let’s see some examples of adding a primary key.

ALTER TABLE student
ADD CONSTRAINT pk_student_stdid PRIMARY KEY (student_id); 

In this example, I’ve added a primary key called “pk_student_stdid” on the student_id column in the student table.

I like to name my constraints using a consistent naming standard. The pk stands for Primary Key, then an underscore, then the table name, then another underscore, then a short version of the column.

ALTER TABLE ADD FOREIGN KEY

You can add a foreign key to a table using the ALTER TABLE command.

It works similar to adding a primary key, and similar to the foreign key command within the CREATE TABLE command.

The syntax is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (columns) [parameters]

So, to add a foreign key, your statement could look like this:

ALTER TABLE student
ADD CONSTRAINT fk_student_addid
FOREIGN KEY (address_id) REFERENCES address(address_id);

This statement will add a foreign key on the address_id field in the student table. It will point to the address_id column in the address table.

ALTER TABLE DROP CONSTRAINT

To drop a constraint using the ALTER TABLE command, you use a statement like this:

ALTER TABLE tablename
DROP constraint_type constraint_name [CASCADE];

Just like with many of the other examples, you start with ALTER TABLE and then specify the table name.

Then, you add the word DROP, and then the constraint type. This can be the constraint type if you know it (such as PRIMARY KEY or UNIQUE), or just the word CONSTRAINT.

You then specify the name of the constraint to drop.

Finally, the optional CASCADE keyword can be provided when dropping primary keys, and it will instruct Oracle to drop any foreign keys that reference the primary key.

An example of the ALTER TABLE DROP CONSTRAINT command is:

ALTER TABLE student
DROP CONSTRAINT fk_address_addid;

Or, we can use the constraint type directly:

ALTER TABLE student
DROP PRIMARY KEY pk_student_id;

ALTER TABLE RENAME TABLE

Another task you can do with the ALTER TABLE command is renaming tables.

The syntax for renaming a table is:

ALTER TABLE tablename RENAME TO new_tablename;

It’s a simple statement, but it’s easy to forget the syntax for it.

An example of this command is

ALTER TABLE student RENAME TO person;

Another example could be:

ALTER TABLE subject RENAME TO old_subject;

This works for Oracle, MySQL, and PostgreSQL. In SQL Server, to rename a table you need to use the sp_rename function.

Summary of Differences Between Each Vendor

This table summarises the differences with ALTER TABLE for each database vendor:

Conclusion

So, that brings us to the end of the guide to the ALTER TABLE statement. It’s a powerful statement that has a lot of options.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!