-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello,
I have problem with foreign keys on postgresql-7.3.2-1PGDG.
I have two tables, sorry for the lenght, but I want to give you complete
information.
Table qmail_account has FK on zone(name). On some occasions when I want
to insert data to qmail_account I get the RI error that the value of
qmail_account.domain was not found in zone.name. I'm sure it is there.
Maybe there is some problem with index zone_name_key.
Originaly I had this problem with zone.name and qmail_account.domain
defined as varchar(100), so I changed it to char(100), but it didn't help.
This problem occurs on some domains only - but on these domains it gives
the error every time.
I realized that when I insert the data with the "on-insert-working" domain
and then update it to the "on-insert-not-working" domain, the update
works.
As I have a trigger on qmail_account which checks some values and sets
them if they are empty. "home" is filled different ways for public and
private domains, so I use select query to the "zone".
When I specify "home" at insert, it works even with domains it failed with
empty and then auto-filled "home".
The trigger is run before insert/update on qmail_account. The trigger
doesn't change the value of NEW.domain in any way.
So I'd like to know if there is something I can do to make the insert
working with the trigger.
cust=# \d zone
Table "public.zone"
Column | Type | Modifiers
- -------------+------------------------+------------------------------------
id | integer | not null default ...
| | ... nextval('zone_id_seq'::text)
contractid | integer | not null
name | character(100) | not null
public_mail | boolean | not null default '0'
public_web | boolean | not null default '0'
ns1id | integer | not null default '0'
ns2id | integer | default '0'
ns3id | integer | default '0'
mx0id | integer | not null default '0'
mx10id | integer | default '0'
mx100id | integer | default '0'
expire | date |
extra | text | default ''
comment | character varying(200) | default ''
Indexes: zone_pkey primary key btree (id),
zone_name_key unique btree (name),
zone_contractid_idx btree (contractid)
cust=# \d qmail_account
Table "public.qmail_account"
Column | Type | Modifiers
- ---------------+-------------------------+----------------------------------
id | integer | not null default ...
| | ... nextval('qmail_account_id_seq'::text)
contractid | integer | not null
username | character varying(50) | not null
password | character varying(50) |
passwordenc | boolean | default 'n'
uid | integer | default '8'
gid | integer | default '11'
home | character varying(100) |
hardquota | integer | default '50'
valid_from | timestamp w/o time zone | default 'now()'
valid_to | timestamp w/o time zone |
enabled | boolean | default '1'
use_dot_qmail | boolean | default '0'
lastlogin | timestamp w/o time zone |
lastdelivery | timestamp w/o time zone |
comment | character varying(200) | default ''
home_old | character varying(100) | default ''
domain | character(100) | not null
Indexes: qmail_account_pkey primary key btree (id),
qmail_account_email_idx unique btree (username, "domain"),
qmail_account_contractid_idx btree (contractid),
qmail_account_username_idx btree (username)
Foreign Key constraints: $2 FOREIGN KEY ("domain") REFERENCES "zone"(name)
ON UPDATE CASCADE ON DELETE NO ACTION,
$1 FOREIGN KEY (contractid) REFERENCES
contract(id) ON UPDATE NO ACTION ON DELETE NO
ACTION
Triggers: log,
qmail_account_check
- --
Martin Edlman
Fortech s.r.o, Litomysl
Public PGP key: http://edas.visaci.cz/#keys
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQE/Vu/FoFlEKJy9loQRAvxiAJ9An+DuvgFh/jyXgCDQrHoDS/s9YQCeL1uF
t/2b7ZRSN+xK6tWSFZPe16o=
=anYf
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
