{"id":53879,"date":"2023-02-18T10:17:55","date_gmt":"2023-02-18T10:17:55","guid":{"rendered":"https:\/\/dbtut.com\/?p=53879"},"modified":"2023-02-18T10:19:40","modified_gmt":"2023-02-18T10:19:40","slug":"primary-key-and-foreign-key-in-postgresql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/","title":{"rendered":"Primary Key And Foreign Key In PostgreSQL"},"content":{"rendered":"

In this article, I will make examples by explaining primary key and foreign key on delete and on update.<\/p>\n

-- DROP TABLE public.personnel cascade;\r\nCREATE TABLE public.personnel\r\n(\r\n     id integer NOT NULL UNIQUE,\r\n     name character(50),\r\n     address character(50) ,\r\n     tcidno character(11) ,\r\n     \"salary\" real,\r\n   CONSTRAINT personnel_pkey PRIMARY KEY (id)\r\n     -- CONSTRAINT personnel_name_tcidno_excl EXCLUDE USING gist (\r\n       -- name WITH =,\r\n      -- tcidno WITH <>)\r\n\r\n)<\/pre>\n
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.<\/p>\n
-- DROP TABLE public.personnel_birth_date;\r\nCREATE TABLE public.personnel_birth_date\r\n(\r\n     id integer,\r\n     birth_date date,\r\n     p_id integer,\r\n     CONSTRAINT aa FOREIGN KEY (p_id)\r\n         REFERENCES public.personnel (id) MATCH SIMPLE\r\n         ON UPDATE NO ACTION\r\n         ON DELETE CASCADE\r\n)<\/pre>\n
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.<\/p>\n
ON DELETE CASCADE parameter is used in the personnel_birth_date table.<\/p>\n
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.<\/p>\n
ON UPDATE and ON DELETE commands can be used by taking the following parameters.<\/p>\n
-SET NULL
\n-SET DEFAULT
\n-NO ACTION
\n-CASCADE
\n-RESTRICT<\/p>\n
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.<\/p>\n
Let’s reinforce them with an example.<\/p>\n
First, let’s load data into the tables we created.<\/p>\n
INSERT INTO public.personnel(\r\nid, name, address, tcidno, \"salary\")\r\nVALUES (1, 'faruk', 'erdem', '11245676866', 1);\r\n\r\nINSERT INTO public.personnel_birth_date(\r\nid, birth_date, p_id)\r\nVALUES(1, '1991-08-30', 1);\r\n\r\nINSERT INTO public.personnel(\r\nid, name, address, tcidno, \"salary\")\r\nVALUES (2, 'ayse', 'aa', '11225675866', 2);\r\n\r\nINSERT INTO public.personnel_birth_date(\r\nid, birth_date, p_id)\r\nVALUES (2, '1991-07-30', 2);<\/pre>\n
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.<\/p>\n
delete from personel where id=2\r\n<\/pre>\n
\"\"<\/p>\n
When we query our staff table, we see that the data has been deleted.<\/p>\n
\"\"<\/p>\n
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.<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
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 ( …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":366,"featured_media":53884,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-53879","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-postgres"],"aioseo_notices":[],"yoast_head":"\nPrimary Key And Foreign Key In PostgreSQL - Database Tutorials<\/title>\n<meta name=\"description\" content=\"In this article, I will make examples by explaining primary key and foreign key on delete and on update.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Primary Key And Foreign Key In PostgreSQL - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"In this article, I will make examples by explaining primary key and foreign key on delete and on update.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-02-18T10:17:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-18T10:19:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png\" \/>\n\t<meta property=\"og:image:width\" content=\"642\" \/>\n\t<meta property=\"og:image:height\" content=\"327\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Faruk Erdem\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Faruk Erdem\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/\"},\"author\":{\"name\":\"Faruk Erdem\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb\"},\"headline\":\"Primary Key And Foreign Key In PostgreSQL\",\"datePublished\":\"2023-02-18T10:17:55+00:00\",\"dateModified\":\"2023-02-18T10:19:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/\"},\"wordCount\":278,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png\",\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/\",\"name\":\"Primary Key And Foreign Key In PostgreSQL - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png\",\"datePublished\":\"2023-02-18T10:17:55+00:00\",\"dateModified\":\"2023-02-18T10:19:40+00:00\",\"description\":\"In this article, I will make examples by explaining primary key and foreign key on delete and on update.\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png\",\"width\":642,\"height\":327},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Primary Key And Foreign Key In PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb\",\"name\":\"Faruk Erdem\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g\",\"caption\":\"Faruk Erdem\"},\"url\":\"https:\/\/dbtut.com\/index.php\/author\/farukerdem\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Primary Key And Foreign Key In PostgreSQL - Database Tutorials","description":"In this article, I will make examples by explaining primary key and foreign key on delete and on update.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Primary Key And Foreign Key In PostgreSQL - Database Tutorials","og_description":"In this article, I will make examples by explaining primary key and foreign key on delete and on update.","og_url":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/","og_site_name":"Database Tutorials","article_published_time":"2023-02-18T10:17:55+00:00","article_modified_time":"2023-02-18T10:19:40+00:00","og_image":[{"width":642,"height":327,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png","type":"image\/png"}],"author":"Faruk Erdem","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Faruk Erdem","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/"},"author":{"name":"Faruk Erdem","@id":"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb"},"headline":"Primary Key And Foreign Key In PostgreSQL","datePublished":"2023-02-18T10:17:55+00:00","dateModified":"2023-02-18T10:19:40+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/"},"wordCount":278,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png","articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/","url":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/","name":"Primary Key And Foreign Key In PostgreSQL - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png","datePublished":"2023-02-18T10:17:55+00:00","dateModified":"2023-02-18T10:19:40+00:00","description":"In this article, I will make examples by explaining primary key and foreign key on delete and on update.","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2023\/02\/Ekran-goruntusu-2023-02-18-131545.png","width":642,"height":327},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2023\/02\/18\/primary-key-and-foreign-key-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Primary Key And Foreign Key In PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/a7dfc5684c116e536b4e93ee214ccbfb","name":"Faruk Erdem","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ad1e61fb5a7c9a590e765f7cad8f2dc8332090f1ceb9a5ee2aa95c69213f0c50?s=96&d=mm&r=g","caption":"Faruk Erdem"},"url":"https:\/\/dbtut.com\/index.php\/author\/farukerdem\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/53879","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/366"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=53879"}],"version-history":[{"count":2,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/53879\/revisions"}],"predecessor-version":[{"id":53886,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/53879\/revisions\/53886"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/53884"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=53879"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=53879"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=53879"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}