skip to content »

Updating tables with foreign key constraints

updating tables with foreign key constraints-34

ALTER TABLE ADD PRIMARY KEY allows you to include existing columns in a primary key if they were first defined as NOT NULL. If the column(s) contain NULL values, the system will not add the primary key constraint. A table can have at most one PRIMARY KEY constraint.A UNIQUE constraint defines a set of columns that uniquely identify rows in a table only if all the key values are not NULL.

checks every foreign key constraint that references the key to make sure that the removal or modification of the row does not cause a constraint violation.If removal or modification of the row would cause a constraint violation, the update or delete is not permitted and ). FOREIGN KEY constraints generate non-unique indexes. UNIQUE constraints generate unique indexes if all the columns are non-nullable, and they generate non-unique indexes if one or more columns are nullable. These indexes are available to the optimizer for query optimization (see CREATE INDEX statement) and have system-generated names.Therefore, if a column or set of columns has a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint on it, you do not need to create an index on those columns for performance. You cannot drop backing indexes with a DROP INDEX statement; you must drop the constraint or the table.If one or more key parts are NULL, duplicate keys are allowed.For example, if there is a UNIQUE constraint on Foreign keys provide a way to enforce the referential integrity of a database.These clauses specify whether should modify corresponding foreign key values or disallow the operation, to keep foreign key relationships intact when a primary key value is updated or deleted from a table.

You specify the update and delete rule of a referential constraint when you define the referential constraint.

For a table-level foreign key constraint in which you specify the columns in the table that make up the constraint, you cannot use the same column more than once.

If there is a column list in the A foreign key constraint is satisfied if there is a matching value in the referenced unique or primary key column.

The update rule applies when a row of either the parent or dependent table is updated. When a value in a column of the parent table's primary key is updated and the update rule has been specified as RESTRICT, triggers have been executed.

If any row in a dependent table violates a foreign key constraint, the statement is rejected.

Check constraints specified as part of a table definition can have column references identifying columns previously defined in the CREATE TABLE statement.