On Monday, February 13, 2012 04:50:47 PM Andreas Kretschmer wrote:
> John Fabiani wrote:
> > Hi,
> > I have read a few articles and I'm not sure if it's me or the authors
> > but I do not believe my question was answered.
> >
> > If I have table that has a PK and a FK - will the planner use the F
John Fabiani wrote:
> Hi,
> I have read a few articles and I'm not sure if it's me or the authors but I
> do
> not believe my question was answered.
>
> If I have table that has a PK and a FK - will the planner use the FK just
> same
> as it would use the PK? IOW's is a FK also an index use
On 2011-01-05, Gary Stainburn wrote:
> On Wednesday 05 January 2011 09:53:43 Gary Stainburn wrote:
>> Now I want to set up a new access level table specific to the itinerary,
>> along the lines of
>>
>> u_id int4 not null references users(u_id)
>> fl_level int4 not null references facility_levels(
On Wednesday 05 January 2011 09:53:43 Gary Stainburn wrote:
> Now I want to set up a new access level table specific to the itinerary,
> along the lines of
>
> u_id int4 not null references users(u_id)
> fl_level int4 not null references facility_levels(16, fl_level)
>
> Firstly, is this possible,
On 30/12/2009 6:59 AM, Andrew Hall wrote:
Hi,
I'm porting some Oracle software to PostgreSQL, & have a question about
best practices with foreign keys.
In the Oracle environment, you're basically obliged to index all foreign
keys (more specifically foreign key columns should appear on the leadi
On Thu, Nov 5, 2009 at 2:08 PM, Svenne Krap wrote:
> Hi.
>
> Is there a simple way to get foreign key data... for example I found a
> view, that does what I want ...
>
> It delivers
>
> fk_table | fk_column | pk_table | pk_column |
> constraint_name
>
> ---
No problems with the design - I was not thinking with the DB hat on at
first. I have been working on clustering for a while... just adjusting.
Thanks everyone.
:)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
am Tue, dem 11.12.2007, um 14:12:38 -0500 mailte PostgreSQL Admin folgendes:
> This is my layout so far:
> [ table-layout]
> ...
> Does this look correct?
Yes, why not? Do you have problems? Which? I can't see problems, it's a
normalized design IMHO.
Andreas
--
Andreas Kretschmer
Kontakt: Hey
PostgreSQL Admin wrote:
> This is my layout so far:
>
> CREATE TABLE users (
> id serial NOT NULL,
> --question REFERENCES questions(id) ON DELETE CASCADE ## ON REMOVED##
> );
>
> CREATE TABLE questions (
> id serial NOT NULL,
> questions varchar(450) NOT NULL
> );
>
> CREATE TABLE answers (
> i
This is my layout so far:
CREATE TABLE users (
id serial NOT NULL,
--question REFERENCES questions(id) ON DELETE CASCADE ## ON REMOVED##
);
CREATE TABLE questions (
id serial NOT NULL,
questions varchar(450) NOT NULL
);
CREATE TABLE answers (
id serial NOT NULL,
question_id int REFERENCES questi
am Tue, dem 11.12.2007, um 13:20:52 -0500 mailte PostgreSQL Admin folgendes:
> I have a table in which people will have a number of questions to
> answer. I want those pk to be placed in my user table. So if a user
> answers three question I want those 3 pk's in the user table (fk).
> What s
On Dec 11, 2007, at 12:20 PM, PostgreSQL Admin wrote:
I have a table in which people will have a number of questions to
answer. I want those pk to be placed in my user table. So if a user
answers three question I want those 3 pk's in the user table (fk).
What should I be doing?
You're goi
- Original Message -
From: "ivan marchesini" <[EMAIL PROTECTED]>
To:
Sent: Monday, September 24, 2007 6:04 PM
Subject: [SQL] foreign key problem
Dear users,
I'm trying to create constraints into a db they give me from access...
I'm using pgadmin to create a new foreign key for the r
> On 8/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>> Hi all. Can i make a FK who points a table in a different schema? Or
>> this
>> is implemented via a trigger by my own?
>
> Sure. just prefix the table name with the schemaname and a .
>
> create schema abc;
> alter user me set search_p
On 8/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Hi all. Can i make a FK who points a table in a different schema? Or this
> is implemented via a trigger by my own?
Sure. just prefix the table name with the schemaname and a .
create schema abc;
alter user me set search_path='abc', 'pub
On Aug 3, 2007, at 2:24 AM, Dani Castaños wrote:
Josh Tolley escribió:
On 8/2/07, Dani Castaños <[EMAIL PROTECTED]> wrote:
Hi all!
Is it possible to have a foreign key where referenced table is in
another database?
Thank you in advance
---(end of
broadcast)--
Josh Tolley escribió:
On 8/2/07, Dani Castaños <[EMAIL PROTECTED]> wrote:
Hi all!
Is it possible to have a foreign key where referenced table is in
another database?
Thank you in advance
---(end of broadcast)---
TIP 1: if posting/reading thro
On 8/2/07, Dani Castaños <[EMAIL PROTECTED]> wrote:
> Hi all!
>
> Is it possible to have a foreign key where referenced table is in
> another database?
>
> Thank you in advance
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, p
Michael Fuhr <[EMAIL PROTECTED]> writes:
> My intent wasn't to assert that IF EXISTS adds a new way for the
> DELETE to fail. I was just pointing out that the test "if no
> referencing rows exist then delete the referenced row" isn't
> foolproof, viz., the DELETE can fail even though IF EXISTS sai
On Sat, Oct 14, 2006 at 08:20:10PM -0400, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
> > Unless the inserters got there first. I just tested both ways; if
> > the insert acquires the lock first then the delete fails, but if the
> > delete acquires the lock first then the insert fai
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote:
>> No, I don't think so, because the DELETE will already be holding
>> exclusive lock on the doomed PK row, which any would-be inserters of
>> matching FK rows will be blocked on. AFAICS the DELETE
On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
> > On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote:
> >> Create an "after delete" trigger on the referencing table that checks
> >> whether there still are records with the same key
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote:
>> Create an "after delete" trigger on the referencing table that checks
>> whether there still are records with the same key (IF EXISTS()), and
>> deletes the referenced row otherwise.
> In
On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote:
> Joost Kraaijeveld wrote:
> > Is there a strategy to implement reference counting for foreign keys so
> > that if the last reference to the key is deleted, the record is deleted
> > also?
>
> Create an "after delete" trigger on the r
Hi, Joost,
Joost Kraaijeveld wrote:
> Is there a strategy to implement reference counting for foreign keys so
> that if the last reference to the key is deleted, the record is deleted
> also?
Create an "after delete" trigger on the referencing table that checks
whether there still are records wi
On 7/6/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
i alsways make my foreign key column data type int4.i'm not sure if i read that somewhere. anyone, please
feel free to chime in if this isn't good practice.read up on currval, nextval and that whole section.you can begin by getting the nextva
> Hi,
> How to know the value which I must set in the
> foreign key field?. I have two
> tables:
>
>
> CREATE TABLE AA (
> Id SERIAL PRIMARY KEY,
> data char(9)
> );
>
> CREATE TABLE BB (
> BB_Id integer REFERENCES AA(Id) NOT NULL,
> field char(5)
> );
>
>
>
>
> I i
A. Kretschmer wrote:
> Davi Leal folgendes:
> > Hi,
> > How to know the value which I must set in the foreign key field?. I have
> > two tables:
> >
> >
> > CREATE TABLE AA (
> > Id SERIAL PRIMARY KEY,
> > data char(9)
> > );
> >
> > CREATE TABLE BB (
> > BB_Id integer REFERENCES
am 05.07.2006, um 17:19:26 +0200 mailte Davi Leal folgendes:
> Hi,
> How to know the value which I must set in the foreign key field?. I have two
> tables:
>
>
> CREATE TABLE AA (
> Id SERIAL PRIMARY KEY,
> data char(9)
> );
>
> CREATE TABLE BB (
> BB_Id integer REFERENCES A
However, if customers or suppliers can have multiple accounts, you are
going to need an intermediate table, as suggested by Neil.
Scratch that. If accounts can have multiple owners you'll need an
intermediate table.
---(end of broadcast)---
On Tue, 2005-11-22 at 16:24 +0100, Joost Kraaijeveld wrote:
> Hi,
>
> Is there a way to create a foreign key to 2 tables: e.g. a bankaccount
> table that has a column "owner", that must point to a record in either
> the customer or the supplier table?
No. What you need is an owner table that cust
I've never seen anything like that. I'm sure it's conceivable that you
could write a weird trigger for it, but you have to consider
maintainability, and what your queries are going to look like. I
haven't seen your datamodel, but it would seem that you could accomplish
what you're looking for
O Joost Kraaijeveld έγραψε στις Nov 22, 2005 :
> Hi,
>
> Is there a way to create a foreign key to 2 tables: e.g. a bankaccount
> table that has a column "owner", that must point to a record in either
> the customer or the supplier table?
While there are techniques to accomplish this,
i see a p
As far as I'm aware, not without using an intermediatary table (id,
cust_id, supplier_id) . Otherwise, how would you know which table the
foreign key was referencing?
That said, an intermediatary table isn't a very clean solution; What
problem are you trying to solve, exactly?
Kind Regards,
Neil
On Wed, Jul 27, 2005 at 16:08:19 -0400,
Jeff Boes <[EMAIL PROTECTED]> wrote:
> Given a table like this:
>
> create table primary (
> a integer primary key,
> b boolean
> );
>
> And another like this:
>
> create table secondary (
> a integer,
> some_other_fields
> );
>
>
> I would lik
"Vsevolod (Simon) Ilyushchenko" <[EMAIL PROTECTED]> writes:
> However, when I pg_dump the database and import it on another server,
> the tables are exported alphabetically, so when the 'people_roles' table
> is created with its foreign keys, the table 'roles' does not exist yet.
> Thus, the for
"M.D.G. Lange" <[EMAIL PROTECTED]> writes:
> No matter what ON DELETE constraint I created, the system will not allow
> me to create a foreign key, as pg_shadow is a system catalog.
We do not support foreign keys (or indeed triggers of any kind) on
system catalogs. I don't foresee that happening
On Wed, Mar 30, 2005 at 02:09:05PM +0200, [EMAIL PROTECTED] wrote:
>
> Thanks for your answers but i make the modifications, the same error returned.
> Here my script :
> ...
> Create table Salariés (Nom_salarié VARCHAR(20),
>Prénom VARCHAR(20),
> Fonction VARC
On Fri, Mar 25, 2005 at 16:31:16 +0100,
[EMAIL PROTECTED] wrote:
>
> When i add table with foreign key in my database, this error return : <
> number of referencing and referenced colums for foreign key disagree>.
>
> How resolve this problem ?
Besides what Mike said, one other thing to rememb
On Fri, Mar 25, 2005 at 04:31:16PM +0100, [EMAIL PROTECTED] wrote:
>
> When i add table with foreign key in my database, this error return : <
> number of referencing and referenced colums for foreign key disagree>.
Apparently the referencing key (the foreign key specification) has
a different num
On Thu, 27 Jan 2005, Sandeep Gaikwad wrote:
> I can give foreign key relationship between two tables of same
> database. Can I give foreign key relationship between tables of two
> databases ? Plz, let me know if possible & send me how can I do that?
Unfortunately, that's not really current
On Wed, 5 Jan 2005, Stephan Szabo wrote:
>
> On Wed, 5 Jan 2005, [iso-8859-2] BARTKO, Zoltán wrote:
>
> > Ok, so I made some changes (manual "inheritance" of PK and FK
> > constraints), but nevertheless I get still the same dumb error. I made
> > a dump of the DB via pg_dump, it is available at
>
On Wed, 5 Jan 2005, [iso-8859-2] BARTKO, Zoltán wrote:
> Ok, so I made some changes (manual "inheritance" of PK and FK
> constraints), but nevertheless I get still the same dumb error. I made
> a dump of the DB via pg_dump, it is available at
>
> http://de.geocities.com/bartkozo/dump.tgz
>
> DRec
Ok, so I made some changes (manual "inheritance" of PK and FK
constraints), but nevertheless I get still the same dumb error. I made
a dump of the DB via pg_dump, it is available at
http://de.geocities.com/bartkozo/dump.tgz
DRecord was dropped, the columns moved into DObject. I still have no
ide
On Tue, 4 Jan 2005, [iso-8859-2] BARTKO, Zoltán wrote:
> if I create the tables that are in the attached file, I can't insert
> rows into the AAttachment table, even though the rows in DObject with
> the given primary key exist (PgSQL 8.0 rc1 complains about
> (ownerid)=(insert the number here) n
On Wed, 27 Oct 2004, Jon Uhal wrote:
> I'm having trouble trying to get my databases setup so that when I
> delete a row from the base table, all related information is removed as
> well. I've been testing this with PostgreSQL version (postmaster
> (PostgreSQL) 8.0.0beta1) on a Windows 2000 Pro m
O Achilleus Mantzios έγραψε στις Sep 29, 2004 :
>
> Hmm, (something went wrong with some mailer)
And again
hope it gets right this time...
>
> Tom Lane wrote:
> > We're adding a trigger to it.
>
>From the docs:
Hmm, (something went wrong with some mailer)
Tom Lane wrote:
> We're adding a trigger to it.
>From the docs:
ACCESS EXCLUSIVE
Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW
EXCLUSIVE, SHARE UPDATE EX
O Tom Lane έγραψε στις Sep 28, 2004 :
> Achilleus Mantzios <[EMAIL PROTECTED]> writes:
> > Whats the purpose of the AccessExclusiveLock on parent table?
>
> We're adding a trigger to it.
>From the docs:
Acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL
commands. This is
O Tom Lane έγραψε στις Sep 28, 2004 :
> Achilleus Mantzios <[EMAIL PROTECTED]> writes:
> > Whats the purpose of the AccessExclusiveLock on parent table?
>
> We're adding a trigger to it.
>From the docs:
Acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL
commands. This i
Achilleus Mantzios <[EMAIL PROTECTED]> writes:
> Whats the purpose of the AccessExclusiveLock on parent table?
We're adding a trigger to it.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
On Fri, 11 Oct 2002, Jeffrey Green wrote:
> > Hello. I was wondering if anybody's run across the problem of
> > creating tables with foreign key constraints out of order. What I
> > mean by this is that say I want a table called that has a
> > foreign key reference to a table . If I define pic
7.2.3.
It's a mystery as to how this happened but there is a program we use to
copy rows from one version to another. For some reason the person who
wrote it disabled triggers before copying rows to a new version then
re-enabled the triggers. If someone made changes while the triggers were
off
On Tue, 8 Oct 2002, Mathieu Arnold wrote:
> I found in an old pgsql dump something like :
>
> UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'TABLE NAME';
>
> inserts
>
> BEGIN TRANSACTION;
> CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
> INSERT INTO "
On Mon, 7 Oct 2002, Laurette Cisneros wrote:
>
> I have two tables:
>
> create table table1
> ( vers integer,
> table1_id text NOT NULL,
> desc text,
> PRIMARY KEY (rev, table1)id)
> );
>
> create table tab
Laurette,
> How is this possible? I've tried to reproduce this, but haven't been able
> to yet. This has happened to use several times.
Oh, forgot question 2:
SELECT version()?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
Laurette,
> How is this possible? I've tried to reproduce this, but haven't been able
> to yet. This has happened to use several times.
Question 1: Can you experimentally create a record in table1, a matching
record in table 2, and then delete the record in table1? It's possible that
your
Lorenzo De Vito wrote:
> What's the best way to build a relation between two tables ?
> I know that Foreign key is no longer supported.
Who told you so?
Jan
--
#==#
# It's easier to get forgiveness for being wrong than fo
On 25 Jun 2001, Itai Zukerman wrote:
> According to the documentation for CREATE TABLE:
>
> In addition, the referenced columns are supposed to be the columns
> of a UNIQUE constraint in the referenced table, however Postgres
> does not enforce this.
>
> Well, it looks like PostgreSQL *do
Je Mon, 25 Jun 2001 09:34:01 +0200 (CEST),
Andreas Tille <[EMAIL PROTECTED]> scribis:
> CREATE TABLE ResKulturDetail
> (
> IdLabNr int,
> IdIndex smallint
> );
>
> CREATE TABLE ResKulturDetailDay
> (
> IdLabNr int,
> IdIndex smallint
> );
>
> CREATE INDEX IX_IdLabNr_KulturDe
Yes, you cannot currently do this. Check past discussions on mailing
lists for more details. Inheritance and constraints is pretty dodgy
right now (for instance, Reservationfile.datafileNr is not unique).
On Tue, 5 Jun 2001, Mikael Kjellström wrote:
> I've declared a foreign key in table File
AFAIK, not easily. There was a post to the hackers list recently with
code for a "database connector" function. Using this it may be possible
to write your own referential integrity triggers that reference the other
database.. maybe not. I didn't look much into the code. There might be
someth
>> CREATE TABLE resource_record(
>> rrid SERIAL
>> -- etc.
>> );
> There is no primary key for this table. Just write PRIMARY KEY after
> SERIAL.
There is. I accidentally left it out in the post.
> CREATE INDEX soa_record_pkey ON soa_record ( rrid );
> You could also state the referenced f
On Thu, 22 Mar 2001, [iso-8859-1] Johannes Grødem wrote:
> Hi,
>
> it seems I can't have a foreign key that references some subclass. Postgres
> says it can't figure out what its primary key is. The primary key is defined
> in the superclass.
Unique/primary key doesn't inherit to subclasses.
Hi Johannes,
On Thursday, 22. March 2001 15:18, Johannes Grødem wrote:
> Hi,
>
> it seems I can't have a foreign key that references some subclass.
> Postgres says it can't figure out what its primary key is. The
> primary key is defined in the superclass.
>
> I have something like this:
>
> CR
Recovery try use:
pg_dump -t tbname -f outfile dbname
use vi erase f key from outfile,
drop old table, reload them.
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
Script to reproduce the problem. It drops the tables at the end of the
script.
Stephan Szabo wrote:
>
> There was a bug (which should be fixed for 7.1) that got the
> arguments wrong for the alter time check of the existing data.
> I think I should be able to get a patch together to fix it once
There was a bug (which should be fixed for 7.1) that got the
arguments wrong for the alter time check of the existing data.
I think I should be able to get a patch together to fix it once
I get a copy of the 7.0.3 source.
Can you send the table schema as well so I can test it out?
Stephan Szab
Try creating the tables without the constraint first,
then populate them, and then add the foreign key constaint
as the last step using ALTER TABLE/ADD CONSTRAINT.
Mark
Jie Liang wrote:
>
> Hi, there,
>
> I want add a constraint to my tables:
> I have 2 tables:
> 1. Table_A(id int 4 primary k
69 matches
Mail list logo
