On Fri, Nov 5, 2010 at 5:27 PM, Nick <nboutelier@gmail.com> wrote:
> Are there any existing trigger functions (preferably C) that could
> retrieve a missing value for a compound foreign key on insert or
> update? If this overall sounds like a really bad idea, please let me
> know as well. This functionality could really speed my project up
> though.
I think your issues are really SQL issues. See my comments below:
> For example,
>
> CREATE TABLE email_addresses (id BIGSERIAL, email_address VARCHAR);
> ALTER TABLE email_addresses ADD CONSTRAINT _email_addresses_pkey
> PRIMARY KEY (id,email_address);
email addresses should be unique, so this is pretty silly. You are
not getting fast lookups on email which is what you need. You have
two choices here: keep the surrogate on email_addresses, in which case
I'd do the tables like this:
CREATE TABLE email_addresses
(
email_address_id BIGSERIAL primary key,
email_address VARCHAR unique
);
create table users
(
user_id BIGSERIAL primary key,
email_address_id BIGINT references email_addresses on delete cascade/set null,
)
your insert will look like this (pseudo code):
select email_address_id from email_addresses where email_address =
'foo@foo.com';
if not found then
insert into email_addresses(email_address) returning email_address_id;
else
insert into users(email_address_id) values (resolved_id)
end if;
OR, you can go the natural route (which tend to prefer):
CREATE TABLE email_addresses
(
email_address VARCHAR primary key
);
create table users
(
user_id BIGSERIAL primary key,
email_address VARCHAR references email_addresses on update cascade
on delete cascade/set null,
)
your insert will look like this (pseudo code):
insert into email_addresses(email_address)
select 'foo@foo.com' where not exists
(select 1 from email_addresses where email_address = 'foo@foo.com')
insert into users (email_address) values ('foo@foo.com');
Obviously this is a rough sketch, you may need to consider locking,
contention, etc. But a trigger is overkill for this problem.
merlin
