The following syntax is allowed in Oracle, but not in SQL Server.
CREATE TABLE DEPARTMENTS
“DEPARTMENT_ID” NUMBER (4, 0),
– Add keys for table HR8.DEPARTMENTS
ALTER TABLE DEPARTMENTS ADD CONSTRAINT “DEPT_ID_PK” PRIMARY KEY (“DEPARTMENT_ID”)
When executed you will get
SQL Server Database Error: Cannot define PRIMARY KEY constraint on nullable column in table ‘DEPARTMENTS’.
When reverse enginering this table from Oracle, the PK is recognized by TDM, but NOT NULL is not selected. When generating DDL for Oracle, this will not cause a problem.
But when converting this Oracle Model to e.g. a SQL Server 2005 model, NOT NULL is still unselected, and the generated DDL does not include it either.
The same happens when a PK is a Varchar2
For a workaround you have to go back in the the model, deselect the PK from the column, select the NOT NULL, and select PK.
Since a PK is always not nullable, thic could be checked automatically (imho)