Re: [GENERAL] referential integrity between elements of an array and another table?

Henry M writes: > No hesitation. I am just trying to understand the options. This blog seemed > to indicate the functionality existed but it looks like it was never > completed ( > https://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ > ).

Re: [GENERAL] referential integrity between elements of an array and another table?

For me, foreign keys for arrays would be incredibly helpful. For simple checkbox-type options, it's _much_ simpler to just put them in an array, and I work with a UI that can handle the arrays well. I do hope this makes it into Postgresql someday! In the meantime, I've mostly sacrificed the

Re: [GENERAL] referential integrity between elements of an array and another table?

No hesitation. I am just trying to understand the options. This blog seemed to indicate the functionality existed but it looks like it was never completed ( https://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ ). I see in this document

Re: [GENERAL] referential integrity between elements of an array and another table?

On 04/19/2017 12:13 PM, Henry M wrote: I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. The only thing

Re: [GENERAL] referential integrity between elements of an array and another table?

On 04/19/2017 01:13 PM, Henry M wrote: I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. -

Re: [GENERAL] referential integrity between elements of an array and another table?

On 04/19/2017 01:13 PM, Henry M wrote: I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. -

[GENERAL] referential integrity between elements of an array and another table?

I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. - below is the message from the reddit poster:

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

Richard Huxton wrote, On 15-Jul-2008 15:19: Sergey Konoplev wrote: Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

On Tue, Jul 15, 2008 at 7:17 PM, Richard Huxton [EMAIL PROTECTED] wrote: Sergey Konoplev wrote: Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

The code you posted is a clear case of doing things wrong deliberately. Yes It's just an example. My real code is more complex of course. In order to prevent this error, you would need to rewrite large parts of Postgres's code which checks referential integrity, and there would still be

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

Sergey Konoplev wrote: CREATE OR REPLACE FUNCTION fktrigfn() RETURNS TRIGGER AS $$ BEGIN PERFORM 1 FROM table1 WHERE a = OLD.aref; IF FOUND THEN RAISE NOTICE 'aborting delete for %', OLD.aref; RETURN NULL; ELSE RAISE NOTICE 'allowing delete for %', OLD.aref;

[GENERAL] Referential integrity vulnerability in 8.3.3

Hello community There is an oddity (or a bug) in situation with returning null before delete trigger and referential integrity in PG 8.3.3. I tryed to find a solution in Google and PG documentation and have noticed nothing useful. Let's start from tables creation. CREATE TABLE table1 ( id

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

Sergey Konoplev wrote: There is an oddity (or a bug) in situation with returning null before delete trigger and referential integrity in PG 8.3.3. I tryed to find a solution in Google and PG documentation and have noticed nothing useful. [snip] CREATE OR REPLACE FUNCTION tr_stop() RETURNS

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? On Tue, Jul 15, 2008 at 4:00 PM, Richard Huxton [EMAIL PROTECTED] wrote: Sergey Konoplev wrote: There is an oddity (or a bug) in situation with

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

Sergey Konoplev wrote: Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE trigger without this loophole. Don't forget,

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE trigger without this loophole. Don't forget, ordinary users can't

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

On Tue, Jul 15, 2008 at 06:02:27PM +0400, Sergey Konoplev wrote: Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE

Re: [GENERAL] Referential integrity vulnerability in 8.3.3

Sergey Konoplev wrote: Yes it is. But it the way to break integrity cos rows from table2 still refer to deleted rows from table1. So it conflicts with ideology isn't it? Yes, but I'm not sure you could have a sensible behaviour-modifying BEFORE trigger without this loophole. Don't forget,

Re: [GENERAL] referential integrity and defaults, DB design or trick

On Fri, 21 Dec 2007 08:19:08 + Richard Huxton [EMAIL PROTECTED] wrote: Ivan Sergio Borgonovo wrote: The default property (that is actually made by several fields) in my case is not completely homogeneous with the others, because it has a double meaning. It is cleaner to split the

Re: [GENERAL] referential integrity and defaults, DB design or trick

Ivan Sergio Borgonovo wrote: The default property (that is actually made by several fields) in my case is not completely homogeneous with the others, because it has a double meaning. It is cleaner to split the meanings and the data. It usually is. A lot of the design decisions you can regret

Re: [GENERAL] referential integrity and defaults, DB design or trick

On Wed, 19 Dec 2007 17:24:52 +0100 Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: I've something like this: create table i ( iid serial primary key, name varchar(32) ); create table p ( pid serial primary key, iid int references i(iid) on delete cascade,

Re: [GENERAL] referential integrity and defaults, DB design or trick

On Dec 20, 2007, at 8:50 AM, Ivan Sergio Borgonovo wrote: On Wed, 19 Dec 2007 17:24:52 +0100 Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: I've something like this: create table i ( iid serial primary key, name varchar(32) ); create table p ( pid serial primary key,

Re: [GENERAL] referential integrity and defaults, DB design or trick

On Thu, 20 Dec 2007 09:55:29 -0600 Erik Jones [EMAIL PROTECTED] wrote: On Dec 20, 2007, at 8:50 AM, Ivan Sergio Borgonovo wrote: On Wed, 19 Dec 2007 17:24:52 +0100 Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: I've something like this: create table i ( iid serial primary

Re: [GENERAL] referential integrity and defaults, DB design or trick

Ivan Sergio Borgonovo wrote: * i has a default property i.name he is hungry by default i.name=hungry * i has a set of other property he can chose from in p (thirsty, happy...) * c is the chosen property of the day (c actually will contain more than a p) c can contain: 1) sorry not chosen yet

Re: [GENERAL] referential integrity and defaults, DB design or trick

On Thu, Dec 20, 2007 at 06:31:47PM +0100, Ivan Sergio Borgonovo wrote: On Thu, 20 Dec 2007 09:55:29 -0600 Erik Jones [EMAIL PROTECTED] wrote: On Dec 20, 2007, at 8:50 AM, Ivan Sergio Borgonovo wrote: On Wed, 19 Dec 2007 17:24:52 +0100 Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: I've

Re: [GENERAL] referential integrity and defaults, DB design or trick

On Thu, 20 Dec 2007 17:53:23 + Richard Huxton [EMAIL PROTECTED] wrote: CREATE TABLE properties ( pid serial, name text, PRIMARY KEY (pid) ); CREATE TABLE user_default_property ( uid int NOT NULL REFERENCES users, pid int NOT NULL REFERENCES properties, PRIMARY KEY

[GENERAL] referential integrity and defaults, DB design or trick

I've something like this: create table i ( iid serial primary key, name varchar(32) ); create table p ( pid serial primary key, iid int references i(iid) on delete cascade, name varchar(32) ); create table c ( bid serial primary key, pid int

Re: [GENERAL] referential integrity without trigger

In article [EMAIL PROTECTED], Alexander Presber [EMAIL PROTECTED] writes: Hello everybody, Assuming I want to empty and refill table A (with roughly the same content, preferrably in one transaction) and don't want to completely empty a dependent table B but still keep referential integrity

Re: [GENERAL] referential integrity without trigger

On Thu, 9 Feb 2006, Alexander Presber wrote: Hello everybody, Assuming I want to empty and refill table A (with roughly the same content, preferrably in one transaction) and don't want to completely empty a dependent table B but still keep referential integrity after the commit. Without

Re: [GENERAL] referential integrity without trigger

Alexander Presber [EMAIL PROTECTED] writes: Is there a clever, general scheme to recheck and enforce foreign key contraints, after the responsible triggers have been disabled and reenabled? Drop the constraint (keep your fingers off the trigger, thank you ;-)). Modify the master table.

Re: [GENERAL] Referential integrity using constant in foreign key

Florian, Or you create a classifier_1 and a classifier_2 table, each containing only the column code. Then you can drop the category1 and category2 fields from info, and just point the foreign keys to the correct table. Thank you. I will probably go by this way. You can, optionally,

Re: [GENERAL] Referential integrity using constant in foreign key

Thomas F.O'Connell wrote: Referential integrity never dictates the need for dummy columns. If you have a column that you need to refer to a column in another table so strongly that you want the values always to be in sync, you create a foreign key, establishing referential integrity between a

Re: [GENERAL] Referential integrity using constant in foreign key

Andrus, it's still not clear to me that you're understanding the role of referential integrity in database design. It exists to guarantee that the values in a column in a given table correspond exactly to the values in a column in another table on a per-row basis. It does not exist to

Re: [GENERAL] Referential integrity using constant in foreign key

] Referential integrity using constant in foreign key Andrus, it's still not clear to me that you're understanding the role of referential integrity in database design. It exists to guarantee that the values in a column in a given table correspond exactly to the values in a column in another table

