How to use cascade delete vs restrict for referential integrity with one-to-one record relationships?

Question

I have a table CPT defined with

   TABLE cpt
(
   recid serial NOT NULL,
   ccpt text,
   cdesc text NOT NULL,
   ninsurance numeric(10,2),
   ncash numeric(10,2),
  ..... )

In which I wish to move the values ninsurance and ncash to another record in the cpt table. Therefore, I move these values to another table, cpt_invoice that is defined as

      TABLE cpt_Invoice
(
   recid serial NOT NULL,
   cpt_recid integer NOT NULL,      <--primary key from the cpt table.
   ninsurance numeric(10,2),
   ncash numeric(10,2),
   CONSTRAINT cs_cpt_invoice FOREIGN KEY (cpt_recid)
   REFERENCES cpt (recid) MATCH SIMPLE
   ON UPDATE CASCADE ON DELETE CASCADE,
  ..... )

Reducing the cpt table to:

   TABLE cpt
(
   recid serial NOT NULL,
   ccpt text,
   cdesc text NOT NULL,
  ..... )

So far so good. Now what is the best way to enforce the following contstraints:

1. Record in cpt_invoice can NOT be deleted if any record in cpt is referencing it, and
2. If a record in cpt is deleted, then the referenced record in cpt_invoice is also deleted.

Note: These tables are one-to-one meaning that each record in cpt will have one and only one record in cpt_invoice and visa versa.

The only idea that comes to mind is to add the primary key of cpt_invoice to the cpt table then on the cpt table do

REFERENCES cpt_invoice (recid) ON DELETE RESTRICT

Does using a back-link make sense? How do others do this?

TIA

Answer 1

I would write the cpt_Invoice like this:

-- mixed case not recommended
create table TABLE cpt_invoice ( 
   recid serial NOT NULL,
   cpt_recid integer references cpt (recid) on delete cascade,
   ninsurance numeric(10,2)
   -- more...
  )

and you will be able to delete a record from cpt with automatic deletion of all dependent records in cpt_invoice (meeting requirement 2).

Your requirement 1 does not make sense: every record in cpt_invoice depends on one in cpt.

Edit: I don't know what your environment or application is, for more security look at the SQL GRANT features, you can limit users to certain actions.