The table provides a comparison of the syntax used to define referential integrity constraints.
| Constraint | Oracle | Microsoft SQL Server |
|---|---|---|
| PRIMARY KEY | [CONSTRAINT constraint_name] PRIMARY KEY (col_name [, col_name2 [..., col_name16]]) [USING INDEX storage_parameters] |
[CONSTRAINT constraint_name] PRIMARY KEY [CLUSTERED | NONCLUSTERED] (col_name [, col_name2 [..., col_name16]]) [ON segment_name] [NOT FOR REPLICATION] |
| UNIQUE | [CONSTRAINT constraint_name] UNIQUE (col_name [, col_name2 [..., col_name16]]) [USING INDEX storage_parameters] |
[CONSTRAINT constraint_name] UNIQUE [CLUSTERED | NONCLUSTERED](col_name [, col_name2 [..., col_name16]]) [ON segment_name] [NOT FOR REPLICATION] |
| FOREIGN KEY | [CONSTRAINT constraint_name] [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])] REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] [ON DELETE CASCADE] |
[CONSTRAINT constraint_name] [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])] REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] [NOT FOR REPLICATION] |
| DEFAULT | Column property, not a constraint DEFAULT (constant_expression) |
[CONSTRAINT constraint_name] DEFAULT {constant_expression | niladic-function | NULL} [FOR col_name] [NOT FOR REPLICATION] |
| CHECK | [CONSTRAINT constraint_name] CHECK (expression) |
[CONSTRAINT constraint_name] CHECK [NOT FOR REPLICATION] (expression) |
The NOT FOR REPLICATION clause is used to suspend column-level, FOREIGN KEY, and CHECK constraints during replication.
The rules for defining foreign keys are similar in each RDBMS. The number of columns and data type of each column specified in the foreign key clause must match the REFERENCES clause. A nonnull value entered in this column(s) must exist in the table and column(s) defined in the REFERENCES clause, and the referenced table’s columns must have a PRIMARY KEY or UNIQUE constraint.
Microsoft SQL Server constraints provide the ability to reference tables within the same database. To implement referential integrity across databases, use table-based triggers.
Both Oracle and SQL Server support self-referenced tables, tables in which a reference (foreign key) can be placed against one or more columns on the same table. For example, the column prereq in the CLASS table can reference the column ccode in the CLASS table to ensure that a valid course code is entered as a course prerequisite.
Whereas cascading deletes and updates are implemented in Oracle with the CASCADE DELETE clause, SQL Server provides the same functionality with table triggers. For more information, see “SQL Language Support” later in this chapter.