Pk and FK finder script
Baqir Hussain, February 05, 2004 - 6:34 pm UTC
Tom,
I would very much appreciate if you please show me how to get Primary Key and Foreign Key sql syntax seperately
And there are compond columns in the primary key.
1. The following script only captures one coulmn when extracting PRIMAY KEY SQL syntax. How can I managed to get the complete PRIMARY KEY SQL syntax. Thanks
select 'alter table '||cns.table_name||' add constraint '||cns.CONSTRAINT_NAME||' PRIMARY KEY '||'('|
|cls.COLUMN_NAME||');'
from
all_constraints cns,
all_cons_columns cls
where
cns.CONSTRAINT_TYPE='P'
and
cns.constraint_name=cls.constraint_name
order by cns.table_name;
2. The following script will give RI SQL syntax. Please review/edit it. Thanks
select
'alter table '||cns.table_name||' add constraint '||cns.CONSTRAINT_NAME
||' FOREIGN KEY '||'('||cls.COLUMN_NAME||') references '
||clf.table_name||' ('||clf.COLUMN_NAME||');' from
all_cons_columns clf ,
all_constraint cns,
all_cons_columns cls
where cns.CONSTRAINT_TYPE='R'
and cns.constraint_name=cls.constraint_name
and clf.CONSTRAINT_NAME = cns.R_CONSTRAINT_NAME
and clf.OWNER = cns.OWNER
and clf.POSITION = clf.POSITION
order by cns.table_name;
index or primary key
Nikunj Thaker, December 27, 2004 - 12:34 am UTC
Dear Tom,
If i have one table on that table i have idno field if i create primary key on it or if i create index on it what are the pros and cros of the both.
regards,
December 27, 2004 - 10:01 am UTC
an index is about trying to speed access.
a primary key is about conveying information, meta data, enforcing a business/data rule.
If you have a primary key -- it would be the wrongest thing not to create a primary key.
Oh wait, that was the original answer above though wasn't it :)
Primary Key with non-unique index
Parthiban Nagarajan, November 17, 2009 - 11:34 pm UTC
Hi Tom
In your follow-up to the starter question of this thread, you have mentioned as below.
<quote>
A primary key just happens to create a not null constraint and an index on the data (may be unique or non-unique index) in order to enforce itself
</quote>
Yes, I have seen a table in production environment whose primary key is associated with a non-unique index ... And after that, I simulated the same in my PC also.
If its a primary key, it makes more sense to only have Unique index associated with it to enforce the uniqueness.
May I know
(1) the explanation regarding the current behaviour
(2) your opinion on having only the primary key associated with unique index
November 23, 2009 - 2:47 pm UTC
(1) deferrable constraints. A primary key constraint may be deferrable - meaning, it can have duplicates within a transaction. Hence a unique index cannot be used.
ops$tkyte%ORA11GR2> create table t
2 ( x int constraint t_pk primary key deferrable )
3 /
Table created.
ops$tkyte%ORA11GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_PK NONUNIQUE
(2) "it depends". There are times when having it non-unique is handy - deferrable constraints for example.
Re: Primary key with non-unique index
Parthiban Nagarajan, November 19, 2009 - 6:48 am UTC
primary keys
sam, November 30, 2010 - 10:35 am UTC
Tom:
My understanding is that every table MUST have a primary key in relational design.
What cases do you see that you dont need a PK for a table.
November 30, 2010 - 12:10 pm UTC
technically you can create a table without a primary key - so the "must part" is an over statement.
SHOULD is the correct word perhaps.
And I think that every table SHOULD have a primary key. I don't know of a case otherwise (ok, maybe DUAL :) )
Primary kesy Internals
Ramana, December 08, 2010 - 11:50 am UTC
Hi Tom,
I have question primary key. How Oracle implementS primary keys internally. What happens in the data block level when create primary key and how it will detects when we are trying to insert record whether its duplicate or new record.
Could you please explain the algorithm.
Thanks & Regards,
Ramana.
December 08, 2010 - 12:21 pm UTC
there is nothing special at the bits and bytes level about a primary key. A primary key is simply a set of one or more attributes such that:
a) the attribute values are NOT-null (enforced via a constraint)
b) the attribute values are unique (enforced via a constraint and an index)
We use an index on the primary key attributes (or at least on the primary key attributes - the index may have other attributes as well) to enforce uniqueness.
A reader, December 08, 2010 - 1:48 pm UTC
Hi Tom,
In which case oracle use non-unique index to enforce primary key? should be the primary key at the leading edge of
non-unique index?
many thanks
December 08, 2010 - 2:24 pm UTC
Oracle will use a non-unique index to enforce a primary key when...
a) the primary key constraint is defined as deferrable - meaning, the constraint can be momentarily violated in a transaction - duplicates CAN exist in a single transaction - but by the time it commits, the values will be unique.
b) there is an existing non-unique index on the set of attributes in the primary key
Yes, the primary key columns must be first in the index in order for the index to be used to enforce the constraints. An index on t(a,b,c) could be used to enforce a primary key on (a,b) or (b,a) but not on (b,c) for example.
A reader, December 08, 2010 - 2:56 pm UTC
Hi Tom,
Thanks for reply
why oracle not use if the column are not on leading edge of index?
could you please explain
Thanks for your valuble time
December 09, 2010 - 10:22 am UTC
suppose you have a phone book. Further, assume that in this world - first names are unique.
It would be very hard to try to use a phone book (where the index is on last_name, first_name) to find out if 'Tom' existed. It would not be very efficient - we'd have to scan the entire index.
That is why - the primary key attributes need to be first so we can actually do the duplicate checking. If they weren't, we'd have to look at the entire index.