![]() ![]() NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is thrown. RESTRICT prevents the deletion of a referenced row. The RESTRICT action is pretty much the same as NO ACTION except that the check is not deferrable. DELETE FROM sportsĪs you can see, the server issues an error indicating that the action we are going to take violates the existing foreign key constraint. Let us try to delete one record from the sports table. The following example demonstrates how NO ACTION works. The NO ACTION referential action produces an error indicating that the data deletion or update would create a foreign key constraint violation. Let’s look at each of them in more detail. PostgreSQL supports the following referential actions: Likewise, the ON UPDATE clause specifies the action to be taken when a referenced column in the referenced table is being updated to a new value. The ON DELETE clause specifies the action to be performed when a referenced row in the referenced table is being deleted. These actions determine the behavior of the PostgreSQL server when a primary key is deleted or updated in the parent table. ON DELETE and ON UPDATE clauses are optional. Parent_table parameter defines the name of the parent table, followed by its column names, which are referenced by the foreign key. Please note that if you do not specify the foreign key name, PostgreSQL will create the constraint name automatically as per the default naming convention.įk_columns are the table columns to be used as a foreign key. In the Postgres foreign key syntax above, specify the following:Ĭonstraint_name is the name to be used to define the foreign key constraint. Let’s look at how to create a PostgreSQL foreign key when creating a table. How to create a foreign key in PostgreSQL? Actually, you can do it in three ways: define the foreign key inside the CREATE TABLE statement, run a separate ALTER TABLE statement in case you want to add a foreign key to an already existing table, or create a FOREIGN KEY constraint visually in a GUI tool for PostgreSQL. CREATE TABLE sports(Ī PostgreSQL table can have various foreign keys depending on its connection with other tables. In other words, multiple records in the sportspeople table can contain the same sports_id that points to one sports_id value in the sports table. This way, it forms one-to-many relationships between the sportspeople and sports tables, which means that for one sport there could be multiple sportspeople. This foreign key refers to the primary key column sports_id in the sports table. Suppose, we have the sportspeople table that has a PostgreSQL foreign key column sports_id. Foreign key exampleįor better understanding, let us consider the following example. ![]() Constraints are the rules enforced on data columns in tables and used to prevent invalid data from being entered into the database. ![]() Technically, FOREIGN KEYs, alongside PRIMARY KEYs, CHECK and UNIQUE constraints, belong to the PostgreSQL database constraints. In other words, the foreign key field in one table refers to another table’s primary key field. The foreign key is often called the referencing key as it matches the primary key field from another table. And the table referenced by the foreign key is called the parent table (or the referenced table). The database table that contains a foreign key is called the child table (or the referencing table). Learn the basics of the PostgreSQL FOREIGN KEY constraint What is FOREIGN KEY in PostgreSQL?Ī foreign key (FK) represents one or more than one column used to establish and enforce a link between data in two database tables for controlling data stored in the foreign key table. Here we will go into the basics of PostgreSQL Foreign Keys, demonstrate to you how to create, drop, and manage foreign keys on real-life examples, and introduce to you one of the most convenient Postgres GUI tools on the market. Both primary keys and foreign keys are the two types of constraints that are used to enforce data integrity in PostgreSQL tables. This is the second article in our series on PostgreSQL database constraints, and it comprises a follow-up to our recent PostgreSQL Primary Keys review.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |