Problem with FKEYS

Lists: pgsql-general
From: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with FKEYS
Date: 2003-11-12 11:06:21
Message-ID: 200311121636.21553.mallah@trade-india.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

How come pgsql7.3.4 Was allowing me to delete the master record while
referencing records were present in slave table?

The problem was detected when the database was migrated to 7.4 and
deletions were being refused.

Regards
Mallah

tradein_clients=# \d user_services
Table "public.user_services"
Column | Type | Modifiers
------------+---------+-----------
userid | integer | not null
service_id | integer | not null
Indexes: user_services_key unique btree (userid, service_id),
user_services_service_id btree (service_id)
Foreign Key constraints: $2 FOREIGN KEY (userid) REFERENCES users(userid) ON UPDATE NO ACTION ON DELETE NO ACTION,
$1 FOREIGN KEY (service_id) REFERENCES services_master(service_id) ON
UPDATE NO ACTION ON DELETE NO ACTION

tradein_clients=# SELECT * from user_services where userid=276720 ;
userid | service_id
--------+------------
276720 | 1
(1 row)

tradein_clients=# begin work;DELETE from users where userid=276720 ;rollback;
BEGIN
DELETE 1
ROLLBACK
tradein_clients=# SELECT version();
version
-------------------------------------------------------------
PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

tradein_clients=#

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Problem with FKEYS
Date: 2003-11-12 15:40:17
Message-ID: 8857.1068651617@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

Rajesh Kumar Mallah <mallah(at)trade-india(dot)com> writes:
> How come pgsql7.3.4 Was allowing me to delete the master record while
> referencing records were present in slave table?

The only explanation I can think of is that the referencing row shown as
being in user_services was actually in a child table --- foreign keys
don't work in inheritance hierarchies at the moment.

If that's not it, can you provide a self-contained example?

regards, tom lane

From: Rajesh Kumar Mallah <mallah(at)trade-india(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Problem with FKEYS
Date: 2003-11-13 16:24:29
Message-ID: 3FB3B03D.9030809@trade-india.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Tom Lane wrote:<br>
<blockquote type="cite" cite="mid8857(dot)1068651617(at)sss(dot)pgh(dot)pa(dot)us">
<pre wrap="">Rajesh Kumar Mallah <a class="moz-txt-link-rfc2396E" href="mailto:mallah(at)trade-india(dot)com">&lt;mallah(at)trade-india(dot)com&gt;</a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">How come pgsql7.3.4 Was allowing me to delete the master record while
referencing records were present in slave table?
</pre>
</blockquote>
<pre wrap=""><!---->
The only explanation I can think of is that the referencing row shown as
being in user_services was actually in a child table --- foreign keys
don't work in inheritance hierarchies at the moment.</pre>
</blockquote>
<br>
No, That is not the case , they are plain tables.<br>
<br>
<blockquote type="cite" cite="mid8857(dot)1068651617(at)sss(dot)pgh(dot)pa(dot)us">
<pre wrap="">

If that's not it, can you provide a self-contained example?</pre>
</blockquote>
Could not replicate it with newly created tables. Only those set of
table<br>
has the problem. I am ready to provide any required info .<br>
<br>
Regds<br>
Mallah.<br>
<br>
<blockquote type="cite" cite="mid8857(dot)1068651617(at)sss(dot)pgh(dot)pa(dot)us">
<pre wrap="">

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

<a class="moz-txt-link-freetext" href="http://archives.postgresql.org">http://archives.postgresql.org</a>
</pre>
</blockquote>
<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 1.6 KB