[CONSTRAINT [constraint_name]
{unique_constraint |
primary_key_constraint |
foreign_key_constraint}]Values in the constrained column cannot be repeated, except in the case of null values. For example:
FOREIGN KEY (fcolumn_name [,fcolumn_name...])
REFERENCES ref_table (pcolumn_name [,pcolumn_name...])
[ON {DELETE | UPDATE}
{CASCADE | SET DEFAULT | SET NULL}]
◦ CASCADE specifies that all rows in the foreign key table that reference the deleted row in the primary key table are also deleted.
◦ SET DEFAULT specifies that the value of the foreign key column is set to the column default value for all rows in the foreign key table that reference the deleted row in the primary key table.
◦ SET NULL specifies that the value of the foreign key column is set to NULL for all rows in the foreign key table that reference the deleted row in the primary key table.
◦ CASCADE specifies that the value of the foreign key column for all rows in the foreign key table that reference the row in the primary key table that had the primary key updated are updated with the new primary key value.
◦ SET DEFAULT specifies that the value of the foreign key column is set to the column default value for all rows in the foreign key table that reference the row that had the primary key updated in the primary key table.
◦ SET NULL specifies that the value of the foreign key column is set to NULL for all rows in the foreign key table that reference the row that had the primary key updated in the primary key table.Assuming the current schema is PUBLIC, the emp table is created with the name, empId, and deptId columns. The table contains a foreign key constraint on the deptId column that references the id column in the dept table. In addition, it sets the value of any rows in the deptId column to NULL that point to a deleted row in the referenced dept table.
![]() |
© 2013 Progress Software Corporation and/or its subsidiaries or affiliates. |