So, as far as I know, PostgreSQL does not have any way of verifying the loss
of referential integrity.
Are there any recommended methods or utilities for checking referential
integrity in a PostgreSQL database? 5 4025
On Tue, Oct 05, 2004 at 02:03:09PM -0400, Geisler, Jim wrote: So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity.
.... just like it doesn't have a way of verifying loss of tables or any
other object. If someone messes up the schema (be it via ALTER commands
or directly modifying system catalogs), Postgres will continue working
with the new schema.
Are there any recommended methods or utilities for checking referential integrity in a PostgreSQL database?
Maybe do pg_dump -s periodically and compare to a known good version?
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Si no sabes adonde vas, es muy probable que acabes en otra parte.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Geisler, Jim wrote: So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity.
Are there any recommended methods or utilities for checking referential integrity in a PostgreSQL database?
Of course, Tom Lane suggested I look at the pg_trigger table. I suppose
I'll have to do this from time to time. His good point on an old
database system (7.1.3) like what is included within Red Hat AS 2.1 is
as he wrote, "Each FK constraint should have three associated triggers
(two on the referencing table, one on the referenced table). You can
sort out which is which by looking at the tgargs field --- note how the
referencing and referenced table and field names are embedded in that. I
suspect that some of these triggers got dropped or disabled.
If you don't find all three triggers for some one constraint, the best
bet is to drop any remaining triggers from the set and then issue
ALTERTABLE ADD FOREIGN KEY to re-make a consistent trigger set."
I did what he suggested and then re-created the offending table and
altered the other offending table. Now, my database has working
referential integrity between the two tables involved.
Jim Apsey
On Tue, 5 Oct 2004, Geisler, Jim wrote: So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity.
Are there any recommended methods or utilities for checking referential integrity in a PostgreSQL database?
Perhaps someone knows of a more "automatic" solution, but what I have done
is generate a test set that exercises as many aspects of the table design
as I have been able to imagine. It also tests pgsql functions, including
triggers. This is particularly useful with version changes. As the
occasional bug is found, more test cases are added. Like a unit or
regression test, of course.
Those more experienced than I probably have better ideas...
-frank
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
"Geisler, Jim" <jg******@vocol lect.com> writes: So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity.
What are you trying to accomplish here, and in what PG version?
Are you trying to check that PG thinks that a foreign-key relationship
is installed? In recent versions psql's "\d" will tell you that. If
you're dealing with an old version you might have to look directly at
the system catalogs.
Are you not trusting that an active foreign-key relationship has been
correctly enforced? Then I think you want to do some kind of JOIN
query to see if you can find any rows with no master row. (You could
actually do this by temporarily creating a new, redundant FK constraint;
but if you are feeling that paranoid you're likely not going to trust
the system's answer anyway...)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
Tom Lane wrote: "Geisler, Jim" <jg******@vocol lect.com> writes:
So, as far as I know, PostgreSQL does not have any way of verifying the loss of referential integrity.
What are you trying to accomplish here, and in what PG version?
Are you trying to check that PG thinks that a foreign-key relationship is installed? In recent versions psql's "\d" will tell you that. If you're dealing with an old version you might have to look directly at the system catalogs.
Are you not trusting that an active foreign-key relationship has been correctly enforced? Then I think you want to do some kind of JOIN query to see if you can find any rows with no master row. (You could actually do this by temporarily creating a new, redundant FK constraint; but if you are feeling that paranoid you're likely not going to trust the system's answer anyway...)
regards, tom lane
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
Of course, I use the most simple method of selecting all values which
are not in RI_table, e.g.
# select * from user_table where user_table.valu e not in (select
RI.value from RI_table);
I had to do this often when I ported from one Postgres-like database
(namely Illustra) into my current Postgres database. I noticed some
rows would not insert into my target table from a text file containing
my source table. So, I created a table like my desired target table but
without referential integrity. Then, on the table w/o RI I did the
above. But, as you can see, I do things as simply as possible.
With great regard for the pros out here in Postgres Land,
Jim Apsey This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: DS |
last post by:
How does one set-up a macro to do the following. When you enter an
Employee Number in a log-in box Access checks the Employee Table to see
if thats a valid Employee number. if it is the Macro continues along
its way, if not it gives a message box saying that "This is not a valid
Employee ID"
DS
|
by: Grant McLean |
last post by:
Hi
First a simple question ...
I have a table "access_log" that has foreign keys "app_id" and
"app_user_id" that reference the "application_type" and "app_user"
tables. When I insert into "access_log", the referential integrity
triggers generate these queries:
SELECT 1 FROM ONLY "public"."application_type" x
|
by: Jimmie H. Apsey |
last post by:
Referential Integrity on one of our production tables seems to have been
lost. I am running Postgres 7.1.3 embedded within Red Hat
kernel-2.4.9-e.49.
Within that I have a table with referential integrity constraints which
no longer work.
I do not know how to disable referential integrity on a column in a table.
I do not know how to view what Postgres thinks my referential integrity
|
by: heyvinay |
last post by:
I have transaction table where the rows entered into the transaction
can come a result of changes that take place if four different tables.
So the situation is as follows:
Transaction Table
-TranId
-Calc Amount
Table 1 (the amount is inserted into the transaction table)
- Tb1Id
|
by: Andrew R |
last post by:
Hi
I'm creating a series of forms, each with with around 15-20 text boxes.
The text boxes will show data from tables, but are unbound to make them
more flexible.
I want the form to be used for both adding new data and modifying
existing data. I have created a save button on the form.
When the user clicks the save button, the code checks to see if there
| |
by: moskie |
last post by:
Is there a way to run an alter table statement that adds a constraint
for a foreign key, but does *not* check the existing data for
refrential integrity? I'm essentially looking for the equivalent to SQL
Server's NOCHECK flag.
I am able to create equivalent keys in the Relationships screen, as
long as the "Enforce referential integrity" box is left unchecked. But
when I try to create that relationship with an ALTER TABLE statement, I...
|
by: CPAccess |
last post by:
How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table?
Here's the situation:
I have two tables: tblContracts and tblPayments. tblContracts has an autonumber field called IDKey as its primary key. tblPayments also has an IDKey field (Integer datatype). The two tables are linked in a one-to-many relationship on the field IDKey with referential integrity...
|
by: Wayne |
last post by:
I've inadvertently placed this post in another similar newgroup, and I
apologise if you get it twice.
I'm building a database that consists of frontend and backend. Some
of the lookup tables need to reside in the frontend database. The
data in the lookup fields that relate to the backend tables will
never
change, but a descriptor for items in these fields will. I may need
to update the descriptors periodically, hence the need to keep...
|
by: ApexData |
last post by:
Access2000, using a continuous form.
I’m getting a message that say “you cannot add or change a record
because a related record is required in table Employee”.
This occurs in all my combobox fields that have referential integrity
linked to another table. I do not have the field in the table set as
required, and I do not want to require the entry. If the record is
filled out completely and added to the table, you can go back to the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |