2

I've got a very simple relationship between two tables that are used to manage custom UI branding:

ui_portal_branding

CREATE TABLE ui_portal_branding 
(
    id VARBINARY(16) NOT NULL, 
    branding_type VARCHAR(128) NOT NULL, 
    portal_name NVARCHAR(128) NOT NULL, 
    theme_id VARBINARY(16) NOT NULL, 
    portal_logo VARBINARY(16) NULL, 
    portal_favicon VARBINARY(16) NULL, 
    background_color VARCHAR(50) NULL, 
    organization_id VARBINARY(16) NULL, 

    CONSTRAINT pk_ui_port_bran_id PRIMARY KEY (id) 
)

ui_portal_resource

CREATE TABLE ui_portal_resource 
( 
    id VARBINARY(16) NOT NULL, 
    mime_type NVARCHAR(128) NOT NULL, 
    binary_data VARBINARY(MAX) NOT NULL, 

   CONSTRAINT pk_ui_port_reso_id PRIMARY KEY (id) 
)

Branding is the main table, Resources is a BLOB store for binary data. Both portal_logo and portal_favicon in the branding table are optional binary data from the resource table.

I'd like to define this as a foreign key constraint with the following general logic: neither logo or favicon are required to be defined. If they are defined, they point to a record in the resource table by ui_portal_resource.id. If the data is deleted from the resource table, I want to set the corresponding column in the branding table to null. I don't want to disallow the resource deletion, I don't want to cascade the delete to the branding table.

So I define the following:

ALTER TABLE ui_portal_branding 
    ADD CONSTRAINT fk_ui_port_bran2ui_port_reso 
    FOREIGN KEY (portal_logo) REFERENCES ui_portal_resource (id) 
    ON DELETE SET NULL

So far so good. Now I define:

ALTER TABLE ui_portal_branding 
    ADD CONSTRAINT fk_ui_port_bran2ui_port_reso2 
    FOREIGN KEY (portal_favicon) REFERENCES ui_portal_resource (id) 
    ON DELETE SET NULL

and all of a sudden we have a problem:

Introducing FOREIGN KEY constraint 'fk_ui_port_bran2ui_port_reso2' on table 'ui_portal_branding' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

This to me seems wrong. I'm not introducing a cycle. It's two tables with the foreign keys defined in a single direction. I guess it can technically be multiple cascade paths–if the same resource is the favicon and the logo it has to set 2 things null. But really? This is the deal breaker for the SQL Server engine? Oracle and Postgres both find this situation to be acceptable.

Is there a sensible workaround for this issue? I'm not interested in a solution involving triggers. Is there a better way to model the data? I was hoping that the resources table could service more than just the branding table, which led to the current FK placement. But maybe that is just not possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jeff
  • 3,669
  • 1
  • 23
  • 33
  • why would you relate two fields on the same foreign field ? – Demonyowh May 05 '17 at 02:22
  • I'm not, they're different foreign fields. Both logo and favicon are a binary resource. It seems entirely reasonable to have two different FKs to the same resource PK. – Jeff May 05 '17 at 02:23
  • A single primary key can be used as a foreign key in many tables. Why should n foreign keys per PK be different if the FK is in the same table as another FK or a different table? – Jeff May 05 '17 at 02:25
  • have you tried to do this in table designer? – anatol May 05 '17 at 02:30
  • 1
    take a look here - http://stackoverflow.com/a/803861/4269118 – anatol May 05 '17 at 02:33
  • you cant assign two fields on the same table to be related on the same foreign table field .. – Demonyowh May 05 '17 at 02:36
  • This (https://support.microsoft.com/en-us/help/321843/error-message-1785-occurs-when-you-create-a-foreign-key-constraint-that-may-cause-multiple-cascade-paths) is absolutely ridiculous. Even the example they give is a pretty clear example of a situation where you'd want to do this. Not to mention the fact that while I'm technically cascading something, set null is hardly a tricky thing to manage. – Jeff May 05 '17 at 02:39
  • http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths?rq=1 – anatol May 05 '17 at 02:50
  • SQL server can't handle the ON DELETE SET NULL. Pathetic! The official excuse has to do with deletion paths, but obviously a SET NULL isn't as complicated as a cascade delete to pull off. SQL Server needs to fall into the ocean – Alkanshel Oct 18 '17 at 01:46

0 Answers0