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/
> ).
Yeah, there was a patch lik
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
refe
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
https://www.postgresql.org/docs
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 I
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.
- below
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.
- below
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:
https
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 tri
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 %', OL
> 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
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 yo
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, ordina
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-m
>>
>> 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
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, ord
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 wi
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 tri
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 seri
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 spl
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 a
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,
>PRI
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]> wr
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
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 (
> >>
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
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
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
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 tab
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.
>
> W
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 integ
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 disabling A's on-delete-trigger B will be be e
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, optio
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
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 i
seems
like they might not be even needed?
Not sure if that is what you are asking?
Oisin
- Original Message -
From: "Thomas F.O'Connell" <[EMAIL PROTECTED]>
To: "Andrus Moor" <[EMAIL PROTECTED]>
Cc:
Sent: Monday, March 28, 2005 10:35
Subject: Re:
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 guarantee
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 implemen
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
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 KE
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:
tr
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
-
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 --
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 constra
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
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
>>
>> s
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 er
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);
al
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
> compa
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 to old databases in the code itself.
So my question
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: referential
Hello,
While copying from a text file of tab delimited data I am getting
" ERROR: 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
"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
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
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 wi
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 RE
-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 REFER
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 associa
AIL PROTECTED]To: <[EMAIL PROTECTED]>
om> cc:
Subject: RE: [GENERAL] Referential
Integrity Problems
I'm rather new to Postgresql, but hope someone here can help me out. Even
though I am specifying foreign and primary keys in my tables as I create
them, the foreign key options, such as CASCADE and NO ACTION, don't seem to
be working. I am allowed to change primary keys with no effect on the
for
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 t
ovice listing, 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] Refer
Newbie question:
I don´t know how to work with referential integrity in Postgresql :
Could someone help me?
62 matches
Mail list logo
