![]() We’ll discuss more on this in the subsequent tutorial. The difference only arises when you define the foreign key constraint as DEFERRABLE with an INITIALLY DEFERRED or INITIALLY IMMEDIATE mode. The RESTRICT action is similar to the NO ACTION. SQL state: 23503 Code language: Shell Session ( shell ) WHERE customer_id = 1 Code language: SQL (Structured Query Language) ( sql )īecause of the ON DELETE NO ACTION, PostgreSQL issues a constraint violation because the referencing rows of the customer id 1 still exist in the contacts table: ERROR: update or delete on table "customers" violates foreign key constraint "fk_customer" on table "contacts"ĭETAIL: Key (customer_id)=(1) is still referenced from table "contacts". The following statement deletes the customer id 1 from the customers table: DELETE FROM customers INSERT INTO contacts(customer_id, contact_name, phone, email) The following inserts data into the customers and contacts tables: INSERT INTO customers(customer_name) REFERENCES customers(customer_id) Code language: SQL (Structured Query Language) ( sql )īecause the foreign key constraint does not have the ON DELETE and ON UPDATE action, they default to NO ACTION. The following foreign key constraint fk_customer in the contacts table defines the customer_id as the foreign key: CONSTRAINT fk_customer ![]() The customer_id column in the contacts table is the foreign key column that references the primary key column with the same name in the customers table. In this example, the customers table is the parent table and the contacts table is the child table.Įach customer has zero or many contacts and each contact belongs to zero or one customer. ) Code language: SQL (Structured Query Language) ( sql ) The following statements create the customers and contacts tables: DROP TABLE IF EXISTS customers Ĭustomer_id INT GENERATED ALWAYS AS IDENTITY,Ĭontact_id INT GENERATED ALWAYS AS IDENTITY, PostgreSQL foreign key constraint examples PostgreSQL supports the following actions: Since the primary key is rarely updated, the ON UPDATE action is not often used in practice. The delete and update actions determine the behaviors when the primary key in the parent table is deleted and updated. Finally, specify the delete and update actions in the ON DELETE and ON UPDATE clauses.Third, specify the parent table and parent key columns referenced by the foreign key columns in the REFERENCES clause.Second, specify one or more foreign key columns in parentheses after the FOREIGN KEY keywords. ![]() If you omit it, PostgreSQL will assign an auto-generated name. First, specify the name for the foreign key constraint after the CONSTRAINT keyword.REFERENCES parent_table( parent_key_columns) The following illustrates a foreign key constraint syntax: FOREIGN KEY( fk_columns) The foreign key constraint helps maintain the referential integrity of data between the child and parent tables.Ī foreign key constraint indicates that values in a column or a group of columns in the child table equal the values in a column or a group of columns of the parent table. In PostgreSQL, you define a foreign key using the foreign key constraint. And the table referenced by the foreign key is called the referenced table or parent table.Ī table can have multiple foreign keys depending on its relationships with other tables. The table that contains the foreign key is called the referencing table or child table. Introduction to PostgreSQL Foreign Key ConstraintĪ foreign key is a column or a group of columns in a table that reference the primary key of another table. Summary: in this tutorial, you will learn about PostgreSQL foreign key and how to add foreign keys to tables using foreign key constraints.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |