Postgresql Add Foreign Key

In this PostgreSQL tutorial, we are going to learn about Postgresql add foreign key. Here we will learn how to add foreign keys in PostgreSQL, and we will also cover the following list of topics.

• Postgresql add foreign key
• Postgresql add foreign key column
• Postgresql add foreign key constraint to existing column
• Postgresql add foreign key to existing column
• Postgresql add foreign key constraint to existing table
• Postgresql add foreign key if not exists
• Postgresql add foreign key alter table
• Postgresql add foreign key existing table
• Postgresql add foreign key on delete cascade

Postgresql add foreign key

A group of columns with its values dependent on the primary key benefits from another table is known as the Foreign key in Postgresql. It has value in one column or group of columns displayed in the same column or a combination of columns in another table.

The foreign key is basically known as the referencing key, and it matches the primary key field from another table, which signifies that the foreign key field in one table refers to another table’s primary key field.

In PostgreSQL, the foreign key’s values are parallel to the actual values of the primary key in the other table which is called a Referential integrity Constraint. The below explains a foreign key constraint syntax.

[CONSTRAINT fk_name]
   FOREIGN KEY(fk_columns) 
   REFERENCES parent_table(parent_key_columns)
   [ON DELETE delete_action]
   [ON UPDATE update_action]

In the above syntax firstly we have specified the name for the foreign key constraint after the CONSTRAINT keyword. The CONSTRAINT clause is optional and if we ignore it, PostgreSQL will assign an auto-generated name.

Then, we will describe one or more foreign key columns in parentheses after the FOREIGN KEY keywords. Now, we will explain the parent table and parent key columns referenced by the foreign key columns in the REFERENCES clause.

Lastly, we will state the delete and update actions in the ON DELETE and ON UPDATE clauses. We will understand this with the help of a simple example. We will create two tables and will apply a foreign key. Let’s check the queries.

CREATE TABLE abc (
    id SERIAL PRIMARY KEY,
    full_name TEXT
);

CREATE TABLE xyz (
    order_id SERIAL,
    dish_name TEXT,
    customer_id INTEGER
);

ALTER TABLE xyz
    ADD CONSTRAINT fk_orders_abc FOREIGN KEY (customer_id) REFERENCES abc (id);

let’s check the output.

Also, read: PostgreSQL Min With Examples

Postgresql add foreign key if not exists

The ALTER TABLE statement in Postgresql is generally used to add, delete or modify our table. If we want to add a column to the table, we can simply describe the ADD COLUMN clause in the ALTER TABLE statement.

However, we will encounter an error if we aim to add a column that already exists. It’s simple to dodge this error by using the IF NOT EXISTS option with our ADD COLUMN clause.

This alternative commands PostgreSQL to add the new column only if the column name does not exist in the table. In this topic, we’ll take learn Postgresql ADD COLUMN IF NOT EXISTS queries and check out some examples of its use. Let’s check the syntax first.

ALTER TABLE TABLE_NAME
ADD COLUMN IF NOT EXISTS column_name [ DATA TYPE ]

Firstly, we will describe the name of a table to which we will add a column. We will give the IF NOT EXISTS option after the ADD COLUMN clause, and then we will describe the name of the column and its data type.

The IF NOT EXISTS option will check if the described column name exists in the table. With this alternative, no error is returned if we try to add a column that is already existing. Let’s check the queries for example.

CREATE TABLE staff(
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
POSITION VARCHAR(50)
);

CREATE TABLE information(
last_name VARCHAR(50),
salary INT
);

INSERT INTO staff VALUES
( 011, 'James','Consultant'),
( 012, 'Lucas','HR'),
( 013, 'Charlie','Sales');

INSERT INTO information VALUES
('Baker', 20000),
('Bell', 50000),
('Puth', 40000);

Let’s check the output.

Now we will alter our tables by inserting IF NOT EXISTS. Let’s check the queries first.

ALTER TABLE INFORMATION ADD COLUMN IF NOT EXISTS TAX_ID INT;

ALTER TABLE information 
ADD CONSTRAINT fk_id 
FOREIGN KEY (tax_id) 
REFERENCES staff (id);

Now, let’s check the output.

Read: PostgreSQL group by with examples

Postgresql add foreign key constraint to existing column

Now we will learn how we can add foreign key constraints to the existing column. Basically, we will learn how to make any column a foreign key. Also, we can have more than one foreign key on our table.

We will understand this by creating the tables. In the below example, we have created two tables market and calender. In the market table, we have made sale_date as a foreign key.

CREATE TABLE market (
    agent_id   integer,
    sale_date  date,
    amout      numeric(10,2)
    );

INSERT INTO market VALUES
    (1, '2021-11-01', 2021.11),
    (1, '2021-11-02', 625.44),
    (1, '2021-11-03', 423.99);

CREATE TABLE calender (
    date_idx   date primary key,
    year       integer not null,
    month      integer not null,
    day        integer not null
);

ALTER TABLE market
ADD CONSTRAINT sale_date_fk FOREIGN KEY (sale_date)
REFERENCES calender (date_idx) NOT VALID;

