Primary Key And Foreign Key In PostgreSQL

Faruk Erdem

2 years ago

In this article, I will make examples by explaining primary key and foreign key on delete and on update.

-- DROP TABLE public.personnel cascade;
CREATE TABLE public.personnel
(
     id integer NOT NULL UNIQUE,
     name character(50),
     address character(50) ,
     tcidno character(11) ,
     "salary" real,
   CONSTRAINT personnel_pkey PRIMARY KEY (id)
     -- CONSTRAINT personnel_name_tcidno_excl EXCLUDE USING gist (
       -- name WITH =,
      -- tcidno WITH <>)

)

-- DROP TABLE public.personnel cascade;

CREATE TABLE public.personnel

(

id integer NOT NULL UNIQUE,

name character(50),

address character(50) ,

tcidno character(11) ,

"salary" real,

CONSTRAINT personnel_pkey PRIMARY KEY (id)

-- CONSTRAINT personnel_name_tcidno_excl EXCLUDE USING gist (

-- name WITH =,

-- tcidno WITH <>)

)

While creating the personnel table above, we create our table by specifying the table in which PRIMARY KEY is to be put in the table, together with the CONSTRAINT parameter and the PRIMARY KEY command at the end of our table.

-- DROP TABLE public.personnel_birth_date;
CREATE TABLE public.personnel_birth_date
(
     id integer,
     birth_date date,
     p_id integer,
     CONSTRAINT aa FOREIGN KEY (p_id)
         REFERENCES public.personnel (id) MATCH SIMPLE
         ON UPDATE NO ACTION
         ON DELETE CASCADE
)

-- DROP TABLE public.personnel_birth_date;

CREATE TABLE public.personnel_birth_date

(

id integer,

birth_date date,

p_id integer,

CONSTRAINT aa FOREIGN KEY (p_id)

REFERENCES public.personnel (id) MATCH SIMPLE

ON UPDATE NO ACTION

ON DELETE CASCADE

)

While creating our personnel_birth_date table above, we specify that a Foreign key will be created in the CONSTRAINT section, and the ON UPDATE and ON DELETE commands also come into play when specifying this.

ON DELETE CASCADE parameter is used in the personnel_birth_date table.

When the record in the personnel table is deleted with this parameter, it will also delete the relevant record in the personnel_birth_date table.

ON UPDATE and ON DELETE commands can be used by taking the following parameters.

-SET NULL
-SET DEFAULT
-NO ACTION
-CASCADE
-RESTRICT

When the personnel is deleted or updated with the above parameters, it is used to perform operations such as null value in the personnel_birth_date table or the column default value.

Let’s reinforce them with an example.

First, let’s load data into the tables we created.

INSERT INTO public.personnel(
id, name, address, tcidno, "salary")
VALUES (1, 'faruk', 'erdem', '11245676866', 1);

INSERT INTO public.personnel_birth_date(
id, birth_date, p_id)
VALUES(1, '1991-08-30', 1);

INSERT INTO public.personnel(
id, name, address, tcidno, "salary")
VALUES (2, 'ayse', 'aa', '11225675866', 2);

INSERT INTO public.personnel_birth_date(
id, birth_date, p_id)
VALUES (2, '1991-07-30', 2);

INSERT INTO public.personnel(

id, name, address, tcidno, "salary")

VALUES (1, 'faruk', 'erdem', '11245676866', 1);

INSERT INTO public.personnel_birth_date(

id, birth_date, p_id)

VALUES(1, '1991-08-30', 1);

INSERT INTO public.personnel(

id, name, address, tcidno, "salary")

VALUES (2, 'ayse', 'aa', '11225675866', 2);

INSERT INTO public.personnel_birth_date(

id, birth_date, p_id)

VALUES (2, '1991-07-30', 2);

After inserting our data, let’s delete the record from our personnel table as follows and check whether the personnel has been deleted from the date of birth table as well.

delete from personel where id=2

1	delete from personel where id=2

When we query our staff table, we see that the data has been deleted.

As can be seen above, when we check the personnel_date_date table, we see that the second record is deleted from here as well.