The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.This page explains how Prisma can connect to a Microsoft SQL Server database. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. SQL FOREIGN KEY Constraint.
This means the REFERENCES clause must use exactly the same columns as a UNIQUE or PRIMARY KEY constraint on the referenced table. Referenced columns must contain only unique sets of values. For an example, see Add multiple foreign key constraints to a single column. A single column can have multiple foreign key constraints. A foreign key column cannot be a computed column. Foreign key columns must use their referenced column's type.
To avoid this, you can use a NOT NULL constraint on foreign keys when creating your tables.A NOT NULL constraint cannot be added to existing tables.By default, composite foreign keys are matched using the MATCH SIMPLE algorithm (which is the same default as Postgres). The write contains null values for at least one foreign key column (if MATCH SIMPLE is specified).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. The write contains null values for all foreign key columns (if MATCH FULL is specified).
In other words, MATCH FULL requires that if any column of a composite key is NULL, then all columns of the key must be NULL.For examples showing how these key matching algorithms work, see Match composite foreign keys with MATCH SIMPLE and MATCH FULL.CockroachDB does not support MATCH PARTIAL. How it worksFor matching purposes, composite foreign keys can be in one of three states:Valid: Keys that can be used for matching foreign key relationships.Invalid: Keys that will not be used for matching (including for any cascading operations).Unacceptable: Keys that cannot be inserted at all (an error is signalled).Valid keys may not contain any null values.Invalid keys contain one or more null values.Unacceptable keys do not exist from the point of view of MATCH SIMPLE all composite keys are acceptable.Unacceptable keys have any combination of both null and non-null values. If you had a composite foreign key constraint and have just upgraded to version 19.1, then please check that MATCH SIMPLE works for your schema and consider replacing that foreign key constraint with a MATCH FULL one. You can specify both MATCH FULL and MATCH SIMPLE.All composite key matches defined prior to version 19.1 use the MATCH SIMPLE comparison method.
If there are any existing references to the key being updated, the transaction will fail at the end of the statement. The key can be updated, depending on the ON UPDATE action.Default action. If there are any existing references to the key being deleted, the transaction will fail at the end of the statement.
If the default value for the column is null, or if no default value is provided and the column does not have a NOT NULL constraint, this will have the same effect as ON DELETE SET NULL or ON UPDATE SET NULL. The column must allow NULL or this update will fail.ON DELETE SET DEFAULT / ON UPDATE SET DEFAULTWhen a referenced foreign key is deleted or updated, the columns of all rows referencing that key are set to the default value for that column. If there are other alterations to the row, such as a SET NULL or SET DEFAULT, the delete will take precedence.Note that CASCADE does not list objects it drops or updates, so it should be used cautiously.When a referenced foreign key is deleted or updated, respectively, the columns of all rows referencing that key will be set to NULL. To set an existing foreign key action to RESTRICT, the foreign key constraint must be dropped and recreated.When a referenced foreign key is deleted or updated, all rows referencing that key are deleted or updated, respectively.
This means that DELETEs on referenced columns will fail, even though the second foreign key constraint ( fk_customer_2) is defined with the ON DELETE CASCADE action.> INSERT INTO parent VALUES ( 1 , 1 , 1 ), ( 2 , 1 , 1 ), ( 1 , 2 , 1 ), ( 1 , 1 , 2 ), ( NULL , NULL , NULL ), ( 1 , NULL , NULL ), ( NULL , 1 , NULL ), ( NULL , NULL , 1 ), ( 1 , 1 , NULL ), ( 1 , NULL , 1 ), ( NULL , 1 , 1 ) Now let's look at some INSERT statements to see how the different key matching algorithms work.Inserting values into the table using the MATCH SIMPLE algorithm (described above) gives the following results: StatementINSERT INTO simple_test VALUES (NULL,NULL,NULL)INSERT INTO simple_test VALUES (1,NULL,NULL)INSERT INTO simple_test VALUES (NULL,1,NULL)INSERT INTO simple_test VALUES (NULL,NULL,1)INSERT INTO simple_test VALUES (1,1,NULL)INSERT INTO simple_test VALUES (1,NULL,1)INSERT INTO simple_test VALUES (NULL,1,1)INSERT INTO simple_test VALUES (2,2,NULL)Inserting values into the table using the MATCH FULL algorithm (described above) gives the following results: StatementINSERT INTO full_test VALUES (NULL,NULL,NULL)INSERT INTO full_test VALUES (1,NULL,NULL)Can't mix null and non-null values in MATCH FULL.INSERT INTO full_test VALUES (NULL,1,NULL)INSERT INTO full_test VALUES (NULL,NULL,1) In this case, that will be the default action ( ON UPDATE NO ACTION ON DELETE NO ACTION) on the first foreign key constraint ( fk_customers).
0 kommentar(er)