Let’s check the output.

Read: PostgreSQL add primary key

Postgresql add foreign key alter table

Now we will learn how to add a foreign key alter table in Postgresql. When we add a column with ADD COLUMN, all the existing rows in the table are instantiated with the column’s default value (NULL if no DEFAULT clause is specified).

When we add a column with a non-null default or change the type of an existing column will need the entire table to be rewritten. This will take an important amount of time for a large table, and it will temporarily require double the disk space.

Adding a CHECK or NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint.

The essential cause for providing the option to describe multiple changes in a single ALTER TABLE is that multiple table scans or rewrites can thereby be combined into a single pass over the table.

ALTER TABLE market ADD COLUMN column_name varchar(20);

We will alter our existing table market.

Read: Postgresql Having Clause + Examples

Postgresql add foreign key constraint to existing table

If we delete any record from the parent table then automatically Now we will learn how to add the foreign key constraint to an existing table. Constraint syntax and example.

CREATE TABLE moo(a INT UNIQUE NOT NULL);

CREATE TABLE noo(b INT);

ALTER TABLE noo ADD COLUMN c INT NOT NULL
CONSTRAINT noo_moo_fk_c REFERENCES moo (a)   
ON UPDATE CASCADE ON DELETE CASCADE; 

Let’s check the output.

Read: Postgresql while loop

Postgresql add foreign key on delete cascade

A cascade in Postgresql means to delete or update records in a parent table will instantly delete or update coordinating records in a child table where a foreign key relationship is in place. If we want to ignore deleting records from the child table, we have to set the foreign key value for the parent table record to NULL.

The table carrying the foreign key is known as referencing table or child table. The parent table turned to the table to which the foreign key is related. A foreign key in the PostgreSQL child table is associated with the primary key in the parent table.

Now we will make a table followed by creating a foreign key with the CASCADE DELETE option can be done through the CREATE TABLE and ALTER TABLE statements. The below are the queries we’ll use to create tables.

CREATE TABLE worker(work_id SERIAL PRIMARY  KEY,
name VARCHAR(30),
STATUS text,
calling_num VARCHAR(12),
activity_fk INT NOT NULL);

CREATE TABLE activity(work_id SERIAL PRIMARY KEY, SECTION VARCHAR(20));

In the above queries, the parent table is the worker table. It is also a primary key which is the work_id field. The activity table will act as the child table in our foreign key DELETE CASCADE example.

Now we will alter data in a table in which we are going to establish this parent-child relationship between the tables and specify that we want the DELETE CASCADE option to apply.

We can also specify the DELETE CASCADE clause using the ALTER TABLE statement. In the query below, we will specify ON DELETE CASCADE which means that if the parent table is deleted, the child table will also be deleted. Let’s check the query.

ALTER TABLE worker ADD FOREIGN KEY (activity_fk)
REFERENCES activity(work_id) ON DELETE CASCADE;

Now we will Insert data in the PostgreSQL table The INSERT statement is used to insert rows in a table. It can be used to insert a single record or multiple records into a table into PostgreSQL. Let’s check the statements.

INSERT INTO activity(SECTION) VALUES ('retail');
INSERT INTO activity(SECTION) VALUES ('sales');
INSERT INTO activity(SECTION) VALUES ('computers');

INSERT INTO worker(name,STATUS,calling_num,activity_fk)
VALUES('grace','regular','0985959905','1'),
('chloe','probationary','093948889487','2'),
('thomas','regular','095599093490','1'),
('david','probationary','097867556451','3'),
('hannah','regular','094458909099','2'),
('joseph','probationary','097746890988','2');

Let’s check the output.

PostgreSQL in cascade delete into the table let’s see how the DELETE CASCADE works in Postgresql. The DELETE statement generally uses a WHERE clause to select rows from the specified table.

In the absence of a WHERE clause, all rows in the table are deleted. We can see the DELETE CASCADE in action. The cascade delete takes effect when we delete the record from the activity table where the value of the section is retail.

The cascade ensures that related records from the worker table will also be removed. Because the work_id for the record in question is ‘1’, all workers under activity_fk 1 will be deleted. Let’s check the output.

You may also like to read the following PostgreSQL articles.

• Postgresql REGEXP_REPLACE Function
• Postgresql Sum – How to use
• Postgresql if else – How to use
• Postgresql replace + Examples
• Postgres date range + Examples
• PostgreSQL Update Join
• Postgresql unique constraint
• Postgresql Average + Examples

In this Postgresql tutorial, we have learned about Postgresql add foreign key. Here we have learned how to add foreign keys in PostgreSQL, and we have also covered the following list of topics.

• Postgresql add foreign key
• Postgresql add foreign key column
• Postgresql add foreign key constraint to existing column
• Postgresql add foreign key to existing column
• Postgresql add foreign key constraint to existing table
• Postgresql add foreign key if not exists
• Postgresql add foreign key alter table
• Postgresql add foreign key existing table
• Postgresql add foreign key on delete cascade