1

I'm analyzing a following PostgreSQL schema:

CREATE SEQUENCE ref_email_type_ref_email_type_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE ref_email_type
(
  ref_email_type_id integer NOT NULL DEFAULT nextval('ref_email_type_ref_email_type_id_seq'::regclass),
  description character varying(50) NOT NULL,
  CONSTRAINT ref_email_type_pkey PRIMARY KEY (ref_email_type_id)
)

CREATE TABLE email
(
  email_id integer NOT NULL DEFAULT nextval('email_email_id_seq'::regclass),
  ref_email_type_id integer NOT NULL DEFAULT nextval('email_ref_email_type_id_seq'::regclass),
  email_address character varying(100),
  CONSTRAINT email_pkey PRIMARY KEY (email_id),
  CONSTRAINT email_ref_email_type_id_fkey FOREIGN KEY (ref_email_type_id)
      REFERENCES ref_email_type (ref_email_type_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Does it make any sense to have email.ref_email_type_id declared with DEFAULT nextval('email_ref_email_type_id_seq'::regclass) in case of NOT NULL and existing constraint:

  CONSTRAINT email_ref_email_type_id_fkey FOREIGN KEY (ref_email_type_id)
      REFERENCES ref_email_type (ref_email_type_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
6
  • 1
    Short anser: No, it makes no sense. A foreign key is intended to refer to a (primary) key in (another) table. It cannot be both the other key and the value supplied by the sequence. If it were the value supplied by the sequence, chances would be big (~1.0) that this value would violate the FK constraint. Commented Nov 11, 2016 at 15:10
  • @joop chances are so big that they are actually 100%. Transactions does not apply to sequence generation in PostgreSQL, nextval() always executed atomically, and never rolled back (thus always provides a unique value, but usually with gaps in the table). Commented Nov 11, 2016 at 16:26
  • Note 1.0 := 100% (there is a small chance of actually hitting an existing key value) Commented Nov 11, 2016 at 16:28
  • @joop no, actually not If it were the value supplied by the sequence (as you started your sentence). If both values are supplied by the sequence, It will always be a miss, even with high concurrency. Commented Nov 11, 2016 at 16:30
  • @pozs : you probably didn't notice that there were two different sequences involved (one for the PK, one for the referring FK) ? Commented Nov 11, 2016 at 17:19

1 Answer 1

1

No, that doesn't make any sense at all.

It won't do any harm either, since the default value will probably never be used.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.