{"id":133,"date":"2014-04-22T12:37:00","date_gmt":"2014-04-22T11:37:00","guid":{"rendered":"https:\/\/debuggersspace.com\/index.php\/2014\/04\/22\/difference-between-primary-key-foreign-key\/"},"modified":"2024-10-04T23:54:39","modified_gmt":"2024-10-04T22:54:39","slug":"difference-between-primary-key-foreign-key","status":"publish","type":"post","link":"https:\/\/debuggersspace.com\/index.php\/2014\/04\/22\/difference-between-primary-key-foreign-key\/","title":{"rendered":"Types of Keys in SQL with Key Differences: Primary Key vs. Foreign Key, and More"},"content":{"rendered":"
\n
\n \t<\/i> Read Time:<\/span>2 Minute, 43 Second <\/div>\n\n <\/div>

In relational database design, keys play a critical role in uniquely identifying records and establishing relationships between tables. Below are the different types of keys used in SQL:<\/p>\n

1. Primary Key<\/h3>\n

A Primary Key uniquely identifies each row in a table and does not allow null values. Only one Primary Key is allowed per table.<\/p>\n

CREATE TABLE Employees (\r\n    EmployeeID INT PRIMARY KEY,\r\n    FirstName VARCHAR(50),\r\n    LastName VARCHAR(50)\r\n);<\/code><\/pre>\n
2. Foreign Key<\/h3>\n
A Foreign Key creates a link between two tables by referencing the Primary Key in another table. It ensures referential integrity.<\/p>\n
CREATE TABLE Orders (\r\n    OrderID INT PRIMARY KEY,\r\n    EmployeeID INT,\r\n    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)\r\n);<\/code><\/pre>\n
3. Unique Key<\/h3>\n
Unique Keys ensure that all values in a column are distinct but allow one null value.<\/p>\n
CREATE TABLE Users (\r\n    UserID INT PRIMARY KEY,\r\n    Email VARCHAR(100) UNIQUE\r\n);<\/code><\/pre>\n
4. Composite Key<\/h3>\n
A Composite Key uses two or more columns to uniquely identify a row.<\/p>\n
CREATE TABLE Enrollment (\r\n    StudentID INT,\r\n    CourseID INT,\r\n    PRIMARY KEY (StudentID, CourseID)\r\n);<\/code><\/pre>\n
5. Candidate Key<\/h3>\n
Any column or combination of columns that can serve as a Primary Key is called a Candidate Key. If one is chosen as the Primary Key, others become Alternate Keys.<\/p>\n
6. Alternate Key<\/h3>\n
An Alternate Key is any Candidate Key not chosen as the Primary Key.<\/p>\n
Summary Table of Key Differences<\/h2>\n\n\n\n\n\n\n\n\n
Aspect<\/th>\nPrimary Key<\/th>\nForeign Key<\/th>\nUnique Key<\/th>\nComposite Key<\/th>\nCandidate Key<\/th>\n<\/tr>\n<\/thead>\n
Uniqueness<\/td>\nUnique<\/td>\nMay contain duplicates<\/td>\nUnique<\/td>\nUnique across multiple columns<\/td>\nUnique<\/td>\n<\/tr>\n
Null Values<\/td>\nNot allowed<\/td>\nAllowed<\/td>\nOne null value allowed<\/td>\nDepends on columns<\/td>\nNot allowed (if chosen as Primary Key)<\/td>\n<\/tr>\n
Relation to Other Tables<\/td>\nNone<\/td>\nReferences a Primary Key in another table<\/td>\nNone<\/td>\nNone<\/td>\nNone<\/td>\n<\/tr>\n
Primary Use<\/td>\nUniquely identifies rows<\/td>\nEstablishes relationships between tables<\/td>\nEnsures uniqueness of column values<\/td>\nIdentifies rows across multiple columns<\/td>\nCan be chosen as Primary Key<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Tricky Interview Questions<\/h2>\n
1. Can a table have multiple Primary Keys?<\/h3>\n
Answer:<\/strong> No, a table can only have one Primary Key, but it can have multiple Candidate Keys.<\/p>\n
2. Can a Foreign Key reference a Unique Key?<\/h3>\n
Answer:<\/strong> Yes, a Foreign Key can reference either a Primary Key or a Unique Key in another table.<\/p>\n
3. What happens when a row with a Foreign Key is deleted in the referenced table?<\/h3>\n
Answer:<\/strong> It depends on the referential action set (e.g., CASCADE<\/code>, SET NULL<\/code>). If CASCADE<\/code> is set, deleting the referenced row will also delete the rows in the child table.<\/p>\n
4. What is the difference between a Candidate Key and an Alternate Key?<\/h3>\n
Answer:<\/strong> A Candidate Key is any column(s) that can uniquely identify a row in a table. An Alternate Key is a Candidate Key that is not chosen as the Primary Key.<\/p>\n
5. Can a Composite Key be a Foreign Key?<\/h3>\n
Answer:<\/strong> Yes, a Composite Key can be used as a Foreign Key, referencing another table\u2019s Composite Key.<\/p>\n
6. Can a table have both a Primary Key and a Foreign Key?<\/h3>\n
Answer:<\/strong> Yes, a table can have both. The Primary Key uniquely identifies rows within the table, while the Foreign Key establishes a relationship with another table.<\/p>\n
7. Can you have multiple Foreign Keys in a table?<\/h3>\n
Answer:<\/strong> Yes, a table can have multiple Foreign Keys, each referencing different tables or even the same table in cases of self-referencing.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
In relational database design, keys play a critical role in uniquely identifying records and establishing relationships between tables. Below are the different types of keys used in SQL: 1. Primary Key A Primary Key uniquely identifies each row in a table and does not allow null values. Only one Primary Key is allowed per table. […]<\/p>\n","protected":false},"author":43,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_uag_custom_page_level_css":"","footnotes":""},"categories":[123,34,27,160,188,161],"tags":[211,209,210,156],"class_list":["post-133","post","type-post","status-publish","format-standard","hentry","category-database","category-ibm","category-sql","category-sql-top-50-qa","category-technical-explorations","category-top-100-qa","tag-composite-key","tag-fk","tag-pk","tag-sql"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Himanshu Namdeo","author_link":"https:\/\/debuggersspace.com\/author\/admin\/"},"uagb_comment_info":0,"uagb_excerpt":"In relational database design, keys play a critical role in uniquely identifying records and establishing relationships between tables. Below are the different types of keys used in SQL: 1. Primary Key A Primary Key uniquely identifies each row in a table and does not allow null values. Only one Primary Key is allowed per table.…","_links":{"self":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts\/133"}],"collection":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/users\/43"}],"replies":[{"embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/comments?post=133"}],"version-history":[{"count":3,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts\/133\/revisions"}],"predecessor-version":[{"id":834,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/posts\/133\/revisions\/834"}],"wp:attachment":[{"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/media?parent=133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/categories?post=133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/debuggersspace.com\/index.php\/wp-json\/wp\/v2\/tags?post=133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}