Optimizer advantage for NOT NULL Check constraints on PK's?
Date: Wed, 6 Feb 2008 12:10:20 -0600
We have some databases in which the original columns were added with a NOT NULL constraint and a PK was added in another statement. Other databases only have the PK. When you compare these two databases you get a variation because the former has 'C' type constraint on the PK column as well as the PK constraint.
I am tempted to go add NOT NULL constraints on all PK columns as a rule or modify the columns to "foo data_type null" since the rules will still be enforced by the PK so that my db compares sync up. This is 9i and 10g by the way.
I would guess that the a Check constraint and PK are the same thing to to the optimizer and it does not matter one way or the other. Can anyone confirm if this in fact that case? Any issue with removing or adding these across the board (assuming only on PK columns).