Обсуждение: many columns with references to one table

Hi,

What is the problem with the following table declaration?

CREATE TABLE persons (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  bdate DATE,
  address INTEGER REFERENCES addresses,
  phonepriv INTEGER REFERENCES phones,
  phoneday INTEGER REFERENCES phones,
  phonemobil INTEGER REFERENCES phones,
  email INTEGER REFERENCES emails,
  mate INTEGER REFERENCES persons,
  updated DATE NOT NULL
);

My problem is that the references to the phones table do not get
restricted, i.e. I can insert any value in the phone*-fields, while the
references to the other tables are restricted to values that allready
exists. The table declaration of phones should be all right, it has a
primary key defined like every other tables I have. Is there a
restriciton that no more then 1 column may reference an other table?

Regards

Andreas Fromm

On Tue, 26 Aug 2003, Andreas Fromm wrote:

> Hi,
>
> What is the problem with the following table declaration?
>
> CREATE TABLE persons (
>   id SERIAL PRIMARY KEY,
>   name TEXT NOT NULL,
>   bdate DATE,
>   address INTEGER REFERENCES addresses,
>   phonepriv INTEGER REFERENCES phones,
>   phoneday INTEGER REFERENCES phones,
>   phonemobil INTEGER REFERENCES phones,
>   email INTEGER REFERENCES emails,
>   mate INTEGER REFERENCES persons,
>   updated DATE NOT NULL
> );
>
> My problem is that the references to the phones table do not get
> restricted, i.e. I can insert any value in the phone*-fields, while the
> references to the other tables are restricted to values that allready
> exists. The table declaration of phones should be all right, it has a
> primary key defined like every other tables I have. Is there a
> restriciton that no more then 1 column may reference an other table?

Not as far as I know (and I've done multiple references to the same table
in the pash) with the exception that versions before either 7.3 or 7.2 are
going to have problems with referential actions other than NO ACTION in
such cases.

Can you make a complete standalone example?

Tom Lane wrote:

>Andreas Fromm <Andreas.Fromm@physik.uni-erlangen.de> writes:
>
>
>>My problem is that the references to the phones table do not get
>>restricted, i.e. I can insert any value in the phone*-fields, while the
>>references to the other tables are restricted to values that allready
>>exists.
>>
>>
>
>Hard to believe --- and I couldn't reproduce it here.  What PG version
>are you using?  Can you show us a complete example?
>
>            regards, tom lane
>
>
Well, the demonstration effect...
After a complete rewrite of my Database-generating sql-file the problem
is solved. Don't know what I did wrong the first time, but it's working
now. The curious thing is that I didn't made anything different in this
part.

Thanks

Andreas

You haven't given the table/column that it references, just the column I
assume.

Andreas Fromm wrote:

> Hi,
>
> What is the problem with the following table declaration?
>
> CREATE TABLE persons (
>  id SERIAL PRIMARY KEY,
>  name TEXT NOT NULL,
>  bdate DATE,
>  address INTEGER REFERENCES addresses,
>  phonepriv INTEGER REFERENCES phones,
>  phoneday INTEGER REFERENCES phones,
>  phonemobil INTEGER REFERENCES phones,
>  email INTEGER REFERENCES emails,
>  mate INTEGER REFERENCES persons,
>  updated DATE NOT NULL
> );
>
> My problem is that the references to the phones table do not get
> restricted, i.e. I can insert any value in the phone*-fields, while
> the references to the other tables are restricted to values that
> allready exists. The table declaration of phones should be all right,
> it has a primary key defined like every other tables I have. Is there
> a restriciton that no more then 1 column may reference an other table?
>
> Regards
>
> Andreas Fromm
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Andreas Fromm <Andreas.Fromm@physik.uni-erlangen.de> writes:
> My problem is that the references to the phones table do not get
> restricted, i.e. I can insert any value in the phone*-fields, while the
> references to the other tables are restricted to values that allready
> exists.

Hard to believe --- and I couldn't reproduce it here.  What PG version
are you using?  Can you show us a complete example?

            regards, tom lane