Re: Thoughts on a surrogate key lookup function?

On Fri, Nov 5, 2010 at 5:27 PM, Nick <nboutelier(at)gmail(dot)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(at)foo(dot)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(at)foo(dot)com' where not exists
(select 1 from email_addresses where email_address = 'foo(at)foo(dot)com')

insert into users (email_address) values ('foo(at)foo(dot)com');

Obviously this is a rough sketch, you may need to consider locking,
contention, etc. But a trigger is overkill for this problem.

merlin