Re: Adding foreign key constraints without integrity check?

Florian,
I understand where you're coming from. Indexes are always unique and
all RDBMS systems use them to 'uniquely' identify a row from the the
perspective of internal software management. Index != PrimaryKey, so
every table created, despite any Primary/Foreign key contraints put on
them, always have a 1-1 Index per row entry. At least that's the way I
understand it, can someone else affirm this statement or redirect a
misguided 'me ;)'?

Thanks group,

Florian G. Pflug wrote:

> louis gonzales wrote:
>
>> Florian,
>> Are you certain:
>>
>> "You can only create an FK if the fields you are referencing in the
>> foreign table form a PK there. And creating a PK implicitly creates
>> an index, which you can't drop without dropping the PK :-("
>>
> Arg.. Should have written "unique index" instead of primary key..
> But it doesn't change much, since a unique index and a pk are nearly
> the same.
>
>> I'm not sure I am convinced the necessity of a foreign key, "needing"
>> to reference a primary keyed entry from a different table.
>
> I tried the following:
> create table a(id int4) ;
> create table b(id int4, a_id int4) ;
> alter table b add constraint pk foreign key (a_id) references a (id) ;
>
Not sure, but maybe the syntax on this is slightly ambiguous. Try
creating table b with a primary key constraint on a_id, then alter the
table to add foreign key constraint. I'm going to look up a couple of
references and see what I can dig up. That may be perfectly legitimate
syntax, but it just seems off to me.

Sorry if it is, I've spent the last few days on Oracle 9i, so I'm
jumping around in my memory.... quite a bit for validity amongst
different syntax.

> The alter table gave me an error stating that I need to have a unique
> index
> defined on a.id...
>

> greetings, Florian Pflug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend