Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2013
    Posts
    18

    Unanswered: [Solved] Relational Integrity Problem - Multiple Cascading Paths

    Hi,

    I'm getting the following error from SSMS when running some DDL statements.

    Msg 1785, Level 16, State 0, Line 6
    Introducing FOREIGN KEY constraint 'REL_Company_jCompanyContactTechnical' on table 'jCompanyContactTechnical' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    The situation is that I have three tables:
    Company
    Person
    jCompanyContactTechnical

    Company and Person are the parent tables, with jCompanyContactTechnical acting as a junction table to allow a many to many relationship between members of the Company table and members of the Person table. I have specified relationships from both parent tables to the junction table, and those relationships are set to enforce cascading updates and deletes, however; SQL Server doesn't seem to want to let me do this (based on the above error message). Why doesn't this work?

    I would have thought this arrangement means that if I update/delete a Company record, then it is cascaded to the junction table, and similarly if I update/delete a record in the Person table, it is cascaded to the junction table. I'm not seeing how that 'may cause cycles or multiple cascade paths' given that the cascade is (I think) directional and only specified from the parent table to the child (which is the junction table)....Can anyone shed some light on what I'm doing wrong?
    Last edited by Simple_One; 01-07-14 at 19:20.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm going to hazard a guess that you've accidentally created a relationship and may not realize the consequences of that relationship. I'd suggest that you drop all three tables, then use a script to recreate them as you want them.

    If you can reproduce the error, then post the script so that we can analyze/try it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2013
    Posts
    18
    Here's the part of the script that I believe is causing the issues. You probably only need to look at the last two code blocks that create the foreign key constraints, but I've included the other indexes for the sake of completeness.

    This script was generated using the ModelRight software, and I'm not particularly sure what the deal is with the 'IX_KEYS_' prefixed indexes that it has produced (these have a type listed as "Key's Index" in ModelRight compared to the conventional indexes that are prefixed just with 'IX_' and have a type of 'Index').

    ---
    --- CREATE TABLE: Test_Schema.jCompanyContactTechnical
    ---
    CREATE TABLE Test_Schema.jCompanyContactTechnical
    (
    IDCompany int NOT NULL,
    IDPerson int NOT NULL,
    Commentary nvarchar,
    CONSTRAINT PK_jCompanyContactTechnical PRIMARY KEY CLUSTERED (IDCompany, IDPerson)
    )
    GO

    ---
    --- CREATE INDEX: IX_jCompanyContactTechnical_1
    ---
    CREATE UNIQUE INDEX IX_jCompanyContactTechnical_1 ON Test_Schema.jCompanyContactTechnical
    (
    IDCompany
    )
    GO

    ---
    --- CREATE INDEX: IX_jCompanyContactTechnical_3
    ---
    CREATE UNIQUE INDEX IX_jCompanyContactTechnical_3 ON Test_Schema.jCompanyContactTechnical
    (
    IDPerson
    )
    GO

    ---
    --- CREATE INDEX: IX_KEYS_jCompanyContactTechnical_REL_Person_jCompa nyContactTechnical_4
    ---
    CREATE INDEX IX_KEYS_jCompanyContactTechnical_REL_Person_jCompa nyContactTechnical_4 ON Test_Schema.jCompanyContactTechnical
    (
    IDPerson
    )
    GO

    ---
    --- CREATE INDEX: IX_KEYS_jCompanyContactTechnical_REL_Company_jComp anyContactTechnical_5
    ---
    CREATE INDEX IX_KEYS_jCompanyContactTechnical_REL_Company_jComp anyContactTechnical_5 ON Test_Schema.jCompanyContactTechnical
    (
    IDCompany
    )
    GO

    ---
    --- CREATE FOREIGN KEY CONSTRAINT: REL_Person_jCompanyContactTechnical
    ---
    ALTER TABLE Test_Schema.jCompanyContactTechnical ADD
    CONSTRAINT REL_Person_jCompanyContactTechnical FOREIGN KEY (IDPerson)
    REFERENCES Test_Schema.Person(IDPerson)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    GO


    ---
    --- CREATE FOREIGN KEY CONSTRAINT: REL_Company_jCompanyContactTechnical
    ---
    ALTER TABLE Test_Schema.jCompanyContactTechnical ADD
    CONSTRAINT REL_Company_jCompanyContactTechnical FOREIGN KEY (IDCompany)
    REFERENCES Test_Schema.Company(IDCompany)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    GO
    SSMS query builder will execute "CREATE FOREIGN KEY CONSTRAINT: REL_Person_jCompanyContactTechnical" fine, but then throws the error on the "CREATE FOREIGN KEY CONSTRAINT: REL_Company_jCompanyContactTechnical" code block.

  4. #4
    Join Date
    Sep 2013
    Posts
    18
    Here's an image to help explain:

    http://imageshack.us/a/img823/9234/67uv.png

    I'm wondering if the problem stems from having a foreign key constraint of IDCompany in the Person table?

    What I'm trying to achieve is the following:
    * Have a table of companies with a surrogate primary key (IDCompany).
    * Have table of people with a surrogate primary key (IDPerson) in which there is a foreign key constraint that relates the person to the company they're employed by.
    * Have a joining table between the two that allows a many to many relationship between IDCompany and IDPerson. The aims is that that a specific person can act as the technical contact for more than one company (i.e. not only for the company they're employed by), and a company can have more than one technical contact.

    Should I be going about this differently?

  5. #5
    Join Date
    Sep 2013
    Posts
    18
    Some googling hurls up a lot results:
    https://www.google.com/webhp?complet...ths+sql+server

    It seems that SQL server assumes a worse case scenario with respect to multiple paths and/or cycles and flat out refuses to create them, even if they couldn't actually occur due to the data content:
    http://support.microsoft.com/kb/321843

    The answer seems to be that you can keep only one path, and all others must be replaced with triggers:
    Solving the SQL Server Multiple Cascade Path Issue with a Trigger
    http://www.dbforums.com/microsoft-sq...ths-error.html
    Last edited by Simple_One; 01-07-14 at 19:19.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    You don't use ISO-11179 rules, so it is hard to read. What is a “j_company”? Why are you not using DUNS for them? Etc. What is a “person_id”? In a valid model, a person plays a role and that is the column name. And you are telling us that you have only generic person of them with a singular table name. And do business with only one company.

    Let's get up to ANSI/ISO and put all of your DRI in one table:

    CREATE TABLE Company_Contacts
    (company_duns CHAR(9) NOT NULL UNIQUE
    REFERENCES Companies(company_duns)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    contact_id CHAR(10) NOT NULL UNIQUE
    REFERENCES Contacts(contact_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    PRIMARY KEY (company_duns, contact_id),
    contact_commentary NVARCHAR(25) NOT NULL);

    Notice that you have overlapping UNIQUE constraints (that is what UNIQUE and PRIMARY KEY constraints are both called). Given A={a, b, c} and X={x, y, z}, the UNIQUE (A, X) constraint allows:

    {(a, x), (a, y), (a, z),
    (b, x), (b, y), (b, z),
    (c, x), (c, y), (c, z)}

    But the UNIQUE(A) and UNIQUE(X) trim out a subset from this set, perhaps:

    {(a, x), (b, y), (c, z)}

    In short, PRIMARY KEY(A, X) is redundant. Drop it. This scares people who think that a table has to have a PRIMARY KEY, but are not comfortable with more than one key or with overlapping keys.

    T-SQL is over-sensitive about cycles, but there is a history about this. DB2 about million years ago allowed reference cycles A → B, A → C, B → C in a schema. So a change to table A cascades to B and C. Fine. But the change to B now cascades to C. Which change takes effect in table C? The answer was “the last guy to touch table C! Random! Ugh!”

  7. #7
    Join Date
    Sep 2013
    Posts
    18
    Sorry, a lot of your post is a bit beyond me as I don't know what some of the acronyms mean (DUNS, DRI). Anyway, I'll have a look at your example script and try and read up on some of the things you have mentioned.

    Also, I don't understand why you feel this structure can't work, to clarify the current structure:

    All the companies that we are involved with are stored in the Company table, 1 record per company with IDCompany as an identity field and the PK.
    Similarly for a person, their details go in the Person table, 1 record per person with a foreign key constraint (IDCompany) being the company they are employed by and IDPerson as an identity field and the PK.
    The j tables (junction/joining, whatever you want to call them) are used to define the necessary and relevant relationships between the entities and/or the role of the entities from our business point of view. So for instance, jCompanyContactTechnical defines which person or persons are the technical contacts for which company or companies by pairing their respective PK's (IDPerson and IDCompany) and knowing the table name is for technical contacts.
    Similarly by having a Customers and Suppliers table, the role that a company plays is defined by which table its identifier (IDCompany from the Company table) is found in. If it's in Customers then that company is a customer, likewise for Suppliers (or it might be in both, which is valid in our case). Since there are not a huge amount of different roles or relationships that are relevant to our business, its not hard to manage these tables whilst knowing that if you need to update the details for a specific company, then there is one (and only one) possible location in which it needs to be done, the Company table; and similarly for a person etc.
    So in short all the types of physical or legal entities are recorded in a single table for that entity type, then their identifiers are placed in named tables to explain their role or the relationships between entities.

    Your point about indexes is noted. I haven't looked at indexes as yet, whatever is in the script is what has been generated automatically by the modelling software. I haven't played with the settings yet that control the auto generation of these indexes so I'm not surprised that there is redundancy. Nevertheless, thanks for your example, I understand what you are pointing out.
    Last edited by Simple_One; 01-08-14 at 19:32.

  8. #8
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    More basic data modeling ..

    Data Universal Numbering System - Wikipedia, the free encyclopedia

    This is the universal identifier for businesses. There are several hundred million of them in use. This is to commercial data processing like an SI unit is to engineering.

    DRI = Declarative Referential Integrity. This is part of SQL.

    Records are physical units of storage; rows are logical. This means row can be virtual or materialized. This is a HUGE difference between rows and records.

    Fields are a contiguous storage area in a record in a particular format. Columns are logical; they can be virtual and we do not care how they are physically stored.

    The proprietary, non-relational IDENTITY is a count of physical insertion attempts and we never, never use it in valid RDBMS. And we certainty never use it as a key!! It makes no logical sense. Good RDBMS programmers look for an industry standard identifiers, learn how to make data encodings, etc. Numbering a record list on one disk on one machine ain’t it! This is as silly as using the parking space number you have today instead of the VIN for your car title.

    The term “junction table” is not RDBMS; it is a from the old network databases. It meant a multi-way set of pointers. We have a table that models either a set of entities or a relationship, not both. We talk about referenced and referencing tables.

    >> So in short all the types of physical or legal entities are recorded in a single table for that entity type, then their identifiers are placed in named tables [as opposed to un-named tables? ] to explain their role or the relationships between entities. <<

    ALL types of entities? This design flaw is called “Automobiles, Squids and Lady Gaga”; you have table that is overloaded instead of modeling a single set or relationship.

  9. #9
    Join Date
    Sep 2013
    Posts
    18
    Before we go further, understand that I appreciate you are trying to help, so thanks for the time you have taken to respond.
    I'll be honest, I'm not as hung up on the academic terminology as you appear to be, I do appreciate that you are correct (I've read enough the books that have told me that) but most of the people I converse with on this topic are familiar with the terms 'record' and 'field' from days of old, so I'm in the habit of using them in order to make communication simpler most of the time.

    I also think you may have over-estimated the scope of this database, which is unsurprising and not your fault since I didn't outline it. This for an SME in Australia, the DUNS is not appropriate, as many companies that we deal with do not have one. Similarly, even an ABN or ACN is not appropriate as a key, as we might have different contracts with different 'divisions' or 'branches' of a corporation that use different trading names, but fall under the same ABN and/or ACN. In our situation, 'companies' are the entities that are associated with a contract that comes in further down the line, we need to be able to distinguish who is holding the contract.

    Logically, there is nothing wrong with using a surrogate key, I'm not sure why you see it as such an issue. Using an identity field ensures that the number will be unique, ergo, any entry made to the table will be able to be uniquely identified by looking up that number. I understand pushing the use of natural or pre-existing keys, but in this situation there are none. I could use a combination key across many columns of this table to generate something that would probably always be unique, but why attempt that, I may as well use a surrogate because it will always be unique and is easier to reference.

    My surrogate 'IDCompany' or 'IDPerson', is analogous to assigning a DUNS number; I'm just doing it many levels further downstream at a much smaller scale. On the face of it, a DUNS has no more significance to the entity it is related to than my surrogate key does. It's importance is simply that it is uniquely identifying a row in someone else's table and database; which is what my solution is doing for me here. I don't care if Identity is proprietary to SQL Server and not standardised as part of SQL, I just need something that will auto increment and that is guaranteed to be unique in that table, whether its derived from some encoding of other data columns is irrelevant here, doing that would just add unnecessary complexity in this case.

    I'm not sure how much you know about VIN's, but they're partly encoded and then concatenated with a serialised number relating to the build sequence. In my case, I don't need a 'human readable' string that provides any information beyond the insertion/build sequence, it is an adequate reference for this purpose.

    "ALL types of entities? This design flaw is called “Automobiles, Squids and Lady Gaga”; you have table that is overloaded instead of modeling a single set or relationship."

    I don't follow that line of argument at all, sorry. That is exactly what this design prevents. Each table does only model a single entity type. Where required there are separate tables that define the relationships between entities. At no point is one table used for more than one entity type, or for more than one form of relationship.

    Anyway, this discussion has got somewhat off track. I've resolved the cycles issue using triggers, but thanks for the information, always good to get input from others.
    Last edited by Simple_One; 01-09-14 at 01:12.

  10. #10
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    DUNS is not appropriate, as many companies that we deal with do not have one
    Even I have a DUNS; I needed it to do business with DELL. There are over 225 Million of them issued and they are free, so my clients make their suppliers get them.

    Those things are not surrogates; they are exposed physical locators. Nobody reads Dr. Codd any more! A quote from Dr. Coddd: "..Database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them ..."(Dr. Codd in ACM TODS, pp 409-410) and Codd, E. (1979), Extending the database relational model to capture more meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434.

    This means that a surrogate ought to act like an index; created by the user, managed by the system and NEVER seen by a user. That means never used in queries, DRI or anything else that a user does.

    Codd also wrote the following:

    "There are three difficulties in employing user-controlled keys as permanent surrogates for entities.

    (1) The actual values of user-controlled keys are determined by users and must therefore be subject to change by them (e.g. if two companies merge, the two employee databases might be combined with the result that some or all of the serial numbers might be changed.).

    (2) Two relations may have user-controlled keys defined on distinct domains (e.g. one uses social security, while the other uses employee serial numbers) and yet the entities denoted are the same.

    (3) It may be necessary to carry information about an entity either before it has been assigned a user-controlled key value or after it has ceased to have one (e.g. and applicant for a job and a retiree).

    These difficulties have the important consequence that an equi-join on common key values may not yield the same result as a join on common entities. A solution - proposed in part [4] and more fully in [14] - is to introduce entity domains which contain system-assigned surrogates. Database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them....." (Codd in ACM TODS, pp 409-410).

    References
    Codd, E. (1979), Extending the database relational model to capture more
    meaning. ACM Transactions on Database Systems, 4(4). pp. 397-434

    On the face of it, a DUNS has no more significance to the entity it is related to than my surrogate key does.
    Nope, a DUNS, VIN, ISBN, etc can be validated and verified while a physical locator like IDENTITY, ROW-ID, et al exists only within one piece of hardware. Your insurance company, car dealer, CarFax and the department of motor vehicles all agree on the VIN; they would not agree on the order of insertion of a physical record on a disk.

    Triggers? More procedural, proprietary code? Oh well, you get a D in Mother Celko's into to Relational Database class

  11. #11
    Join Date
    Sep 2013
    Posts
    18
    Quote Originally Posted by Celko View Post
    Even I have a DUNS; I needed it to do business with DELL. There are over 225 Million of them issued and they are free, so my clients make their suppliers get them.
    Yep, that will not be happening. If you start trying to pull those sort of antics with small businesses, they will just take their business elsewhere. I understand that you can push those sort of things with larger companies and bigger deals, but that's just more overhead to a small business that just wants to get on with doing their thing. If all our competitors made everyone do it, then fine, but they don't.

    Quote Originally Posted by Celko View Post
    A quote from Dr. Coddd: "..Database users may cause the system to generate or delete a surrogate, but they have no control over its value, nor is its value ever displayed to them ..."(Dr. Codd in ACM TODS, pp 409-410) and Codd, E. (1979)
    As academically magnificent as that is (and I do understand the argument for using meaningfully encoded identifiers), I don't have the time or the reason to sit down and do so. I don't care what number is used as the identifier as long as it is unique, and as long as it is issued only once. From that point on it becomes a meaningful identifier for reference purposes. In 1979 maybe the great Codd could would not ever have seen or been able to see the values of a surrogate, In this day and age it can be displayed and queries can be made against an IDENTITY column without issue. Note Pat's sig in the second post, courtesy of Einstein. I'm not concerned with how puritanical my approach is, simply that it works and takes no time to do.

    I understand that no one else can generate the same number for the same customer. I also understand that I could not regenerate the same number for the same customer if the database was moved or data loss occurs. I understand that IDENTITY is based on the physical insertion attempts. These are not issues that bother me.


    Quote Originally Posted by Celko View Post
    (1) The actual values of user-controlled keys are determined by users and must therefore be subject to change by them (e.g. if two companies merge, the two employee databases might be combined with the result that some or all of the serial numbers might be changed.).

    (2) Two relations may have user-controlled keys defined on distinct domains (e.g. one uses social security, while the other uses employee serial numbers) and yet the entities denoted are the same.

    (3) It may be necessary to carry information about an entity either before it has been assigned a user-controlled key value or after it has ceased to have one (e.g. and applicant for a job and a retiree).
    None of those three points will be an issue in this system. I understand where they could be issues in different circumstances, but not in this case.

    Quote Originally Posted by Celko View Post
    Nope, a DUNS, VIN, ISBN, etc can be validated and verified while a physical locator like IDENTITY, ROW-ID, et al exists only within one piece of hardware. Your insurance company, car dealer, CarFax and the department of motor vehicles all agree on the VIN; they would not agree on the order of insertion of a physical record on a disk
    I don't need my identifiers validated externally, they are only relevant within this system.

    Quote Originally Posted by Celko View Post
    Triggers? More procedural, proprietary code? Oh well, you get a D in Mother Celko's into to Relational Database class




    Your points have validity and in some situations/circumstances that are different to those for my system, they would be critical. I could follow them inside this system too, if I had more time; but I don't. I'll sacrifice theory for a practical result.
    Anyway, thanks for your input, time and referenced arguments!
    Last edited by Simple_One; 01-09-14 at 20:00.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Simple_One View Post
    I'm wondering if the problem stems from having a foreign key constraint of IDCompany in the Person table?
    Yes. That is what is causing the problem.
    You will have to decide which of the two referential integrity rules you want to enforce through cascading updates and deletes, and enforce the other one using a different method.

    The problem is likely the delete, though. You might be able to enforce deletes on your jCompanyContactTechnical table, and just updates on the other relationship (would you really want to delete records in Company if a Person is deleted, or vice versa?).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Sep 2013
    Posts
    18
    Thanks Blindman, though I've already resolved it in a different fashion.

    Should anyone have soldiered through the discussion between myself and Celko, another (and possibly better) solution for the surrogate keys in SQL Server 2012 might be to create a SEQUENCE and use it to produce the values for the surrogate keys. You can insert values from the sequence into a column of your table and define that column as the PK (and place whatever other constraints you have for uniqueness etc). A sequence behaves very much like IDENTITY (i.e. an auto incrementing integer).

    The only real upside to this compared to using IDENTITY, is that the column is user editable, so surrogate keys can then be changed later on should the need arise for whatever reason.
    Edit: Just bare in mind that sequences are created independently from the tables themselves, and therefore you must alter/delete them independently from whatever table you might be utilising them in.
    Last edited by Simple_One; 01-12-14 at 20:44.

  14. #14
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    SEQUENCE is not surrogate

    A sequence is a particular kind of attribute. It is an ordered, dense set of values. Think check numbers, invoice numbers in Italy that have to be serial, etc.

    It is not necessarily a key, just unique in a context (many people will have a cheek #042 at the same bank).

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Celko View Post
    It is not necessarily a key, just unique in a context (many people will have a cheek #042 at the same bank).
    Exactly! I agree with you that this has the potential to be a problem in the grand scheme of the universe, but in the problem at hand for Simple_One this is a limitation that is of no consequence.

    As you and I have battled long and hard, I use both an identity for a SK (Surrogate Key) which is a surrogate for an NK (Natural Key) if and when a Natural Key exists. I define indexes for both the SK and the NK, and make it clear to the developers that the SK is simply a stand-in that the database engine can use more efficiently than the NK... The two are equivalent and inextricably linked, but the SK offers performance advantages. As you've pointed out in the past, the database engine ought to handle this for us (and some engines can do that), but Microsoft SQL Server can't do it yet.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •