Re: [GENERAL] foreign key with char and varchar

Thomas Poty writes: > I wanted to test if char and varchar can be cross-referenced as foreign > key. So i did these tests : > ... > I thought the columns referring and referenced had to be the same data type > with the same length but it seems not to be the case. Looking

[GENERAL] foreign key with char and varchar

Hi world, I wanted to test if char and varchar can be cross-referenced as foreign key. So i did these tests : 1) Can a varchar(7) reference a char(2) ? PostgreSQL accepts it create table t1 (id char(2) primary key, data text); create table t2 (id char(2) primary key, data text, id_t1 varchar(7)

Re: [GENERAL] Foreign key references a unique index instead of a primary key

On Feb 23, 2017 12:42 PM, "Ivan Voras" wrote: Hello, I've inherited a situation where: - a table has both a primary key and a unique index on the same field. - at some time, a foreign key was added which references this table (actually, I'm not sure about the

Re: [GENERAL] Foreign key references a unique index instead of a primary key

On 02/23/2017 03:40 AM, Ivan Voras wrote: > Hello, > > I've inherited a situation where: > > * a table has both a primary key and a unique index on the same field. > * at some time, a foreign key was added which references this table > (actually, I'm not sure about the sequence of

[GENERAL] Foreign key references a unique index instead of a primary key

Hello, I've inherited a situation where: - a table has both a primary key and a unique index on the same field. - at some time, a foreign key was added which references this table (actually, I'm not sure about the sequence of events), which has ended up referencing the unique index

Re: [GENERAL] Foreign key against a partitioned table

ostgresql.org > *Subject:* [GENERAL] Foreign key against a partitioned table > > > > How do you create a foreign key that references a partitioned table? > > > > I'm splitting a large table "molecules" into 20 partitions, which also has > an associated "mol

Re: [GENERAL] Foreign key against a partitioned table

On 08/23/2016 01:00 PM, Craig James wrote: How do you create a foreign key that references a partitioned table? I'm splitting a large table "molecules" into 20 partitions, which also has an associated "molecular_properties" table. It looks something like this (pseudo-code): create table

Re: [GENERAL] Foreign key against a partitioned table

I have wondered if there were any plans to enhance fkey support for partitioned tables now that more work is being done on partitioning (I know there has been a large thread on declarative partitioning on hackers, though I haven't followed it too closely). Foreign keys are all done through

Re: [GENERAL] Foreign key against a partitioned table

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Craig James Sent: Tuesday, August 23, 2016 4:00 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Foreign key against a partitioned table How do you create a foreign key that references

[GENERAL] Foreign key against a partitioned table

How do you create a foreign key that references a partitioned table? I'm splitting a large table "molecules" into 20 partitions, which also has an associated "molecular_properties" table. It looks something like this (pseudo-code): create table molecules(molecule_idinteger primary key,

Re: [GENERAL] foreign key with where clause

On Thu, Aug 18, 2016 at 1:10 PM, Mark Lybarger wrote: > I have two tables that i want to link with a FK where the child table record > is "active". > > some googling shows that i could use a function and a check constraint on > the function, but that only works for inserts,

Re: [GENERAL] foreign key with where clause

My first instinct would be to look into triggers. In addition to an FK a(b_id) -> b(id), you could have an insert and update trigger on a(b_id) and b(active) to ensure the additional constraints. On Thu, Aug 18, 2016 at 1:10 PM, Mark Lybarger wrote: > I have two tables that

[GENERAL] foreign key with where clause

I have two tables that i want to link with a FK where the child table record is "active". some googling shows that i could use a function and a check constraint on the function, but that only works for inserts, not updates on table b. create table a (int id, text name); create table b (int id,

Re: [GENERAL] Foreign key triggers

Yes, foreign keys are implemented using triggers. Here is a blog post explaining a little more: http://bonesmoses.org/2014/05/14/foreign-keys-are-not-free/ I would assume it's still got to do a seq scan even on every referencing table even if it's empty for every record since there are no

[GENERAL] Foreign key triggers

Hi, I was trying to clean a database by deleting records of some of its tables. But in our model we have a table that is heavily referenced, that is, many tables reference this particular table by foreign key constraints. We don't have foreign key indexes, so executing a delete from mytable takes

[GENERAL] Foreign key referencing a VIEW

Hi all, I have a feature request for our dear fellow developpers : I would like to be able to create a foreign key constraint that references a view (or anything that looks like a table, as a function returning a table for instance) instead of a table. Does that look doable ? The trickiest part

Re: [GENERAL] foreign key to "some rows" of a second table

On 22/02/2016 13:03, Chris Withers wrote: Hi All, So, I have a table that looks like this: CREATE TABLE config ( regionvarchar(10), namevarchar(10), valuevarchar(40) ); Another looks like this: CREATE TABLE tag ( hostvarchar(10), typevarchar(10),

[GENERAL] foreign key to "some rows" of a second table

Hi All, So, I have a table that looks like this: CREATE TABLE config (     region    varchar(10),     name    varchar(10),     value    varchar(40) ); Another looks like this: CREATE TABLE tag (     host    varchar(10),    

[GENERAL] Foreign Key constraint violation

Hi all, I've discovered one of our databases has managed to get into a state where it is violating a foreign key constraint. The facts are these: Table B (row_id) references Table A (row_id). Table B contains multiple row_id's that do not exist in Table A. There also exists a BEFORE INSERT OR

Re: [GENERAL] Foreign Key violated

Apologies for not replying sooner. After a few days, we actually found out the cause was a user turning off all triggers on the table, forcing some data into it to try and solve an RMA issue manually, then turning the triggers back on. This hadn't showed up on any logs, and after finding zero

[GENERAL] foreign key to multiple tables depending on another column's value

Sorry, I wasn't sure what list I should be sending this question to... I have a multi-tenant-like application. We have a fields tree that we call a template, with something like this: fields(id, parent_id, name) And several other related tables. Since we started to support multiple

Re: [GENERAL] Foreign Key violated

On 23 May 2013 15:33, Thom Brown t...@linux.com wrote: On 23 May 2013 10:15, Keith Fiske ke...@omniti.com wrote: Client reported an issue where it appears a foreign key has been violated prod=#\d rma_items [snip] rma_items_rma_id_status_fk FOREIGN KEY (rma_id, rma_status) REFERENCES

Re: [GENERAL] foreign key to multiple tables depending on another column's value

On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas rr.ro...@gmail.com wrote: I know I could use a trigger, or some check constraint maybe, to ensure the field exists upon insert (or update), but I can't ensure the database will become inconsistent in case I remove a mapped field from

Re: [GENERAL] foreign key to multiple tables depending on another column's value

Em 29-05-2013 12:51, Vick Khera escreveu: On Wed, May 29, 2013 at 9:58 AM, Rodrigo Rosenfeld Rosas rr.ro...@gmail.com mailto:rr.ro...@gmail.com wrote: I know I could use a trigger, or some check constraint maybe, to ensure the field exists upon insert (or update), but I can't

[GENERAL] Foreign Key violated

Client reported an issue where it appears a foreign key has been violated prod=#\d rma_items [snip] rma_items_rma_id_status_fk FOREIGN KEY (rma_id, rma_status) REFERENCES rmas(id, status) ON UPDATE CASCADE ON DELETE CASCADE prod=# select i.rma_id, i.rma_status, r.id, r.status from rmas r join

Re: [GENERAL] Foreign Key violated

On 23 May 2013 10:15, Keith Fiske ke...@omniti.com wrote: Client reported an issue where it appears a foreign key has been violated prod=#\d rma_items [snip] rma_items_rma_id_status_fk FOREIGN KEY (rma_id, rma_status) REFERENCES rmas(id, status) ON UPDATE CASCADE ON DELETE CASCADE prod=#

Re: [GENERAL] Foreign Key violated

Hi, I'm not sure if I understand your issue, but could you output SELECT COUNT(*) FROM rmas WHERE id = 1008122437 AND status = 'r'; HTH Kind regards/met vriendelijke groet, Serge Fonville http://www.sergefonville.nl Convince Microsoft! They need to add TRUNCATE PARTITION in SQL Server

Re: [GENERAL] foreign key from array element

Hi Rafal, Il 24/09/12 07:54, Rafal Pietrak ha scritto: I did what I could - the review is on the hackers list. Thanks! That's much appreciated. Should I do anything else to finilize this review, like linking it to the pending patch? somehow? -R You should update the

Re: [GENERAL] foreign key from array element

On Tue, 2012-09-18 at 09:37 +0200, Albe Laurenz wrote: Rafal Pietrak wrote: On Mon, 2012-09-17 at 14:31 -0500, Merlin Moncure wrote: On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak ra...@zorro.isa-geek.com wrote: [] Who can review that patch? You :^) I did what I

Re: [GENERAL] foreign key from array element

Hi Chris, thank you very much for taking the time to read the article and get into the features proposed with our patch. On Tue, 18 Sep 2012 17:17:56 -0700, Chris Travers chris.trav...@gmail.com wrote: So those are the cautions and why I don't think a feature like this is suitable for

Re: [GENERAL] foreign key from array element

On Thu, Sep 20, 2012 at 12:18 AM, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: Hi Chris, thank you very much for taking the time to read the article and get into the features proposed with our patch. You are welcome. Also in case there is ambiguity, the feature I was

Re: [GENERAL] foreign key from array element

Ciao Rafal, You can download the refreshed version of the patch: http://archives.postgresql.org/message-id/1347983571.11539.14.ca...@greygoo.devise-it.lan 5. where do I get current-v9.3 from? git clone git://git.postgresql.org/git/postgresql.git cd postgresql git checkout -b aefk bzcat

Re: [GENERAL] foreign key from array element

On Tue, 2012-09-18 at 18:39 -0400, Tom Lane wrote: Rafal Pietrak ra...@zorro.isa-geek.com writes: postmaster/postmaster.o: In function `PostmasterMain':^M postmaster.c:(.text+0x4bc8): undefined reference to `optreset'^M tcop/postgres.o: In function `process_postgres_switches':^M

Re: [GENERAL] foreign key from array element

On Mon, 2012-09-17 at 14:31 -0500, Merlin Moncure wrote: On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak ra...@zorro.isa-geek.com wrote: On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote: On 09/17/2012 04:46 PM, Rafal Pietrak wrote: [--] There was some quite recent

Re: [GENERAL] foreign key from array element

Rafal Pietrak wrote: On Mon, 2012-09-17 at 14:31 -0500, Merlin Moncure wrote: On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak ra...@zorro.isa-geek.com wrote: On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote: On 09/17/2012 04:46 PM, Rafal Pietrak wrote: [--] There

Re: [GENERAL] foreign key from array element

On Tue, Sep 18, 2012 at 12:37 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote: You :^) The list of patches for the commitfest is here: https://commitfest.postgresql.org/action/commitfest_view?id=15 There is no reviewer for Array ELEMENT Foreign Keys yet. Silly question perhaps better

Re: [GENERAL] foreign key from array element

On Tue, 2012-09-18 at 09:37 +0200, Albe Laurenz wrote: Rafal Pietrak wrote: [--] Who can review that patch? You :^) ;7 OK. (still smiling a little) [---] Here is information about what is required: http://wiki.postgresql.org/wiki/Reviewing_a_Patch I

Re: [GENERAL] foreign key from array element

Hi Rafal, Il 18/09/12 13:00, Rafal Pietrak ha scritto: I did have a look, and feel slightly encouraged reading: Many people feel that they're not qualified; yes, that's me all right. Serously, I will try to do by best ... by the weekend, when I get some spare time. It is such a coincidence

Re: [GENERAL] foreign key from array element

Hi, On Tue, 2012-09-18 at 15:12 +0200, Gabriele Bartolini wrote: Hi Rafal, [] It is such a coincidence that yesterday I had started to write this article (http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/) about this feature for 9.3 and

Re: [GENERAL] foreign key from array element

Rafal Pietrak ra...@zorro.isa-geek.com wrote: where do I get current-v9.3 from? At this point 9.3 just means the HEAD of the master branch of the git repository, which is where development for the next major release of software is always done. For details of the public git repository see:

Re: [GENERAL] foreign key from array element

On Tue, 2012-09-18 at 15:38 -0500, Kevin Grittner wrote: Rafal Pietrak ra...@zorro.isa-geek.com wrote: where do I get current-v9.3 from? At this point 9.3 just means the HEAD of the master branch of the git repository, which is where development for the next major release of software is

Re: [GENERAL] foreign key from array element

Rafal Pietrak ra...@zorro.isa-geek.com writes: postmaster/postmaster.o: In function `PostmasterMain':^M postmaster.c:(.text+0x4bc8): undefined reference to `optreset'^M tcop/postgres.o: In function `process_postgres_switches':^M postgres.c:(.text+0x1422): undefined reference to `optreset'^M

Re: [GENERAL] foreign key from array element

On Tue, Sep 18, 2012 at 6:12 AM, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: Hi Rafal, Il 18/09/12 13:00, Rafal Pietrak ha scritto: I did have a look, and feel slightly encouraged reading: Many people feel that they're not qualified; yes, that's me all right. Serously, I

[GENERAL] foreign key from array element

Hello the list, recently I'm cooking a database for an application, that I feel is best implemented with tables containing arrays. I have found some related info here: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01356.php although the initial advice in that thread was: not to use

Re: [GENERAL] foreign key from array element

On 09/17/2012 04:46 PM, Rafal Pietrak wrote: Hello the list, recently I'm cooking a database for an application, that I feel is best implemented with tables containing arrays. I have found some related info here: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01356.php although the

Re: [GENERAL] foreign key from array element

On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote: On 09/17/2012 04:46 PM, Rafal Pietrak wrote: [--] There was some quite recent discussion on ELEMENT foreign keys on the -hackers list. Try searching pgsql-hackers for ELEMENT foreign key. See:

Re: [GENERAL] foreign key from array element

On Mon, Sep 17, 2012 at 7:24 AM, Rafal Pietrak ra...@zorro.isa-geek.com wrote: On Mon, 2012-09-17 at 19:58 +0800, Craig Ringer wrote: On 09/17/2012 04:46 PM, Rafal Pietrak wrote: [--] There was some quite recent discussion on ELEMENT foreign keys on the -hackers list. Try

[GENERAL] Foreign Key with an OR condition (and two concatenated columns)?

Hi. Hope I'm articulating the question correctly. I currently have a foreign key like this: fk_table2 FOREIGN KEY (id) REFERENCES table1(id) ON DELETE CASCADE; Given the peculiar needs of this db, it seems that in some cases we will need to track the id of table2 against a concatenation of

Re: [GENERAL] Foreign Key with an OR condition (and two concatenated columns)?

On Jan 8, 2012, at 9:33, Phoenix Kiula phoenix.ki...@gmail.com wrote: Hi. Hope I'm articulating the question correctly. I currently have a foreign key like this: fk_table2 FOREIGN KEY (id) REFERENCES table1(id) ON DELETE CASCADE; Given the peculiar needs of this db, it seems that in

[GENERAL] Foreign key check only if not null?

Hi, I bet this is a simple solution but I have been racking my brains. I have a column in my table: user_id varchar(100) ; This can be NULL, or it can have a value. If it has a value during INSERT or UPDATE, I want to check that the user exists against my Users table. Otherwise, NULL is

Re: [GENERAL] Foreign key check only if not null?

hi, fks do just that. you can create your fk with just one command: alter table xxx add constraint fk_name foreign key (user_id) references users (id); parent table's id field should also be of the same type and also it should be primary key or at least unique. you can create your pk with (if

Re: [GENERAL] Foreign key check only if not null?

On Mon, Sep 12, 2011 at 6:48 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: I have a column in my table:    user_id  varchar(100) ; This can be NULL, or it can have a value. If it has a value during INSERT or UPDATE, I want to check that the user exists against my Users table. Otherwise,

[GENERAL] Foreign key in composite values

Hello, I have a composite type with 2 fields. I would like to check that one of these fields exists in another table. Foreign keys on a composite field does not seem to be supported. Is there another way to do that ? Thanks for your reply Vincent De Groote

Re: [GENERAL] Foreign key in composite values

Vincent De Groote vdg.encel...@gmail.com writes: I have a composite type with 2 fields. I would like to check that one of these fields exists in another table. Foreign keys on a composite field does not seem to be supported. Works for me, in 8.4 and up. Whether it's a good idea is a

Re: [GENERAL] Foreign key in composite values

In your example can you Make cx.r a foreign key on another table (say real) so that a valid instance of cx must have a value for r that exists in real? I guess you could make r an enum but that wouldn't readily allow you to modify the allowable values for r. A domain and/or check constraint

Re: [GENERAL] Foreign key and locking problem

On Mon, Apr 4, 2011 at 4:18 PM, Edoardo Serra edoa...@serra.to.it wrote: At this point, client1 gives the following error: ERROR: could not serialize access due to concurrent update CONTEXT: SQL statement SELECT 1 FROM ONLY public.people x WHERE id OPERATOR(pg_catalog.=) $1 FOR SHARE OF x

[GENERAL] Foreign key and locking problem

Hi guys, I have a problem with the implicit FOR SHARE lock which postgres seems to get on the referenced record when there is a foreign key. I'm using postgres 8.3.3 from debian packages. Here is a sample database structure and commands to reproduce. -- Test database structure CREATE TABLE

Re: [GENERAL] Foreign key and locking problem

On 04/05/2011 04:18 AM, Edoardo Serra wrote: Hi guys, I have a problem with the implicit FOR SHARE lock which postgres seems to get on the referenced record when there is a foreign key. I'm using postgres 8.3.3 from debian packages. [snip] At this point, client1 gives the following error:

[GENERAL] Foreign Key

Hi I don't understand what the following error means. I am trying to add a foreign key to table image, that holds column device_id. This is to be controlled by column devices_id in table devices. ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses

Re: [GENERAL] Foreign Key

2010/4/23 Bob Pawley rjpaw...@shaw.ca Hi I don't understand what the following error means. I am trying to add a foreign key to table image, that holds column device_id. This is to be controlled by column devices_id in table devices. ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN

Re: [GENERAL] Foreign key behavior different in a function and outside

On 05/03/10 18:12, Mridula Mahadevan wrote: Richard, To answer your questions, I have a live application that is running on postgresql. We are seeing this issue on certain installations and not on others. So the code is no different in each set up. I also added the trigger to table B and then

Re: [GENERAL] Foreign key behavior different in a function and outside

On 05/03/10 06:45, Mridula Mahadevan wrote: Thanks for the response Tom. I am running postgres 8.3.7. Yes, his is a highly simplified version, but I also didn't get the column name right. The core issue is the foreign key reference being deleted even though there is no cascade delete

Re: [GENERAL] Foreign key behavior different in a function and outside

- From: Richard Huxton [mailto:d...@archonet.com] Sent: Friday, March 05, 2010 1:27 AM To: Mridula Mahadevan Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] Foreign key behavior different in a function and outside On 05/03/10 06:45, Mridula Mahadevan wrote: Thanks for the response

Re: [GENERAL] Foreign key behavior different in a function and outside

Mridula Mahadevan mmahade...@stratify.com writes: CREATE OR REPLACE FUNCTION delete_B(id integer) RETURNS void AS $BODY$ declare vSql varchar; BEGIN delete from B where id = id; END; That's

Re: [GENERAL] Foreign key behavior different in a function and outside

[mailto:t...@sss.pgh.pa.us] Sent: Thursday, March 04, 2010 7:45 PM To: Mridula Mahadevan Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Foreign key behavior different in a function and outside Mridula Mahadevan mmahade...@stratify.com writes: CREATE OR REPLACE FUNCTION delete_B(id integer

[GENERAL] FOREIGN KEY composite_type.its_field REFERENCES ....

Hi everyone! Is there a way to construct FOREIGN KEYs from parts of composite-typed field? The code returns an error: - create table aaa ( a_id integer primary key, a_str varchar) ; create type content_of_bbb (a_id integer, b_str varchar); create table

Re: [GENERAL] FOREIGN KEY composite_type.its_field REFERENCES ....

Belka Lambda lambda-be...@yandex.ru writes: Is there a way to construct FOREIGN KEYs from parts of composite-typed field? No, and even if the system let you do it, the performance would probably suck. Composite-type fields are not something to be used with abandon. To me your example looks

[GENERAL] Foreign Key Deferrable Misunderstanding or Bug?

Why does the attached script fail with a foreign key constraint violation? Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this

Re: [GENERAL] Foreign Key Deferrable Misunderstanding or Bug?

Paul Rogers prog...@sparkbase.com writes: Why does the attached script fail with a foreign key constraint violation? The ON DELETE RESTRICT is why. Per the fine manual: [RESTRICT] is the same as NO ACTION except that the check is not deferrable. It's a bit odd, but that's the

Re: [GENERAL] Foreign Key Deferrable Misunderstanding or Bug?

On Thu, 6 Aug 2009, Paul Rogers wrote: Why does the attached script fail with a foreign key constraint violation? Referential actions are not deferred when a constraint is marked deferrable (as that appears to be what the spec wants), so ON DELETE RESTRICT will still fail on the statement,

[GENERAL] Foreign Key Unique Constraint can be dropped

Hi, from postgres docs: foreign key must reference columns that either are a primary key or form a unique constraint. It does not allow creating foreign key constraint on non-unique column - an error is raised. After playing with a new database I discovered that such a unique constraint can be

Re: [GENERAL] Foreign Key Unique Constraint can be dropped

On Mon, Jun 8, 2009 at 11:48 AM, Vlado Moravecvm.d...@gmail.com wrote: Hi, from postgres docs: foreign key must reference columns that either are a primary key or form a unique constraint. It does not allow creating foreign key constraint on non-unique column - an error is raised. After

Re: [GENERAL] Foreign Key Unique Constraint can be dropped

Vlado Moravec vm.d...@gmail.com writes: from postgres docs: foreign key must reference columns that either are a primary key or form a unique constraint. It does not allow creating foreign key constraint on non-unique column - an error is raised. After playing with a new database I discovered

Re: [GENERAL] Foreign Key question

On Jun 3, 1:04 am, wmo...@potentialtech.com (Bill Moran) wrote: In response to Dave Clarke pigwi...@gmail.com: I have a table that I'm trying to refactor and I'm by no means a SQL expert (apologies if I'm posting to the wrong group). The table in question has a column that allows

Re: [GENERAL] Foreign Key question

Hi Dave, that makes sense. You should read the documentation about FK. They can be 1:n, 1:1, n:1. Normally i would make a unique field in each table to avoid complex PK/FK. Eg a serial column. Dave Clarke schrieb: Hello I have a table that I'm trying to refactor and I'm by no means a SQL

Re: [GENERAL] Foreign Key question

In response to Dave Clarke pigwi...@gmail.com: I have a table that I'm trying to refactor and I'm by no means a SQL expert (apologies if I'm posting to the wrong group). The table in question has a column that allows NULLs. I want to move that column into a separate table and set up a FK

[GENERAL] Foreign key verification trigger conditions

I have an update statement that affects every row in a given table. For that table it changes the value in a single column, which itself has a foreign key constraint. The table has an additional 9 foreign keys, some of which reference large tables. My expectation would be that only the changed

Re: [GENERAL] Foreign key verification trigger conditions

j-lists jamisonli...@gmail.com writes: I have an update statement that affects every row in a given table. For that table it changes the value in a single column, which itself has a foreign key constraint. The table has an additional 9 foreign keys, some of which reference large tables. My

Re: [GENERAL] Foreign key verification trigger conditions

Hi Tom, Thank you for pointing out the condition under which this occurs, I had not made the connection that the check was only occurring when the value in the other columns with foreign keys are null. I agree 100% that a strict key equality check that is in general use in the database should not

[GENERAL] Foreign Key question

Hello I have a table that I'm trying to refactor and I'm by no means a SQL expert (apologies if I'm posting to the wrong group). The table in question has a column that allows NULLs. I want to move that column into a separate table and set up a FK reference back to the original table. My question

Re: [GENERAL] Foreign Key 'walker'?

Erwin Moller wrote: No, that is not the kind of chicken I was talking about. ;-) My chicken is more along these lines: I often have some tables to which everything is related (eg tblcourse that contains everything belonging to a certain course). I don't want to make a single simple mistake

Re: [GENERAL] Foreign Key 'walker'?

Craig Ringer schreef: Erwin Moller wrote: No, that is not the kind of chicken I was talking about. ;-) My chicken is more along these lines: I often have some tables to which everything is related (eg tblcourse that contains everything belonging to a certain course). I don't want to make a

Re: [GENERAL] Foreign Key 'walker'?

Hi Pavel, Thanks for that. But I already wrote a nice extension to my DB-class in PHP that uses Thomas Kellerer's approach. It was simple once you know how to retrieve the info from the systemtables. :-) Regards, Erwin Moller Pavel Stehule schreef: Hello I used this code CREATE OR

Re: [GENERAL] Foreign Key 'walker'?

Erwin Moller schreef: Craig Ringer schreef: Erwin Moller wrote: No, that is not the kind of chicken I was talking about. ;-) My chicken is more along these lines: I often have some tables to which everything is related (eg tblcourse that contains everything belonging to a certain course). I

[GENERAL] Foreign Key 'walker'?

Hi group, Considering following (simplified) example: CREATE TABLE tblnr1( nr1id SERIAL PRIMARY KEY, firstname TEXT ); CREATE TABLE tblnr2( nr2id SERIAL PRIMARY KEY, nr1id INTEGER REFERENCES tblnr1(nr1id) ); CREATE TABLE tblnr3( nr3id SERIAL PRIMARY KEY, nr2id INTEGER REFERENCES

Re: [GENERAL] Foreign Key 'walker'?

On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote: Hi group, Considering following (simplified) example: CREATE TABLE tblnr1( nr1id SERIAL PRIMARY KEY, firstname TEXT ); CREATE TABLE tblnr2( nr2id SERIAL PRIMARY KEY, nr1id INTEGER REFERENCES tblnr1(nr1id) ); CREATE TABLE tblnr3( nr3id SERIAL

Re: [GENERAL] Foreign Key 'walker'?

Erwin Moller, 18.11.2008 15:47: Suppose I want to delete a record in tblnr1. Does Postgres has some command/procedure/function to list tables that have FK constraints on that table (tblnr1) That could be resolved with a query against the INFORMATION_SCHEMA Something like SELECT c.table_name

Re: [GENERAL] Foreign Key 'walker'?

ries van Twisk schreef: On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote: Hi group, Considering following (simplified) example: CREATE TABLE tblnr1( nr1id SERIAL PRIMARY KEY, firstname TEXT ); CREATE TABLE tblnr2( nr2id SERIAL PRIMARY KEY, nr1id INTEGER REFERENCES tblnr1(nr1id) ); CREATE

Re: [GENERAL] Foreign Key 'walker'?

Thomas Kellerer schreef: Erwin Moller, 18.11.2008 15:47: Suppose I want to delete a record in tblnr1. Does Postgres has some command/procedure/function to list tables that have FK constraints on that table (tblnr1) That could be resolved with a query against the INFORMATION_SCHEMA Something

Re: [GENERAL] Foreign Key 'walker'?

ries van Twisk wrote: On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote: Hi group, Considering following (simplified) example: snip Suppose I want to delete a record in tblnr1. Does Postgres has some command/procedure/function to list tables that have FK constraints on that table (tblnr1)

Re: [GENERAL] Foreign Key 'walker'?

Shane Ambler schreef: ries van Twisk wrote: On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote: Hi group, Considering following (simplified) example: snip Suppose I want to delete a record in tblnr1. Does Postgres has some command/procedure/function to list tables that have FK constraints

Re: [GENERAL] Foreign Key 'walker'?

Hello I used this code CREATE OR REPLACE FUNCTION list_user_tables_sort_depend (owner VARCHAR, revers BOOLEAN) RETURNS SETOF VARCHAR AS ' DECLARE tabulky VARCHAR[]; i INTEGER; opakovat BOOLEAN = ''t''; pom VARCHAR; exportovano VARCHAR[] = ''{}''; r RECORD; mohu_exportovat BOOLEAN; BEGIN

Re: [GENERAL] foreign key problem

Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Jorge Godoy [EMAIL PROTECTED] 09/17/08 1:36 AM Em Monday 15 September 2008 19:05:25 [EMAIL PROTECTED] escreveu: Hi, I need a foreign key (or equivalent) where the referenced table cannot have a unique constraint. For fisheries

[GENERAL] Foreign Key normalization question

I'm building an app that has a customers table, a locations table, a products table, and a product_locations table. They make a diamond shape. The locations table and the products table each have a customer_id column that links back to the customers table. Then the product_locations table table

Re: [GENERAL] Foreign Key normalization question

within this transmission. To: pgsql-general@postgresql.org From: [EMAIL PROTECTED] Subject: [GENERAL] Foreign Key normalization question Date: Tue, 2 Sep 2008 19:14:17 + I'm building an app that has a customers table, a locations table, a products table, and a product_locations table

Re: [GENERAL] Foreign Key normalization question

On Tue, Sep 2, 2008 at 1:14 PM, Matthew Wilson [EMAIL PROTECTED] wrote: I'm building an app that has a customers table, a locations table, a products table, and a product_locations table. They make a diamond shape. The locations table and the products table each have a customer_id column

Re: [GENERAL] Foreign Key normalization question

On Tue 02 Sep 2008 04:06:20 PM EDT, Martin Gainty wrote: you can use setup a foreign key constraint in your create table so that col= umn is only populated when there is a value which syncs to the referenced value http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html I don't

Re: [GENERAL] Foreign Key normalization question

On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote: If the two subordinate tables ALWAYS have to point to the same place, why two tables? Can't a customer have 1 location? I'm pretty sure IBM has more than one corporate office you could ship things to. Yeah, so the idea is one customer

Re: [GENERAL] Foreign Key normalization question

On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson [EMAIL PROTECTED] wrote: On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote: If the two subordinate tables ALWAYS have to point to the same place, why two tables? Can't a customer have 1 location? I'm pretty sure IBM has more than one

Re: [GENERAL] Foreign Key normalization question

On Tue 02 Sep 2008 04:40:55 PM EDT, Scott Marlowe wrote: On Tue, Sep 2, 2008 at 2:35 PM, Matthew Wilson [EMAIL PROTECTED] wrote: On Tue 02 Sep 2008 04:19:41 PM EDT, Scott Marlowe wrote: If the two subordinate tables ALWAYS have to point to the same place, why two tables? Can't a customer have

  1   2   3   >