0

I have two tables. The first table is a list of database names and backup files. The second table is the relationship of said data bases, There're primary, attachment, common and formulary databases and they are used together as groups. Some primary databases may share other databases (attachment, common and formulary) with a working group.

Example databases 'sales' and 'marketing' are primary databases and they share attachment, common and formulary databases. Another primary database, 'distribution', shares common with the other two but has its own attachment and formulary. And there are other primary databases that have their own relations ships with the supporting databases.

Each column in DatabaseRelationships must exist in the database x ref table. But SQL seems to prevent this. How do I make this happen?

CREATE TABLE [dbo].[DatabaseNameToFileNameXref](
    [DatabaseName] [nvarchar](128) NOT NULL,
    [FileName] [nvarchar](2048) NOT NULL,
 CONSTRAINT [PK_DatabaseNameToFileNameXref] PRIMARY KEY CLUSTERED 
(
    [DatabaseName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[DatabaseRelationships](
    [PrimaryDatabaseName] [nvarchar](128) NOT NULL,
    [AttachmentDatabaseName] [nvarchar](128) NOT NULL,
    [CommonDatabaseName] [nvarchar](128) NOT NULL,
    [FormularyDatabaseName] [nvarchar](128) NOT NULL,
 CONSTRAINT [DatabaseRelationships_PK] PRIMARY KEY CLUSTERED 
(
    [PrimaryDatabaseName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

It has been suggested that this question is related to the "Polymorphic Association" problem. However, I do not believe this is the case. Each column is a DatabaseBase, the link back to the xref table doesn't determine the type. The type of database is determined by the DatabaseRelationship table: Primary, Attachment, etc.

The actual error message is related to the "On Update Cascade" for FK, which yeilds: '..may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION' I now see that I can only have one such FK back to the x-ref table; when I attempt to create a second FK with the same on-update setting, then I get the error. Otherwise, with out the on-update, it works.

So the actual question is, how do I add the cascade feature? Is there some other mechanism that I may use?

Using this short hand, DatabaseRelationship as DR and DatabaseNameToFileNameXref as X, the FK's are from DR.PrimaryDatabase to X.DatabaseName, and from DR.AttachmentDatabase to X.DatabaseName, and from DR.CommonDatabaseName to X.DatabaseName, and lastly DR.FormularyDatabaseName to X.DatabaseName. The X table provides the filename for restoring databases. The DR table describes how the databases are related to each other. If I change the name of a database, with out the cascade, I have to create a new entry in the X table for the new name, then update each row in the DR table that is affected and then delete the old row from the X table, which creates more opportunity for mistakes.

Display name
  • 1,228
  • 1
  • 18
  • 29
  • Why no identity columns? – steamrolla Feb 07 '17 at 20:42
  • @steamrolla just tossing an identity as the primary key of every table is not always a good approach. – Sean Lange Feb 07 '17 at 21:44
  • 1
    Q: Why no identity columns? A: I generally don't like identity columns, Also 'DatabaseName' and 'PrimaryDatabaseName' are unique and work well as primary keys for their respective tables. – Display name Feb 07 '17 at 21:56
  • @SeanLange agreed. but, here, we'd get similar speeds while using less data, and, don't have to deal with key issues when someone wants the name of one to change (which will probably happen, because managers =D). – steamrolla Feb 07 '17 at 22:06
  • 1
    The real issue I see here is that you are describing a many to many relationship but your data model does not support that. You need a third table. Each primary database can have 1 or more related databases and each related database can belong to 1 or more primary databases. This is a classic many to many relationship which needs a bridge table in between them. – Sean Lange Feb 07 '17 at 22:16
  • What does "SQL seems to prevent this" mean? Ie how would you like this constrainted? 2. This is a faq, google 'stackoverflow database sql' plus multiple/many/two FKs/relationships/associations multiple/many tables or subtyping or polymorphism. – philipxy Feb 08 '17 at 11:15
  • Possible duplicate of [Possible to do a MySQL foreign key to one of two possible tables?](http://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables) – philipxy Feb 08 '17 at 11:17
  • I now see that the problem is not the actual FK relationships per-se, I can create one relationship for each column in the DatabaseRelationship back to the same column in the Xref table.The issue is that I also "on update cascade" so that a change in the X-ref table for the database name would then also change the related entry in the DatabaseRelationships table. – Display name Feb 08 '17 at 15:08

1 Answers1

0

The problem stems from a limitation in T-SQL itself. From this Error message 1795 "a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement.

The solution is to use triggers on the lookup table to simulate an update cascade for the remaining relationships.

Display name
  • 1,228
  • 1
  • 18
  • 29