Postgres Composite Primary Key

In this article, the concept of the composite primary key will be discussed in PostgreSQL. A primary key is a field assigned to a row with unique values in a database’s table, but when a table has more than one unique value, we use a composite primary key for these attributes that will set them apart for distinction. PostgreSQL allows its users to have composite primary keys in their tables. We will discuss several examples of the composite primary key concept in PostgreSQL to better understand this concept.

Composite Primary Key’s Syntax in PostgreSQL

Before we jump straight to implementing the composite primary key concept, we should know about the syntax for making 2 or more attributes a primary key in the table. So, a composite primary key is declared just like a normal primary is declared when we are creating a table. The syntax for composite primary key is described below, along with its characteristics or column names:

In this query, we are initializing a table with several columns, and instead of inline single primary key initialization for the columns, we are separately initializing them after we have defined the column’s name and column’s datatype. We use the “PRIMARY KEY” keyword with a bracket in which we write the columns’ names separated with commas to specify them as a composite primary key.

Initializing a Composite Primary Key in PostgreSQL

As the syntax is familiar to us now, we can look at some examples for creating a table with multiple primary keys. So first, we will open our query editor and create a table.

Now, we can create a table out of this query, but there’s a problem in this table as no column has been specified for being a primary key. In this table, there can be more than one primary key as per the needs, like what if the salary has to be added with bonuses to certain employees with certain types and names, so they all have to be a primary key. What if we initialize each of them as a primary key separately? Let us see how this turns out when we perform this in PostgreSQL.

The output is attached in the appended image.

As the output suggests that we cannot create more than one primary key in our tables if we use the one-liner method. This method is not allowed in the PostgreSQL environment and can only be used when we have to declare only one column as a primary key. So now, we will look at the correct method for declaring more than one primary key in a table in PostgreSQL.

Declaring Two Columns as the Primary Key

In this situation, we will make two columns of the table to be primary keys at the same time. We will make the id column a primary key and the type of employee column a primary key in our table. We must construct this query as follows for it to run successfully:

The output is attached in the appended image.

As you can see that the output suggests that the table was created successfully, and we can use it for inserting values. Now let us verify that in our Postgres environment.

The output is attached in the appended image.

As you can see, we have successfully created a table and inserted values in it while we had two primary keys assigned to the columns of the table. So, in this table, the primary keys are “e_id” and “e_type”, and we have defined them as unique attributes for the table named “Employee1”.

Declaring Three Columns as Primary Key

In this situation, we will make three columns of the table to become primary keys at the same time. We will make the id column a primary key, the name of the employee will be set as primary key, and as well the type of employee column to be a primary key in our table. To make this query execute successfully, we will need to put it together like this:

The output is attached in the appended image.

As you can see that the output suggests that the table was created successfully, and we can use it for inserting values. Now let us verify whether the insertion is applicable in our Postgres environment or not.

The output is attached in the appended image.

As you can see, we successfully created a table and entered data into it while assigning three primary keys to the table’s columns. So, in this table, the primary keys are “e id”, “e type”, and “e name”, and we have designated them as a unique attribute for the table “Employee1”.

Declaring All Columns as Primary Key

In this situation, we will make all four columns of the table to be primary keys at the same time. For this query to run successfully, we have to write like this as shown below:

The output is attached in the appended image.

As you can see, the result indicates that the table was successfully created, and we can now use it to insert values. Now let’s see whether the insertion works in our Postgres environment.

The output is attached in the appended image.

As you can see, we created a table, filled it with data, and assigned four primary keys to the table’s columns. The primary keys in this table are “e_id”, “e_type”, “e_name”, and “e_sal”. They have been declared as a unique attribute for the table  “Employee1″.

We have concluded that PostgreSQL allows us to have more than one primary key in our table. We can scale it up to as many columns as possible using the composite primary key function or even assigning the primary key uniqueness to all the columns of the table.

Conclusion

In this article, we have learned about the concept of Composite Primary Key in PostgreSQL. So, if we counter a situation in which we have to declare more than one primary key, we can use the composite primary key function to that situation with the help of this article. The syntax for the composite primary key declaration was also discussed in a brief detail in Postgres, as all the aspects of the function were discussed in parts. Then we also implemented this concept in the PostgreSQL environment. The correct way to declare two, three, or even more than 3 primary keys in a single table in PostgreSQL is by using the composite primary key function.