PostgreSQL – Primary Key Constraint

ByAdmin

In PostgreSQL, a primary key constraint is a way to enforce the uniqueness of a column or a group of columns in a table.

A primary key constraint is created using the PRIMARY KEY keyword after the column or group of columns that you want to use as the primary key. For example, to create a primary key on the id column of a table named mytable, you can use the following SQL statement:

CREATE TABLE mytable (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50)
);

In this example, the id column is defined as the primary key of the mytable table. This means that the values in the id column must be unique for each row in the table, and the column cannot contain null values.

If you try to insert a row with a duplicate value in the primary key column, PostgreSQL will return an error.

You can also define a primary key constraint on a group of columns by listing them in the PRIMARY KEY clause. For example:

CREATE TABLE mytable (
  id INTEGER,
  name VARCHAR(50),
  age INTEGER,
  PRIMARY KEY (id, name)
);

In this example, the combination of id and name columns must be unique for each row in the mytable table.

If a table already exists and you want to add a primary key constraint to it, you can use the ALTER TABLE statement with the ADD PRIMARY KEY clause. For example: 

ALTER TABLE mytable ADD PRIMARY KEY (id);

If you need to drop a primary key constraint from a table, you can use the ALTER TABLE statement with the DROP CONSTRAINT clause. For example: 

ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;

This statement will drop the primary key constraint from the mytable table. Note that you need to specify the name of the primary key constraint, which is automatically generated by PostgreSQL when you define the primary key constraint using the PRIMARY KEY keyword.

You can also create a foreign key constraint that references a primary key in another table. This is a common way to establish relationships between tables in a database. For example: 

CREATE TABLE orders 
( id INTEGER PRIMARY KEY, 
customer_id INTEGER, ... 
FOREIGN KEY (customer_id) REFERENCES customers (id) );

In this example, the orders table has a foreign key constraint on the customer_id column that references the primary key of the customers table. This ensures that every value in the customer_id column of the orders table corresponds to a valid id value in the customers table.

A primary key constraint can be defined on one or more columns. If a primary key is defined on multiple columns, it is called a composite primary key. Example: 

CREATE TABLE mytable 
( id INTEGER, 
name VARCHAR(50), 
PRIMARY KEY (id, name) 
);

In this example, the id and name columns together form a composite primary key for the mytable table. This means that the combination of values in these columns must be unique for each row in the table.

When you define a primary key constraint on a table, PostgreSQL automatically creates a unique index on the columns that make up the primary key. This index is used to enforce the uniqueness constraint and can also be used to optimize queries that involve the primary key columns.

Similar Posts