Why is the primary key foreign key relationship important in a relational database?
|
Improve Article Show Save Article Improve Article Save Article Primary Key: A primary key is used to ensure that data in the specific column is unique. A column cannot have NULL values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence. Example: Refer the figure – Foreign Key: Example: Refer the figure – Figure:  Let’s see the difference between Primary Key and Foreign Key:
Siebel Analytics Server Administration Guide > Data Modeling > Understanding the Physical Database Model > Primary Key-Foreign Key RelationshipsTo fully understand physical database models, it is important to understand the concepts behind primary key-foreign key relationships. A primary key-foreign key relationship defines a one-to-many relationship between two tables in a relational database. A foreign key is a column or a set of columns in one table that references the primary key columns in another table. The primary key is defined as a column (or set of columns) where each value is unique and identifies a single row of the table. Consider Figure 5, where the upper table is a fact table named Sales and the lower table is a dimension table named Date. The Sales fact table contains one row for every sales transaction, and the Date dimension table contains one row for every date the database will potentially cover. Figure 5. Primary Key-Foreign Key Sample Because of this primary key-foreign key relationship, you can join the Sales and Date tables to combine the other attributes of the Date table with the records in the Sales table. For example, if an analyst asks for the total sales, the day of the week, the product name, and the store in which the product was sold, the information is compiled by joining the sales, date, product, and store tables through the primary key-foreign key relationships between the Sales table and the various dimension tables. What is a foreign key?A foreign key is a column or columns of data in one table that refers to the unique data values -- often the primary key data -- in another table. Foreign keys link together two or more tables in a relational database. To ensure the links between a foreign key and primary keys aren't broken, foreign key constraints are created to prevent actions that would damage the links between the two tables and prevent erroneous data from being added to the foreign key column. Relational databases, such as Microsoft SQL Server, organize data points with defined relationships. They have several key types to help keep track of this information. Administrators can select or change a primary key as needed in a relational database. Importance of foreign keysForeign keys are important for several reasons, including the following:
Example of how foreign keys workFigure 2 shows two tables that contain basic data about customers and their orders from an online office furniture vendor. The primary key in the Customers table is CustomerNo, and the primary key in the Orders table is OrderNo. The primary key uniquely identifies the table and contains the value that the foreign key refers to. Primary keys must be unique to each row of data. In the Orders table, the CustomerNo value is a foreign key that links the two tables relating back to the CustomerNo value in the Customers table. When a primary key in a table changes, the set of associated foreign keys also changes. A primary key column in the original or parent table can be targeted by multiple foreign keys from other tables in the database, which are called child tables. But a primary key does not have to be the target of foreign keys. Differences between primary keys and foreign keysPrimary and foreign keys commonly work together to link tables in a relational database. They each do something different to characterize and link tables. Primary keysA database table has only one primary key. A table's primary key acts as a unique identifier. The primary key values identify specific records and prevent duplication of records. Null values are not permitted in a primary key. Primary keys generally should be read-only because they must be consistent to maintain the unique identity of the table. The following are some real-world primary key data types:
Foreign keysThese link two tables together. It is possible to have more than one foreign key in a table, and they can accept a null value. Foreign key values do not need to be unique; duplicate values can be stored in foreign key columns. Foreign keys do have to link back to columns with unique values. Those columns are frequently primary keys. What is a foreign key constraint?Constraints in general are pieces of code that enforce rules and regulations on the data. They create conditions that the data must satisfy. Foreign key constraints prevent invalid data from being placed into the foreign key column because the data must be one of the values contained in the table where it's directed. A foreign key constraint links a column in one table to a column in another and prevents actions that would destroy the link between two tables. In the context of the above example, the constraint is a bit of code that ensures a value can only be added to the Orders table if it already exists in the Customers table. SQL is a coding language used for programming relational databases. To create a constraint to define a foreign key in Figure 2, the following SQL code would be used: ALTER TABLE ORDERS ADD FOREIGN KEY (CustomerNo) REFERENCES CUSTOMERS (CustomerNo); Any constraint defined using the above ALTER TABLE command can be dropped using the DROP CONSTRAINT command. To drop or remove a foreign key, use the following SQL syntax: ALTER TABLE ORDERS DROP FOREIGN KEY; Types of constraintsForeign keys do not have to be linked specifically to a primary key constraint in another table; they can also reference the columns of a UNIQUE constraint elsewhere. Foreign key constrains are not the only types of constraints. The following are some other SQL constraint types:
Foreign key problemsMany database users encounter foreign key errors, often because of referential integrity problems. Referential integrity is the accuracy and consistency of data in a relationship. A foreign key might point to data that no longer exists, or the foreign key's data type doesn't match the primary key data type, eroding referential integrity. Referential integrity can also be corrupted if the foreign key doesn't reference all the data from the primary key. If there's a parent table for Sales which consists of a primary key of the company name, department name and address, then the child table for Customers must refer to all attributes of the parent table -- not just one or two. If the child table presents a foreign key value that doesn't correspond to a value in the parent table, it corrupts referential integrity. The unmatched value in the child table is sometimes referred to as an orphan record. In Figure 2, if the CustomerNo column in the Orders table contained the value five, that value would be an orphan because there is no corresponding CustomerNo 5 in the parent table. If a primary key was deleted from the Customers table, referential integrity would be damaged. Database administrators can define how the database engine handles foreign key problems and lapses in referential integrity using referential integrity constraints. If a user tries to delete or update a key that a foreign key points to, the following user-defined constraints dictate the referential actions the database engine can take in response:
Cloud-based relational databases use foreign keys to give them structure and to maintain data integrity. Learn how cloud databases are deployed and managed and the benefits of using them. |
