Re: Foreign key check only if not null?

On Mon, Sep 12, 2011 at 6:48 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:

> I have a column in my table:
>    user_id  varchar(100) ;
>
> This can be NULL, or it can have a value. If it has a value during
> INSERT or UPDATE, I want to check that the user exists against my
> "Users" table. Otherwise, NULL is ok. (Because the functionality in
> question is open to both unregistered and registered users).
>
> Any idea on how I can implement a FOREIGN KEY constraint?

This sounds like an ordinary foreign key constraint. Just be sure
that you drop the null constraint on the table's user_id column.

So:

ALTER TABLE "my table"
ADD CONSTRAINT "my table_Users_user_id_fkey"
FOREIGN KEY (user_id)
REFERENCES "Users" (user_id) ON UPDATE CASCADE ON DELETE SET NULL,
ALTER COLUMN user_id DROP NOT NULL;

--
Regards,
Richard Broersma Jr.