Thread: DDL problems: Referential issue?
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, PRIMARYKEY (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: pgslekt=> alter table participants add constraint unique_person_event unique (person_fk, event_fk); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "unique_person_event" for table "participants" ALTER TABLE But I still get the same error message as above. This works fine, naturally: pgslekt=> CREATE TABLE participant_notes ( pgslekt(> person_fk INTEGER NOT NULL references persons (person_id), pgslekt(> event_fk INTEGER NOT NULL references events (event_id) 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" CREATE TABLE However, I think that this table should reference participants, not the primary tables persons and events. -- Leif Biberg Kristensen http://solumslekt.org
On Wed, Nov 4, 2009 at 11:03 AM, Leif Biberg Kristensen <leif@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.
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. -- Leif Biberg Kristensen http://solumslekt.org
On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen <leif@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.
On Wednesday 4. November 2009 19.37.41 Scott Marlowe wrote: > On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen > <leif@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 And is probably what I want. -- Leif Biberg Kristensen http://solumslekt.org
On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen <leif@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 >> <leif@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.
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_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. 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
On Wed, Nov 4, 2009 at 1:14 PM, Leif Biberg Kristensen <leif@solumslekt.org> wrote: > 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_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. > > 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. Exactly. Note that null values i pgsql take up VERY little space, so performance-wise one table is likely faster, but it's not gonna break the bank to have two.