Using a Foreign Members Table with Oracle or PostgreSQL

Lyris ListManager requires that the column that stores the member ID be unique and auto-numbering. For Oracle this is implemented as a trigger against a sequence table; for Postgres, it is a default rule on the column that accesses the sequence table.

If you use a foreign member table, you may need to add the sequence to your member ID column yourself and make sure the next value is automatically populated. You need only add the sequence yourself if the existing ID field has no other means of getting a default unique value during an insert.
 

Creating a Sequence Table for Oracle

This example presumes you want to use a foreign to use a foreign members table with the name LOCAL_MEMBERS, using the column ID as the Member ID column.

1. Find out the maximum number already in the id column:
 

select max(ID) from LOCAL_MEMBERS;

2. Create a sequence for that table, starting with the maximum number already in the id column, and adding one. For example, if the maximum number is 50, create the following sequence:

create sequence LOCAL_MEMBERS_seq start 51;

3. Create a "before trigger" to allow ListManager to transparently create new rows. It will then fill in the member ID, which ListManager doesn't specify when inserting rows.

create trigger localmemtrig before insert on LOCAL_MEMBERS
for each row
begin
select LOCAL_MEMBERS_seq.nextval into LOCAL_MEMBERS.ID from dual;
end;

Other steps needed to use a foreign members table may be found in Using a Foreign Members Table.

Creating a Sequence Table for Postgres

This example presumes you want to use a foreign to use a foreign members table with the name LOCAL_MEMBERS, using the column ID as the Member ID column.

1. Find out the maximum number already in the id column:
 

select max(ID) from LOCAL_MEMBERS;

2. Create a sequence for that table, starting with the maximum number already in the id column, and adding one. For example, if the maximum number is 50, create the following sequence:

create sequence LOCAL_MEMBERS_seq start 51;

3. Create a default rule to allow ListManager to transparently create new rows. It will then fill in the member ID, which ListManager doesn't specify when inserting rows.

alter table LOCAL_MEMBERS
alter column ID
set default nextval('LOCAL_MEMBER_seq');

Other steps needed to use a foreign members table may be found in Using a Foreign Members Table.