Can i use NOT NULL constraint with a condition? in PostgreSQL

im a newbie to PostgreSQL, is there any way that i can make some tuples not deletable if some condition holds? to be specific, suppose i have:

Table Males( Name_A, Profession)

Table Students( Names_B, Date_birth)

where Names_B references Names_A, how can i make sure that only those Names_A are "not deletable" whose Date_birth="xx/yy/zz"

sorry if i couldnt clearly explain it, havnt found anything in DDL using NOT NULL constraint to write this up.

Thanks in advance for the help!开发者_如何学运维

CREATE FUNCTION protect_delete() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF OLD.date_birth = 'xx/yy/zz' THEN  -- don't actually use this date format
        RETURN NULL;  -- don't delete
    ELSE
        RETURN OLD;
    END IF;
END;
$$;

CREATE TRIGGER protect_delete BEFORE DELETE ON students FOR EACH ROW
    EXECUTE PROCEDURE protect_delete();

You can use a PostgreSQL rule:

create rule rule_test as 
  on delete to test 
  -- old is a reference to your table 
  where old.birth = '2011-1-1' -- or whatever condition you want 
  do instead nothing;

One a big table this may run faster since this will modify the query itself and rewrite the query with the condition instead of checking each row. (Triggers may be more powerful and easier to understand if you are planning to do a lot of this type of stuff.)

• create rule - http://www.postgresql.org/docs/current/static/sql-createrule.html
• rules vs. triggers - http://www.postgresql.org/docs/current/static/rules-triggers.html

See Postgres trigger documentation for information on creating triggers. It sounds like you want a row level trigger.

"A row-level trigger fired before an operation ... can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that invoked the trigger (the insertion or modification of a particular table row). "

So within the trigger test for your condition and return null to prevent the deletion, return the trigger row to allow the deletion to continue.