Re: [SQL] DDL problems: Referential issue?
On Wed, Nov 4, 2009 at 11:03 AM, Leif Biberg Kristensen l...@solumslekt.org wrote: PostgreSQL 8.3.8 on Gentoo Linux. I've got a junction table: CREATE TABLE participants ( person_fk INTEGER REFERENCES persons (person_id), event_fk INTEGER REFERENCES events (event_id) ON DELETE CASCADE, sort_order INTEGER NOT NULL DEFAULT 1, is_principal BOOLEAN NOT NULL DEFAULT TRUE, PRIMARY KEY (person_fk, event_fk) ); CREATE INDEX event_key ON participants (event_fk); CREATE INDEX person_key ON participants (person_fk); Now I want to add some text to a few participants, but as this will probably only be for a few per cent, I try to create an extra table like this: pgslekt= CREATE TABLE participant_notes ( pgslekt( person_fk INTEGER NOT NULL REFERENCES participants (person_fk), pgslekt( event_fk INTEGER NOT NULL REFERENCES participants (event_fk) ON DELETE CASCADE, pgslekt( part_note TEXT, pgslekt( PRIMARY KEY (person_fk, event_fk) pgslekt( ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index participant_notes_pkey for table participant_notes ERROR: there is no unique constraint matching given keys for referenced table participants I fail to see what is the problem. I even tried to add a unique constraint to participants: You're referencing a single column, which does not have a unique key on it. Being part of a two column unique PK index doesn't count, as you could have an entry where one column or the other repeats on its own while the other column changes. You might want the syntax: FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) where you FK a pair of columns to a pair of other columns. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wednesday 4. November 2009 19.24.29 Scott Marlowe wrote: You're referencing a single column, which does not have a unique key on it. Being part of a two column unique PK index doesn't count, as you could have an entry where one column or the other repeats on its own while the other column changes. You might want the syntax: FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) where you FK a pair of columns to a pair of other columns. Thank you very much! pgslekt= CREATE TABLE participant_notes ( pgslekt( person_fk INTEGER, pgslekt( event_fkINTEGER, pgslekt( part_note TEXT, pgslekt( FOREIGN KEY (person_fk, event_fk) REFERENCES participants (person_fk, event_fk) pgslekt( ); CREATE TABLE I'd missed that particular syntax. This table is now without a primary key, but is that a problem? I don't expect it to grow beyond maybe a few thousand rows. -- Leif Biberg Kristensen http://solumslekt.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen l...@solumslekt.org wrote: On Wednesday 4. November 2009 19.24.29 Scott Marlowe wrote: You're referencing a single column, which does not have a unique key on it. Being part of a two column unique PK index doesn't count, as you could have an entry where one column or the other repeats on its own while the other column changes. You might want the syntax: FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) where you FK a pair of columns to a pair of other columns. Thank you very much! pgslekt= CREATE TABLE participant_notes ( pgslekt( person_fk INTEGER, pgslekt( event_fk INTEGER, pgslekt( part_note TEXT, pgslekt( FOREIGN KEY (person_fk, event_fk) REFERENCES participants (person_fk, event_fk) pgslekt( ); CREATE TABLE I'd missed that particular syntax. This table is now without a primary key, but is that a problem? I don't expect it to grow beyond maybe a few thousand rows. Hard to say, but if you really need a PK, you can always create one later. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wednesday 4. November 2009 19.37.41 Scott Marlowe wrote: On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen l...@solumslekt.org wrote: I'd missed that particular syntax. This table is now without a primary key, but is that a problem? I don't expect it to grow beyond maybe a few thousand rows. Hard to say, but if you really need a PK, you can always create one later. This looks strange to me, but it works: pgslekt= CREATE TABLE participant_notes ( pgslekt( person_fk INTEGER NOT NULL, pgslekt( event_fkINTEGER NOT NULL, pgslekt( part_note TEXT, pgslekt( PRIMARY KEY (person_fk, event_fk), pgslekt( FOREIGN KEY (person_fk, event_fk) REFERENCES participants (person_fk, event_fk) pgslekt( ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index participant_notes_pkey for table participant_notes CREATE TABLE And is probably what I want. -- Leif Biberg Kristensen http://solumslekt.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen l...@solumslekt.org wrote: On Wednesday 4. November 2009 19.37.41 Scott Marlowe wrote: On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen l...@solumslekt.org wrote: I'd missed that particular syntax. This table is now without a primary key, but is that a problem? I don't expect it to grow beyond maybe a few thousand rows. Hard to say, but if you really need a PK, you can always create one later. This looks strange to me, but it works: pgslekt= CREATE TABLE participant_notes ( pgslekt( person_fk INTEGER NOT NULL, pgslekt( event_fk INTEGER NOT NULL, pgslekt( part_note TEXT, pgslekt( PRIMARY KEY (person_fk, event_fk), pgslekt( FOREIGN KEY (person_fk, event_fk) REFERENCES participants (person_fk, event_fk) pgslekt( ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index participant_notes_pkey for table participant_notes CREATE TABLE Note that this will limit you to one record in your participant notes for each record in the participants table. If you need 1 of those, then you could either create a serial and use that for a PK, or PK on person_fk, event_fk and part_not, assuming part_note doesn't get real big. If it does you can PK on something like event, person, and md5(part_note) or something along those lines. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] DDL problems: Referential issue?
On Wednesday 4. November 2009 21.03.26 Scott Marlowe wrote: On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen This looks strange to me, but it works: pgslekt= CREATE TABLE participant_notes ( pgslekt( person_fk INTEGER NOT NULL, pgslekt( event_fkINTEGER NOT NULL, pgslekt( part_note TEXT, pgslekt( PRIMARY KEY (person_fk, event_fk), pgslekt( FOREIGN KEY (person_fk, event_fk) REFERENCES participants (person_fk, event_fk) pgslekt( ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index participant_notes_pkey for table participant_notes CREATE TABLE Note that this will limit you to one record in your participant notes for each record in the participants table. That's exactly what I want :) For all practical purposes, the design is equivalent to adding a TEXT column to the participants table. But as I expect a very small number of notes compared to the number of rows in the participants table, I prefer to create a small extra table rather than having a large number of null values in the participants table. Performance-wise, it probably doesn't matter much. It's more a matter of taste. -- Leif Biberg Kristensen http://solumslekt.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
