Regardless of the database management system (DBMS) you are using, it is recommended that you always name your constraints. If you do not name your constraints, both Oracle and Microsoft® SQL Server™ name them for you. Because Oracle and SQL Server use different default naming conventions, the differences can unnecessarily complicate your migration process. The discrepancy would appear when dropping or disabling constraints because they must be dropped by name.
The ANSI standard requires that all values in a primary key are unique and that it does not allow null values. Both Oracle and SQL Server enforce uniqueness by creating unique indexes automatically whenever a PRIMARY KEY or UNIQUE constraint is defined. Additionally, primary key columns are defined automatically as NOT NULL. Only one primary key is allowed per table.
A SQL Server clustered index is created by default for a primary key. A nonclustered index can also be requested. The Oracle index can be removed by either dropping or disabling the constraint, whereas the SQL Server index can be removed only by dropping the constraint.
In either DBMS, alternate keys can be defined using a UNIQUE constraint. Multiple UNIQUE constraints can be defined on any table. UNIQUE constraint columns can be nullable. In SQL Server, a nonclustered index is created by default, unless specified otherwise.
Note SQL Server allows only one row to contain the value NULL for the complete unique key (single or multiple column index), while Oracle allows any number of rows to contain the value NULL for the complete unique key.
This table shows the constraint syntax.
| Oracle | SQL Server |
|---|---|
| CREATE TABLE DEPT_ADMIN.DEPT (DEPT VARCHAR2(4) NOT NULL, DNAME VARCHAR2(30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY (DEPT) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE (DNAME) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED) ) |
CREATE TABLE USER_DB.DEPT_ADMIN.DEPT (DEPT VARCHAR(4) NOT NULL, DNAME VARCHAR(30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY CLUSTERED (DEPT), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE NONCLUSTERED (DNAME) ) |
Oracle treats a default as a column property, while SQL Server treats a default as a constraint. The SQL Server DEFAULT constraint can contain constant values, built-in functions that do not take arguments (niladic-functions), or NULL.
Because you cannot apply a constraint name to the Oracle DEFAULT column property, it is recommended that you define DEFAULT constraints at the column level in SQL Server and do not apply constraint names to them. SQL Server generates a unique name for each DEFAULT constraint.
The syntax used to define CHECK constraints is the same in Oracle and SQL Server. The search condition must evaluate to a Boolean expression and cannot contain subqueries. A column-level CHECK constraint can reference only the constrained column, and a table-level check constraint can reference only columns of the constrained table.
Multiple CHECK constraints can be defined for a table. Unlike Oracle, SQL Server can define only one column-level CHECK constraint per column per CREATE TABLE statement (although each column constraint can have multiple conditions).
Converting the DBMS-specific syntax is the fundamental issue in migrating these constraints.
| Oracle | SQL Server |
|---|---|
| CREATE TABLE STUDENT_ADMIN.STUDENT ( SSN CHAR(9) NOT NULL, FNAME VARCHAR2(12) NULL, LNAME VARCHAR2(20) NOT NULL, GENDER CHAR(1) NOT NULL CONSTRAINT STUDENT_GENDER_CK CHECK (GENDER IN ('M','F')), MAJOR VARCHAR2(4) DEFAULT 'Undc' NOT NULL, BIRTH_DATE DATE NULL, TUITION_PAID NUMBER(12,2) NULL, TUITION_TOTAL NUMBER(12,2) NULL, START_DATE DATE NULL, GRAD_DATE DATE NULL, LOAN_AMOUNT NUMBER(12,2) NULL, DEGREE_PROGRAM CHAR(1) DEFAULT 'U' NOT NULL CONSTRAINT STUDENT_DEGREE_CK CHECK (DEGREE_PROGRAM IN ('U', 'M', 'P', 'D')), ... |
CREATE TABLE USER_DB.STUDENT _ADMIN.STUDENT ( SSN CHAR(9) NOT NULL, FNAME VARCHAR(12) NULL, LNAME VARCHAR(20) NOT NULL, GENDER CHAR(1) NOT NULL CONSTRAINT STUDENT_GENDER_CK CHECK (GENDER IN ('M','F')), MAJOR VARCHAR(4) DEFAULT 'Undc' NOT NULL, BIRTH_DATE DATETIME NULL, TUITION_PAID NUMERIC(12,2) NULL, TUITION_TOTAL NUMERIC(12,2) NULL, START_DATE DATETIME NULL, GRAD_DATE DATETIME NULL, LOAN_AMOUNT NUMERIC(12,2) NULL, DEGREE_PROGRAM CHAR(1) DEFAULT 'U' NOT NULL CONSTRAINT STUDENT_DEGREE_CK CHECK (DEGREE_PROGRAM IN ('U', 'M', 'P', 'D')), ... |
An Oracle table column always defaults to NULL unless specified in the CREATE TABLE or ALTER TABLE statements. In SQL Server, database and session settings can influence and possibly override the nullability of the data type used in a column definition. For more information, see CREATE TABLE.
To avoid possible confusion when migrating, it is recommended that all of your SQL scripts (whether Oracle or SQL Server) explicitly define both NULL and NOT NULL.
The rules for defining foreign keys are similar in Oracle and SQL Server. The number of columns and data types 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.
The SQL Server constraints can reference only tables within the same database. Table-based triggers must be used to simulate referential integrity across databases.
Both Oracle and SQL Server support self-referenced tables. These are tables in which a reference (foreign key) can be placed against one or more columns on the same table.
Regardless of DBMS, foreign key constraints do not create an index. For performance reasons, it is recommended that you index all of your foreign keys. This allows for faster execution times when a referenced key is modified or used for join purposes.
The Oracle CASCADE DELETE references option is not available with SQL Server. This option is used in situations where both the parent and child values are deleted when a parent row is deleted. In SQL Server, this option must be enforced with table triggers. For more information, see Nested Triggers.
Disabling constraints improves performance and streamlines the data replication processes. For example, when you rebuild or replicate table data at a remote site, you do not have to repeat constraint checks because the integrity of the data was checked when it was entered into the table. If your Oracle application currently disables and enables constraints (except for PRIMARY KEYand UNIQUE), this process can be duplicated in SQL Server using the CHECK and WITH NOCHECK options with the ALTER TABLE statement.
You can defer all constraints for the table, including the CHECK and FOREIGN KEY constraints, by using the ALL keyword. However, you cannot defer PRIMARY KEY and UNIQUE constraints because these constraints must be dropped.
If your Oracle application disables or drops PRIMARY KEY or UNIQUE constraints using the CASCADE option, you may need to rewrite some of your program code. This is because the CASCADE option disables or drops both the parent and any related child integrity constraints.
This is an example of the syntax:
DROP CONSTRAINT DEPT_DEPT_PK CASCADE
SQL Server does not support this cascading capability. The SQL Server application must be modified to first drop the child constraints followed by the parent constraints. For example, in order to drop the PRIMARY KEY constraint on the DEPT table, the foreign keys for the columns STUDENT.MAJOR and CLASS.DEPT must be dropped. This is an example of the syntax:
ALTER TABLE STUDENT
DROP CONSTRAINT STUDENT_MAJOR_FK
ALTER TABLE CLASS
DROP CONSTRAINT CLASS_DEPT_FK
ALTER TABLE DEPT
DROP CONSTRAINT DEPT_DEPT_PK
The ALTER TABLE syntax that is used to add and drop constraints is almost identical for Oracle and SQL Server.