Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

SQL Constraints

Introduction
Primary keys
6. Deleting primary keys
Foreign keys
Updating and deleting with foreign keys
Summary

Instruction

Excellent! Now, how can we remove a primary key from a table? We have this table definition:

CREATE TABLE tennis_court (
  id integer PRIMARY KEY,
  address text,
  type varchar(32)
);

We can use the following instruction:

ALTER TABLE tennis_court
DROP CONSTRAINT tennis_court_pkey;

As we said before, primary keys are a kind of constraint; that's why we use the syntax the DROP CONSTRAINT. You may be wondering where tennis_court_pkey name came from. Internally, database engines store primary keys as uniquely-named constraint objects. By default, PostgreSQL uses the naming convention table_name + _pkey. That's why the primary key in table tennis_court was named tennis_court_pkey. Naturally, if you provide your own constraint name when adding a primary key, you'll have to use the same custom name to delete the constraint.

Remember that other databases may use different naming conventions. Always check the documentation of your database for more details.

Exercise

The company size increased; there are so many departments now that the company wants to introduce a unique id column to the department table. Before it's marked as the primary key, we need to get rid of the existing primary key. The table has the following definition:

CREATE TABLE department (
  country varchar(32),
  name varchar(32),
  address varchar(64),
  PRIMARY KEY (country, name)
);

Remove the primary key from the table.