Florian,
So if you:
create table test (
id varchar(2) primary key,
age int );
create table test2 (
id varchar(2) primary key,
age2 int );
alter table test2 add foreign key (id) references test (id);
\d test2
you'll see that attribute "id" from test2, now has both a primary key
constraint and a foreign key that references the primary key of test.
perhaps you can assert two constraints at the same time during an "alter
table ..." not sure why your example syntax is failing
louis gonzales wrote:
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings