SQL Primary/Foreign Key

Primary Key

A table usually has a column or combination of columns whose values uniquely identify each row in the table -- this column (or columns) is called the primary key of the table

INNER JOIN, Example

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

LEFT JOIN, Example

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

FULL JOIN

SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name

INNER JOIN

SELECT column_name(s) FROM table_name2 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

UNION

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2

A primary key is a value

that must be unique within the table; it binds data together, across tables, without repeating all of the data in every table

Tables in a database

are often related to each other with keys

The primary key of the table

enforces the entity integrity of the table

Foreign Key (FK)

is a column or combination of columns used to establish and enforce a link between the data in two tables A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table-- this column becomes a FK in the second table

The JOIN keyword:

is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

The UNION operator:

is used to combine the result-set of two or more SELECT statements

The FULL JOIN keyword:

return rows when there is a match in one of the tables

The LEFT JOIN keyword:

returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2)

The RIGHT JOIN keyword:

returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1)

The INNER JOIN keyword:

returns rows when there is at least one match in both tables