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
