Toad World® Forums

Oracle to SQL Server RE forgets NOT NULL clause on PK


#1

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)

Cheerz, Bart


#2

Hi Bart,

During RE of Oracle, NotNull property at attributes is set up only if you set NotNull for the attribute during creation of the table. If you didn’t set up NotNull during creation of the table, the attribute looks like not null (thanks to the PK), but Not Null constraint is not defined there. TDM loads it as it is in the database.

Please find attached a script that will set up NotNull at attributes where PK is defined. You will not have to do your workaround.

Run the script in Scripting Window (Expert Mode must be on, Scripting Window is available in Tools menu).

If you have more questions, please write me back. Thanks.

Regards,

Vladka + Mario
NotNullSetInPKAttributes.txt (871 Bytes)