On this page Show
A foreign key is a column (or combination of columns) in a table whose values must match values of a column in some other table. For example, given an
To learn more about the basics of foreign keys, watch the following video:
DetailsRules for creating foreign keysForeign Key Columns
Referenced Columns
Null valuesSingle-column foreign keys accept null values. Multiple-column (composite) foreign keys only accept null values in the following scenarios:
For more information about composite foreign keys, see the composite foreign key matching section. Note that allowing null values in either your foreign key or referenced columns can degrade their referential integrity, since any key with a null value is never checked against the referenced table. To avoid this, you can use a
Note: A Composite foreign key matchingBy default, composite foreign keys are matched using the All composite key matches defined prior to version 19.1 use the How it worksFor matching purposes, composite foreign keys can be in one of three states:
For examples showing how these key matching algorithms work, see Match composite foreign keys with Note: CockroachDB does not support Foreign key actionsWhen you set a foreign key constraint, you can control what happens to the constrained column when the column it's referencing (the foreign key) is deleted or updated.
PerformanceBecause the foreign key constraint requires per-row checks on two tables, statements involving foreign key or referenced columns can take longer to execute. To improve query performance, we recommend doing the following:
SyntaxForeign key constraints can be defined at the table level. However, if you only want the constraint to apply to a single column, it can be applied at the column level. Note: You can also add the Column level
Example icon/buttons/copy
Warning:
Table level
Example icon/buttons/copy
Usage examplesUse a foreign key constraint with default actionsIn this example, we'll create a table with a foreign key constraint with the default actions ( First, create the referenced table: icon/buttons/copy
Next, create the referencing table: icon/buttons/copy
Let's insert a record into each table: icon/buttons/copy
icon/buttons/copy
The second record insertion
returns an error because the customer Let's insert a record into the referencing table and try to update the referenced table: icon/buttons/copy
icon/buttons/copy
The update to the referenced table
returns an error because icon/buttons/copy
icon/buttons/copy
Now let's try to delete a referenced row: icon/buttons/copy
Similarly, the deletion returns an
error because icon/buttons/copy
icon/buttons/copy
Use a Foreign Key Constraint with CASCADEIn this example, we'll create a table with a foreign key constraint with the foreign key actions First, create the referenced table: icon/buttons/copy
Then, create the referencing table: icon/buttons/copy
Insert a few records into the referenced table: icon/buttons/copy
Insert some records into the referencing table: icon/buttons/copy
Now, let's update an icon/buttons/copy
icon/buttons/copy
icon/buttons/copy
When Similarly, a deletion will cascade. Let's delete icon/buttons/copy
icon/buttons/copy
Let's check to make sure the rows in icon/buttons/copy
Use a Foreign Key Constraint with SET NULLIn this example, we'll create a table with a foreign key constraint with the foreign key actions First, create the referenced table: icon/buttons/copy
Then, create the referencing table: icon/buttons/copy
Insert a few records into the referenced table: icon/buttons/copy
Insert some records into the referencing table: icon/buttons/copy
icon/buttons/copy
Now, let's update an icon/buttons/copy
icon/buttons/copy
icon/buttons/copy
When Similarly, a deletion will set the referencing icon/buttons/copy
icon/buttons/copy
Let's check to make sure the row in icon/buttons/copy
Use a Foreign Key Constraint with SET DEFAULTIn this example, we'll create a table with a First, create the referenced table: icon/buttons/copy
Then, create the referencing table with the icon/buttons/copy
Insert a few records into the referenced table: icon/buttons/copy
Insert some records into the referencing table: icon/buttons/copy
icon/buttons/copy
Now, let's update an icon/buttons/copy
icon/buttons/copy
icon/buttons/copy
When Similarly, a deletion will set the referencing icon/buttons/copy
icon/buttons/copy
Let's check to make sure the
corresponding icon/buttons/copy
If the default value for the
For example, let's create a new icon/buttons/copy
icon/buttons/copy
Then we can create a new icon/buttons/copy
icon/buttons/copy
Deleting and updating values in the icon/buttons/copy
icon/buttons/copy
icon/buttons/copy
Add multiple foreign key constraints to a single columnYou can add more than one foreign key constraint to a single column. For example, if you create the following tables: icon/buttons/copy
icon/buttons/copy
You can create a table with a column that references
columns in both the icon/buttons/copy
Inserts into the Let's insert a record into each table: icon/buttons/copy
icon/buttons/copy
icon/buttons/copy
The last statement succeeds because For instance, the following statement fulfills just one of the foreign key constraints and returns an error: icon/buttons/copy
CockroachDB allows you to add multiple foreign key constraints on the same column, that reference the same column: icon/buttons/copy
icon/buttons/copy
There are now two foreign key
constraints on In the event of a icon/buttons/copy
Match composite foreign keys with MATCH SIMPLE and MATCH FULLThe examples in this section show how composite foreign key matching works for both the First, let's create some tables. icon/buttons/copy
icon/buttons/copy
icon/buttons/copy
Next, we populate icon/buttons/copy
Now let's look at some
MATCH SIMPLEInserting values into the table using the
MATCH FULLInserting values into the table using the
See also
Was this helpful?
What is primary key and its importance?A primary key is the column or columns that contain values that uniquely identify each row in a table. A database table must have a primary key for Optim to insert, update, restore, or delete data from a database table. Optim uses primary keys that are defined to the database.
What is primary key and example?Primary key
A primary key is a column -- or a group of columns -- in a table that uniquely identifies the rows of data in that table. For example, in the table below, CustomerNo, which displays the ID number assigned to different customers, is the primary key. CUSTOMERS. CustomerNo. FirstName.
What is primary key and secondary key?A primary key is the field in a database that is the primary key used to uniquely identify a record in a database. A secondary key is an additional key, or alternate key, which can be use in addition to the primary key to locate specific data.
Which of the following statements about the primary key for a table is not true?The statement that is false regarding primary key is: Only parent tables must have a primary key. Because, a primary key must be unique, it can be made up of multiple attributes and it must not contain a null value.
|