[GENERAL] Foreign key check only if not null?
Hi, I bet this is a simple solution but I have been racking my brains. 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? Or do I need a pre-insert and pre-update RULE for this? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign key check only if not null?
hi, fks do just that. you can create your fk with just one command: alter table xxx add constraint fk_name foreign key (user_id) references users (id); parent table's id field should also be of the same type and also it should be primary key or at least unique. you can create your pk with (if you don't already have one): alter table users add constraint pk_users primary key (id); also i wouldn't use a varchar(100) as a pk field, i would suggest using some other datatype maybe a bigint, but that always finally depends on the model, like if there is a really good reason for using a varchar(100), well, it's your call. regards, eduardo On Mon, Sep 12, 2011 at 10:48 PM, Phoenix Kiula phoenix.ki...@gmail.comwrote: Hi, I bet this is a simple solution but I have been racking my brains. 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? Or do I need a pre-insert and pre-update RULE for this? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign key check only if not null?
On Mon, Sep 12, 2011 at 6:48 PM, Phoenix Kiula phoenix.ki...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
