#POSTGRESQL APPILY 2 PRIMARY KEYS HOW TO#

You can create a primary key in PostgreSQL with the ALTER TABLE statement. So each combination of order_date and customer_id must be unique in the order_details table.Ĭreate Primary Key - Using ALTER TABLE statement This example creates a primary key called order_details_pk that is made up of a combination of the order_date and customer_id columns. We could also create a primary key with more than one field as in the example below: CREATE TABLE order_detailsĬONSTRAINT order_details_pk PRIMARY KEY (order_date, customer_id) It consists of only one column - the order_detail_id column. In these two examples, we've created a primary key on the order_details table called order_details_pk. ( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY, Or you could also create the primary key on the order_details table using the following syntax: CREATE TABLE order_details CREATE TABLE order_detailsĬONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)

#POSTGRESQL APPILY 2 PRIMARY KEYS HOW TO#

Let's look at an example of how to create a primary key using the CREATE TABLE statement in PostgreSQL. index_col_n The columns that make up the primary key. constraint_name The name of the primary key. See the PostgreSQL CREATE TABLE statement for more detailed CREATE TABLE syntax as this is an over-simplification to demonstrate how to create a Primary Key. column1, column2 The columns that you wish to create in the table. ) table_name The name of the table that you wish to create. index_col_n)Ĭolumn1 datatype CONSTRAINT constraint_name PRIMARY KEY, The syntax to create a primary key using the CREATE TABLE statement in PostgreSQL is: CREATE TABLE table_name You can create a primary key in PostgreSQL with the CREATE TABLE statement. If you had two primary keys, the system would not know on which to ensure referential integrity, so you'd have to tell the system which key to use - which would make one of the primary keys more important, which would make it the 'primary key' (!) of the primary keys.Create Primary Key - Using CREATE TABLE statement Otherwise, you would have orders without being able to find out the customer data, which would violate your database's referential integrity. The DB system then ensures several things, among which is the fact that you can not remove a customer who has orders in your database without first removing the orders. You can then link the two tables on the customer number. Every order is uniquely identified by the order number and the customer number.Every customer can be identified uniquely by his customer number.

For example: If you have a Customer table and an Orders table, where the Customers table has a primary key on the customer number and the Orders table has a primary key on the order number and the customer number, that means:

You can 'link' tables using their primary key. That, however, does not mean there can be no other combination of fields uniquely identifying the record! This is where a second feature kicks in: referential integrity. If you have a table and come to the conclusion that your primary key does not uniquely identify each record (also meaning that there can't be two records with the same values for the primary key), you have chosen the wrong primary key, as by definition, the fields of the primary key must uniquely define each record.

There can not be two 'primary' conditions, just like there can not be two 'latest' versions.Įvery table can contain more than one unique keys, but if you decide to have a primary key, this is just one of these unique keys, the 'one' you deem the 'most important', which identifies every record uniquely.

Postgresql appily 2 primary keys