Re: [GENERAL] Referential integrity using constant in foreign key

Andrus Moor wrote: thank you for reply. There was a typo in my code. Second table should be CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), FOREIGN KEY ('1', code1) REFERENCES classifier, FOREIGN KEY ('2', code2) REFERENCES classifier ); I try to explain my problem more precicely. I can

Re: [GENERAL] Referential integrity using constant in foreign key

Thomas, thank you for reply. There was a typo in my code. Second table should be CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), FOREIGN KEY ('1', code1) REFERENCES classifier, FOREIGN KEY ('2', code2) REFERENCES classifier ); I try to explain my problem more precicely. I can

[GENERAL] Referential integrity using constant in foreign key

I need to create referential integrity constraints: CREATE TABLE classifier ( category CHAR(1), code CHAR(10), PRIMARY KEY (category,code) ); -- code1 references to category 1, -- code2 references to category 2 from classifier table. CREATE TABLE info ( code1 CHAR(10), code2 CHAR(10), FOREIGN

Re: [GENERAL] Referential integrity using constant in foreign key

It's somewhat unclear what you're attempting to do, here, but I'll give a shot at interpreting. Referential integrity lets you guarantee that values in a column or columns exist in a column or columns in another table. With classifier as you've defined it, if you want referential integrity in

Re: [GENERAL] referential integrity preventing simultaneous insert

On Thu, Sep 09, 2004 at 12:53:57PM -0500, Edwin Grubbs wrote: I have experienced problems with postgres hanging when two inserts reference the same foreign key. It appears that the second insert is waiting for the first insert to release a lock. You can also create a deadlock situation:

Re: [GENERAL] referential integrity constraints not checked inside

Christian Rank wrote: create table a (n integer); create table b (n integer); alter table a add primary key (n); alter table b add foreign key (n) references a(n); Have you considered using on delete cascade in table b? -- jimoe at sohnen-moe dot com

[GENERAL] referential integrity constraints not checked inside PL/pgSQL functions?

Hello, I came across the following problem with integrity constraints and PL/pgSQL (PostgreSQL version used: 7.4.2): I defined the following tables, constraints and data: create table a (n integer); create table b (n integer); alter table a add primary key (n);

Re: [GENERAL] referential integrity constraints not checked inside PL/pgSQL functions?

On Thu, May 13, 2004 at 11:41:24AM +0200, Christian Rank wrote: create function f () returns void as ' begin delete from a; delete from b; return; end; ' language plpgsql; I would expect that select f(); yields an error message

Re: [GENERAL] referential integrity constraints not checked inside

Patrick Welche wrote: On Thu, May 13, 2004 at 11:41:24AM +0200, Christian Rank wrote: create function f () returns void as ' begin delete from a; delete from b; return; end; ' language plpgsql; I would expect that select f(); yields an

Re: [GENERAL] referential integrity constraints not checked inside PL/pgSQL functions?

Supposing you drop the delete from b; from the function, you'll find that the function fails with much the same error message you had before. Evidently that foreign key check gets _deferred_ in the context of the stored procedure. It is indeed checked; just not at the point you expect it to be

Re: [GENERAL] referential integrity constraints not checked inside

Christian Rank [EMAIL PROTECTED] writes: ... according to the docs, the validity of a constraint should be checked after each statement unless this behaviour is altered with a SET CONSTRAINTS statement. Statement means interactive command in that context --- in other words, the constraints

Re: [GENERAL] referential integrity constraints not checked inside

Tom Lane wrote: Christian Rank [EMAIL PROTECTED] writes: ... according to the docs, the validity of a constraint should be checked after each statement unless this behaviour is altered with a SET CONSTRAINTS statement. Statement means interactive command in that context --- in other

Re: [GENERAL] Referential Integrity -- SystemTables

On Mon, 3 Sep 2001, David M. Richter wrote: Hello! I want to update a old postgresdatabase. In the time of databasecreation the postgres had no references construct. So the integrity is maintained by the C-Driver. Now I want to build in the integrity. But i have to maintain compatibiliy

[GENERAL] referential integrity violation

Hello, While copying from a text file of tab delimited data I am getting ERROR: unnamed referential integrity violation - key referenced from films not found in sales The salesid field salesid INTEGER PRIMARY KEY DEFAULT NEXTVAL('sales_serial') exists Help =:-D Cheers Tony Grant

Re: [GENERAL] referential integrity violation

It means that the particular key value that is used in the referencing table does not have an associated value in the referenced table, not the columns themselves. On 30 Jul 2001, tony wrote: Hello, While copying from a text file of tab delimited data I am getting ERROR: unnamed

[GENERAL] referential integrity

Title: referential integrity Please could someone help me my database does not have referential integrity. I want to be able to delete a primary key in one table and for it to delete anything relating to it below ( same with update). i.e table product primary key product_name table

Re: [GENERAL] referential integrity

"Michelle Anderson" wrote: I get a message to screen that says NOTICE: CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented You need to upgrade to PostgreSQL 7.0.3; referential inegrity is not implemented in 6.5.3 and earlier. -- Oliver Elphick

Re: [GENERAL] Referential integrity question

In [EMAIL PROTECTED], on 11/05/00 at 10:12 AM, "Raymond O'Donnell" [EMAIL PROTECTED] said: Hi all, Is this normal? - I have discovered the hard way that permissions GRANTed to a user on one table influence that user's ability to update another table in a foreign key relationship with it.

[GENERAL] referential integrity

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It seems that cascading across multiple tables does not work correctly, when combining different action types. For example, given: CREATE TABLE a (anum Integer PRIMARY KEY); CREATE TABLE b (bnum Integer PRIMARY KEY, anum Integer

Re: [GENERAL] referential integrity

On Thu, 31 Aug 2000, Ian Turner wrote: It seems that cascading across multiple tables does not work correctly, when combining different action types. For example, given: CREATE TABLE a (anum Integer PRIMARY KEY); CREATE TABLE b (bnum Integer PRIMARY KEY, anum Integer

[GENERAL] Referential integrity

Hi again. Still new to databases, and I was wondering if I could get advice on how to set this up right. Essentially, I have a table, lets call it main, in which each row, describes another table in the database. I want to set it up so that, dropping the table will also drop the

Re: [GENERAL] Referential integrity

, search the "referential integrity" or/and "foreign key", you will find a lot. hope this will help. amy From: "Pablo Sentis" [EMAIL PROTECTED] To: "PostgreSQL mailing list" [EMAIL PROTECTED] Subject: [GENERAL] Referential integrity Date: Tue, 31 Aug 1999 12:09:

Re: [GENERAL] Referential integrity

Try using triggers: (see attached example) José Pablo Sentis ha scritto: Part 1.1Type: Plain Text (text/plain) Encoding: quoted-printable --Referential integrity: --1. Don't allow to add a detail without header --2. Delete all details in cascade if one decide to delete