Primary Key

[MG]

I need to remove the restriction that imposes that, if a field in a
table is a primary key it's mandatory. I need to remove this to allow
the final script to be something like:

CREATE TABLE TABLE_4
(
COLUMN_1 RAW,
CONSTRAINT PK_TABLE_4 PRIMARY KEY (COLUMN_1)
);

This is because, in Oracle the column will be always validated for
null values because of the PK constraint and if the not null is
defined then another not null constraint is generated without being
necessary.

Can anyone point me in a the good direction?

[rkkier]

Have you verified in the database that this is a problem? I'd check
the system catalogs and verify that

1) you are actually getting two not-null constraints on the column
2) the database is actually doing some extra work because of this (is
there a performance impact)

We're using PowerDesigner to model hundreds of Oracle databases and
haven't bothered with this. Before you start tweaking the tool (a
tweak you'll need to carry forward every time you upgrade), you should
make sure it's necessary. If you're not sure how to verify what's
happing in the database, I'd check www.asktom.com. This sounds like a
question he'll have answered at some point. An Oracle PK constraint
is a combination of two distinct things: not null and unique. A
unique constraint (an index under the hood) by itself doesn't include
nulls (i.e. every row in the table can be null but once there's a
value in it they better be unique) so I'd be surprised if the DDL in
question is actually causing a problem.

[MG]

Have you verified in the database that this is a problem?  I'd check
the system catalogs and verify that
1) you are actually getting two not-null constraints on the column
2) the database is actually doing some extra work because of this (is
there a performance impact)
We're using PowerDesigner to model hundreds of Oracle databases and
haven't bothered with this.  Before you start tweaking the tool (a
tweak you'll need to carry forward every time you upgrade), you should
make sure it's necessary.  If you're not sure how to verify what's
happing in the database, I'd checkwww.asktom.com.  This sounds like a
question he'll have answered at some point.  An Oracle PK constraint
is a combination of two distinct things:  not null and unique.  A
unique constraint (an index under the hood) by itself doesn't include
nulls (i.e. every row in the table can be null but once there's a
value in it they better be unique) so I'd be surprised if the DDL in
question is actually causing a problem.

You are correct and thats exactly my problem. I don't want to have a
primary key constraint and a not null constraint. The recommendation
that I see for Oracle in some places is to create the DDL without the
NOT NULL for the PK, however Power Designer creates automatically
this.. And oracle, when it sees NOT NULL creates automatically the not
null constraint.
But in your case you don't get the NOT NULL constraints when you
generate the database?

[Mark Brady]

Post by MG

Have you verified in the database that this is a problem?  I'd check
the system catalogs and verify that
1) you are actually getting two not-null constraints on the column
2) the database is actually doing some extra work because of this (is
there a performance impact)
We're using PowerDesigner to model hundreds of Oracle databases and
haven't bothered with this.  Before you start tweaking the tool (a
tweak you'll need to carry forward every time you upgrade), you should
make sure it's necessary.  If you're not sure how to verify what's
happing in the database, I'd checkwww.asktom.com.  This sounds like a
question he'll have answered at some point.  An Oracle PK constraint
is a combination of two distinct things:  not null and unique.  A
unique constraint (an index under the hood) by itself doesn't include
nulls (i.e. every row in the table can be null but once there's a
value in it they better be unique) so I'd be surprised if the DDL in
question is actually causing a problem.

You are correct and thats exactly my problem. I don't want to have a
primary key constraint and a not null constraint.  The recommendation
that I see for Oracle in some places is to create the DDL without the
NOT NULL for the PK, however Power Designer creates automatically
this.. And oracle, when it sees NOT NULL creates automatically the not
null constraint.
But in your case you don't get the NOT NULL constraints when you
generate the database?

Please post links to that article which tells you to do so. If it's on
DBA-ORACLE.com it's wrong.