Re: DDL problems: Referential issue?

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.