Re: Foreign key check only if not null?

On Mon, Sep 12, 2011 at 6:48 PM, Phoenix Kiula <phoenix.kiula@gmail.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.