![]() ![]() ![]() 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. Why is a GUI tool a better way to manage keys in PostgreSQL?.Learn the basics of the PostgreSQL FOREIGN KEY constraint.Read on to find out what foreign keys in PostgreSQL are and how to use them. In fact, it is the FOREIGN KEY mechanism that provides the relationality of relational databases. We’ll focus on the ON DELETE action.Foreign keys help normalize data in databases and reduce redundancy. 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: 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 |