A foreign key is a key of a different table than the one in which it resides
|
The difference between a composite key, a foreign key and a primary key is a good illustration of the complex and byzantine nature of relational database standards that make database administration an advanced job role requiring specialized skills. Show Primary KeyFirst, a primary key uniquely identifies each record in a database table. Any individual key that does this can be called a candidate key, but only one can be chosen by database engineers as a primary key. Composite KeyNext, there's the composite key, which is composed of two or more attributes that collectively uniquely identify each record. An example would be a list of homes on a real estate market. In a well-ordered database, there should be a primary key that uniquely identifies each record. How this works may have to do with the sophistication of the database. In some cases, the homes may only be uniquely identified by a mortgage number — all other data (towns, streets, house numbers) is not unique to each record. The mortgage number would be the primary key. Suppose, however, that an MLS realtor’s listing technology assigns its own unique numbers to the records in the table. Then, there will be two keys that developers might identify as “candidate keys”:
One of them will qualify as the “primary key” in what some would consider an arbitrary way. A composite key, then, would be the combination of two keys. For example: the combination of house number and street might qualify as a composite key, given that the market listings are local. If so, then when someone searches using both the house number and the street, they should only get one single record returned. Foreign KeyMeanwhile, if there is a key in a linked table, such as a buyer’s table that references the primary key, that will be a foreign key. While a primary key and a composite key might do the same things, the primary key will consist of one column, where the composite key will consist of two or more columns. The relationship between a primary key and a foreign key is quite different. The key thing to understand here is that the primary key in one database table becomes a foreign key in another database table, and vice versa. A foreign key in a database table is taken from some other table and applied in order to link database records back to that foreign table. The foreign key in the database table where it resides is actually the primary key of the other table. Here's an example to make this clearer: If we have a database table labeled, say, "orders," and we have a foreign key that's labeled "customers," we could perhaps link that foreign key to a primary key, identifying each customer in a separate table. In sum, all of this interconnection of key categories shows the detailed nature of creating sophisticated patterns and relationships in relational database systems. The database engineer knows these categories and attributes intimately, and applies them in order to help ensure that the SQL queries and other data retrieval methods triggered by end users are effective. Along with database normalization and promoting structured data, these key relationships are core parts of database system integrity and data storage design best practices. Share this Q&A TagsDatabase Data Management Written by Justin Stoltzfus | Contributor, Reviewer Justin Stoltzfus is a freelance writer for various Web and print publications. His work has appeared in online magazines including Preservation Online, a project of the National Historic Trust, and many other venues. Read Justin Stoltzfus’s bio More Q&As from our experts
Related Terms
Related ArticlesEmerging Technology How Apache Drill Makes Data Analysis Easier for EveryoneEmerging Technology Hadoop Analytics: Not So Easy Across Multiple Data SourcesEmerging Technology Hadoop Analytics: Even Harder With External SourcesIT Careers Database Administration Careers 101Term of the Day Chief Cloud Officer A chief cloud officer (CCO) is an individual who manages, supervises and governs the entire cloud computing environment and... What is a foreign key in a table?A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table.
Why is the foreign key field in a table often the primary key of a different table?Foreign Keys
A foreign key column in a table points to a column with unique values in another table (often the primary key column) to create a way of cross-referencing the two tables. If a column is assigned a foreign key, each row of that column must contain a value that exists in the 'foreign' column it references.
What are keys in a different table from the one in which they reside?Columns that are keys of a different table than the one in which they reside are called foreign keys.
Does a foreign key have to be a primary key of the table it is in?From Books Online: A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. So in your case if you make AnotherID unique, it will be allowed.
|
