Domain integrity enforces valid entries for a given column. Domain integrity is enforced by restricting the type (through data types), the format (through CHECK constraints), or the range of possible values (through REFERENCE and CHECK constraints).
Oracle treats a default as a column property, and Microsoft 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.
To easily migrate the Oracle DEFAULT column property, you should define DEFAULT constraints at the column level in SQL Server without applying constraint names. 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. SQL Server syntax allows only one column-level CHECK constraint to be created on a column in a CREATE TABLE statement, and the constraint can have multiple conditions.
The best way to test your modified CREATE TABLE statements is to use the SQL Server Query Analyzer in SQL Server, and parse only the syntax. The results pane indicate any errors. For more information about constraint syntax, see SQL Server Books Online.
| Oracle | Microsoft 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')), ... |
A note about user-defined rules and defaults: The syntax for Microsoft SQL Server rules and defaults remains for backward compatibility purposes, but CHECK constraints and DEFAULT constraints are recommended for new application development. For more information, see SQL Server Books Online.
Microsoft SQL Server and Oracle create column constraints to enforce nullability. An Oracle column defaults to NULL, unless NOT NULL is specified in the CREATE TABLE or ALTER TABLE statements. In Microsoft SQL Server, database and session settings can override the nullability of the data type used in a column definition.
All of your SQL scripts (whether Oracle or SQL Server) should explicitly define both NULL and NOT NULL for each column. To see how this strategy is implemented, see Oratable.sql and Sstable.sql, the sample table creation scripts. When not explicitly specified, column nullability follows these rules.
| Null settings | Description |
|---|---|
| Column is defined with a user-defined data type | SQL Server uses the nullability specified when the data type was created. Use the sp_help system stored procedure to get the data type’s default nullability. |
| Column is defined with a system-supplied data type | If the system-supplied data type has only one option, it takes precedence. Currently, the bit data type can be defined only as NOT NULL. If any session settings are ON (turned on with the SET), then: If ANSI_NULL_DFLT_ON is ON, NULL is assigned. If ANSI_NULL_DFLT_OFF is ON, NOT NULL is assigned. If any database settings are configured (changed with the sp_dboption system stored procedure), then: If ANSI null default is true, NULL is assigned. If ANSI null default is false, NOT NULL is assigned. |
| NULL/NOT NULL Not defined |
When not explicitly defined (neither of the ANSI_NULL_DFLT options are set), the session has not been changed and the database is set to the default (ANSI null default is false), then SQL Server assigns it NOT NULL. |