Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Effectively you create a <em>race condition</em> with contradicting rules.</p> <p>My first impulse was to check whether a <a href="http://www.postgresql.org/docs/current/interactive/sql-set-constraints.html" rel="nofollow"><code>DEFERRED</code></a> constraint would help. But it makes sense that it <em>doesn't make any difference.</em></p> <p>I found that the FK constraint that comes <strong>first in the <code>CREATE TABLE</code></strong> script is the winner of this race. If the <code>ON DELETE CASCADE</code> comes first, the delete is cascaded, if <code>ON DELETE RESTRICT</code> comes first, the operation is aborted.</p> <p>Consider the demo on <a href="http://sqlfiddle.com/#!12/09465/5" rel="nofollow"><strong>SQL Fiddle</strong></a>.</p> <p>This seems to correlate with a smaller <code>oid</code> in the catalog table <a href="http://www.postgresql.org/docs/current/interactive/catalog-pg-constraint.html" rel="nofollow"><code>pg_constraint</code></a>:</p> <pre><code>SELECT oid, * FROM pg_constraint WHERE conrelid = 'task'::regclass </code></pre> <p>But your feedback indicates, this is not the cause. Maybe <a href="http://www.postgresql.org/docs/current/interactive/catalog-pg-attribute.html" rel="nofollow"><code>pg_attribute.attnum</code></a> decides the race. Either way, as long as it is not documented behavior you cannot rely on it to stay that way in the next major version. Might be worth to post a question on pgsql-general@postgresql.org.</p> <p>Independent from all that, you need to consider other rows: even if <code>CASCADE</code> would go through for a row in <code>task</code> that has both <code>tenant_id</code> and <code>customer_id</code> pointing to a <code>person</code>, it will still be restricted if any row has only <code>customer_id</code> referencing <code>person</code>.<br> Another <a href="http://sqlfiddle.com/#!12/f9e67/1" rel="nofollow">SQL Fiddle</a> demonstrating the case.</p> <h3>How to disable the constraint?</h3> <p>Your best bet is to drop and recreate it. Do it all inside a transaction to make sure you don't corrupt referential integrity.</p> <pre><code>BEGIN; ALTER TABLE task DROP CONSTRAINT task_customer_id_fkey; DELETE FROM person WHERE id = 3; ALTER TABLE task ADD CONSTRAINT task_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES person (id) ON DELETE RESTRICT; COMMIT; </code></pre> <p>This locks the table exclusively and is not fit for routine use in a multi-user environment.</p> <p>How did I know the <em>name</em> of the constraint? I took it from <code>pg_constraint</code> as demonstrated above. Might be easier to use an explicit constraint name to begin with:</p> <pre><code>CREATE TEMP TABLE task ( customer_id integer NOT NULL ,tenant_id integer NOT NULL REFERENCES person (id) ON DELETE CASCADE ,CONSTRAINT task_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES person (id) ON DELETE RESTRICT ); </code></pre> <hr> <p>There is also </p> <pre><code>ALTER TABLE task DISABLE trigger ALL; </code></pre> <p><a href="http://www.postgresql.org/docs/current/interactive/sql-altertable.html" rel="nofollow">More in the manual here</a>. But that would disable <em>all</em> triggers. I had no luck trying to disable only the trigger created by the system to implement a single FK constraint.</p> <p>Other alternatives would be to implement your regime with <a href="http://www.postgresql.org/docs/current/interactive/sql-createtrigger.html" rel="nofollow">triggers</a> or <a href="http://www.postgresql.org/docs/current/interactive/sql-createrule.html" rel="nofollow">rules</a>. That would work just fine, but those are not enforced as strictly as foreign keys.</p>
    singulars
    1. This table or related slice is empty.
    1. POShould I temporarily disable foreign key constraints? How?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USErwin Brandstetter
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POShould I temporarily disable foreign key constraints? How?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI already tried something similar and, for some reason, it didn't work. I had droped `tenant_id` foreign key and recreated it afterwards, so its oid is bigger (I can tell with the select from `pg_constraint` that you pointed out). I copied the code from your fiddle and it works locally, so this can be a way to achieve my goal, but I think the reason why it works could other than the oid being bigger. I have to try the `DEFERRED` thing. I don't understand the last part of your answer: both `tenant_id` and `customer_id` have `not null` constraints.
      singulars
      1. PO
      1. USclapas
    2. CO@clapas: Consider the update to my answer.
      singulars
      1. PO
      1. USErwin Brandstetter
    3. COAhh! Now I understand what you mean! The scenario in the second sqlfiddle does not happen in my system, because different tenants cannot share a common customer, i.e. the tenant defines a scope. My bad not mentioning it. I guess it is better to not rely on an undocumented behavior, as you say, so I will temporarily disable constraints or implement the solution with triggers or rules, I have not decided it yet. Anyway, thanks very much for your help.
      singulars
      1. PO
      1. USclapas
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload