none
Database design multiple cascade path

    Question

  • I've got a design question guys,

    so I have an application where the user can enter patient medical tests results

    the table looks as follows

    PATIENT_TEST

    -----------------

    Id

    Datetime

    Location

    Name

    The application also keeps track of the visits and the associated tests (for that visit), here the visit table

    VISIT

    --------

    Id

    Prescription

    Diagnostics

    Here is the visits test table

    -------

    VISIT_TEST

    VisitId

    TestId

    VisitId REFERENCES VISIT(Id) ON DELETE CASCADE

    TestId REFERENCES PATIENT_TEST(Id) ON DELETE CASCADE

    SQL complains about multiple cascade paths.

    The reason I'm doing this is that, if the visit is deleted I want VISIT_TEST entry to go away AND if the test gets deleted I want the VISIT_TEST entry to go away AS WELL.

    Is there a better design I should follow.

    Thanks in advance

    Thursday, June 27, 2013 11:05 PM

Answers

  • If you search on the error message, you will find many discussions regarding the source of the problem, why it occurs, and how to get around it.  In short, there is a limitation to the use of the cascading delete option.  Is there a better design?  Well - you can't use this one so obviously anything is better.  Those same discussions you find in your search will also have suggestions; the general approach is to not use the cascade option and to delete "from the bottom up".   Just realize that there are some things sql server cannot automatically do for you. 

    Hopefully you've learned from this experience how important it is to post an example that actually demonstrates the problem.  Posting a script that does not generate the error only serves to confuse everyone and, quite often, leads the discussion in a not-so-useful direction.  There are many other issues with your table defintions - one of the more important ones is that you NEVER store a monetary amount in a floating datatype.  Since you're learning, I suggest you stick to more simple models - i.e., "smaller" tables.   

    One last comment.  Always indicate the version and edition of sql server that you are using.  Features vary by version and there is much to be gained by avoiding discussions of features that are not available to you.

    • Marked as answer by raac130 Thursday, July 04, 2013 12:52 AM
    Wednesday, July 03, 2013 8:29 PM

All replies

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you made a mess). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Here is my guess. Think about how vague “name” is! test_name? patient_name? Why do you believe in a generic “id”? That is Kabbalah magic, not RDBMS. Tables model sets, so their names are collective or plural, not singular.  Patients are clearly entities, so where is their table? Tests are clearly entities, so where is their table? Giving a patient a test is a relationship between them, so where is that table? Also, aren't there standard medical codes for these tests? 

    Your Visit_Tests is wrong. You want to locate an event in time (test date) and space (location). This event has a test (FK) and a patient (FK) to exist. 

    CREATE TABLE Patients
    (patient_id CHAR(10) NOT NULL PRIMARY KEY,
     …); 

    CREATE TABLE Tests
    (test_id CHAR(10) NOT NULL PRIMARY KEY,
     …); 

    CREATE TABLE Visits
    (patient_id CHAR(10) NOT NULL
     REFERENCES Patients(patient_id),
     visit_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
     PRIMARY KEY (patient_id, visit_date),
     test_id CHAR(10) NOT NULL
     REFERENCES Tests(test_id)
     ON DELETE CASCADE,
     visit_location CHAR(10) NOT NULL,
     prescription_nbr CHAR(10) DEFAULT 'None' NOT NULL,
     diagnostics CHAR(10) NOT NULL, --- ICD-10 code? 
    .. );
     
    >> If the visit is deleted I want visit_test entry to go away and if the test gets deleted I want the visit_test entry to go away as well.<<-

    The simple deletion and the CASCADE will do this. But this does not make sense. A test is a process and it has a result (Old joke: “My Billy is such a smart boy! He scored a FOUR on his Wasserman test!”), but you do not have a result for the test. Then you do diagnostics and write prescriptions without a result. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 28, 2013 2:02 AM
  • I don't get an error:

    CREATE TABLE PATIENT_TEST 
      ( 
         Id       varchar(30) PRIMARY KEY, 
         Datetime varchar(30), 
         Location varchar(30), 
         Name     varchar(30) 
      ) 
    CREATE TABLE VISIT 
      ( 
         Id           varchar(30) PRIMARY KEY, 
         Prescription varchar(30), 
         Diagnostics  varchar(30) 
      ) 
    CREATE TABLE VISIT_TEST 
      ( 
         VisitId varchar(30) REFERENCES VISIT(Id) ON DELETE CASCADE, 
         TestId  varchar(30) REFERENCES PATIENT_TEST(Id) ON DELETE CASCADE 
      ) 
    GO   


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Saturday, June 29, 2013 2:33 PM
    Moderator
  • I apologize for my first post, I'm still learning relational database designs. This is for a school project, no medical codes or any of that.

    When I ran this script...

    CREATE TABLE PATIENT(
    	Id	INT	IDENTITY(1,1) NOT NULL,
    	FirstName	VARCHAR(64)	NOT NULL,
    	LastName	VARCHAR(64)	NOT NULL,
    	Gender	VARCHAR(16)	NULL,
    	MaritalStatus	VARCHAR(16)	NULL,
    	Birthdate	DATETIME NULL,
    	Age		INT		NULL,
    	HomePhone	VARCHAR(64)	NULL,
    	CellPhone	VARCHAR(64) NULL,
    	Email		VARCHAR(64)	NULL,
    	Employer	VARCHAR(64)	NULL,
    	Insurance	VARCHAR(64)	NULL,
    	InsuranceId	VARCHAR(128)	NULL,
    	Occupation	VARCHAR(64)	NULL,
    	Schedule	VARCHAR(64)	NULL,
    	Address		VARCHAR(128)	NULL,
    	Subdivision	VARCHAR(64)	NULL,
    	City	VARCHAR(64)	NULL,
    	StateResidence	VARCHAR(64)	NULL,
    	Country		VARCHAR(64)	NULL,
    	ZipCode	VARCHAR(64)	NULL,
    	TimeInEating	INT	NULL,
    	Picture	VARBINARY(MAX)	NULL,
    	PreviousDiets	VARCHAR(MAX)	NULL,
    	BreakfastTime	VARCHAR(32)	NULL,
    	LunchTime	VARCHAR(32)	NULL,
    	SnackTime	VARCHAR(32)	NULL,
    	Dinner_Time	VARCHAR(32)	NULL,
    	PRIMARY KEY (Id)
    	);
    GO
    	
    
    CREATE TABLE PATIENT_TESTS(
    	Id	INT	IDENTITY(1,1)	NOT NULL,
    	PatientId	INT	NOT NULL,
    	TestDate	DATETIME	NOT NULL,
    	TestNotes	VARCHAR(MAX)	NULL,
    	TestName	VARCHAR(128)	NOT NULL,
    	PRIMARY KEY (Id),
    	FOREIGN KEY (PatientId) REFERENCES PATIENT(Id) ON DELETE CASCADE
    );
    GO
    
    CREATE TABLE PATIENT_TEST_DETAILS(
    	Id	INT	IDENTITY(1,1)	NOT NULL,
    	TestId	INT	NOT NULL,
    	TestType	VARCHAR(128)	NOT NULL,
    	TestResult	VARCHAR(128)	NOT NULL,
    	TestRange	VARCHAR(128)	NULL,
    	Healthy		BIT		NULL,
    	PRIMARY KEY (Id),
    	FOREIGN KEY (TestId) REFERENCES PATIENT_TESTS(Id) ON DELETE CASCADE
    );
    GO
    
    CREATE TABLE VISIT(
    	Id	INT IDENTITY(1,1)	NOT NULL,
    	PatientId	INT	NOT NULL,
    	VisitDate	DATETIME	NOT NULL,
    	AmountCharged	FLOAT	NOT NULL,
    	DoctorInCharged VARCHAR(64)	NOT NULL,
    	Reason	VARCHAR(MAX)	NULL,
    	CurrentMedication	VARCHAR(MAX)	NULL,
    	Smoke	BIT	NULL,
    	Alcohol	BIT	NULL,
    	Pregnant	BIT	NULL,
    	Height	VARCHAR(64) NULL,
    	Weight	VARCHAR(64)	NULL,
    	Observations	VARCHAR(MAX)	NULL,
    	Indications	VARCHAR(MAX)	NULL,
    	Plans	VARCHAR(MAX)	NULL,
    	DietarySupplements	VARCHAR(512)	NULL,
    	PatientRuns	VARCHAR(512)	NULL,
    	WaterFrequency	VARCHAR(MAX)	NULL,
    	MilkFrequency	VARCHAR(MAX)	NULL,
    	FruitFrequency	VARCHAR(MAX)	NULL,
    	VeggiesFrequency	VARCHAR(MAX)	NULL,
    	SodasFrequency	VARCHAR(MAX)	NULL,
    	CoffeeFrequency	VARCHAR(MAX)	NULL,
    	CerealFrequency	VARCHAR(MAX)	NULL,
    	LegumesFrequency	VARCHAR(MAX)	NULL,
    	MeatFrequency	VARCHAR(MAX)	NULL,
    	FatFrequency	VARCHAR(MAX)	NULL
    	PRIMARY KEY (Id),
    	FOREIGN KEY (PatientId) REFERENCES PATIENT(Id) ON DELETE CASCADE
    	
    );
    GO
    
    
    CREATE TABLE VISIT_TESTS(
    	VisitId	INT	NOT NULL,
    	TestId	INT	NOT NULL,
    	FOREIGN KEY (VisitId) REFERENCES VISIT(Id) ON DELETE CASCADE,
    	FOREIGN KEY (TestId) REFERENCES PATIENT_TESTS(Id) ON DELETE CASCADE
    );
    GO

    I get this error

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

    There are two forms, one will have all the medical tests of a patient. The other will have the associated tests from the selected visit. So if the record is delete from either form it should go away from table 'VISIT_TEST'

    Thanks again for your time.

    Wednesday, July 03, 2013 12:41 AM
  • You are either a bad student or have a truly awful teacher. You have no idea how to use ISO-11179 rules for data element names. You have done no research at all for industry standards. If you were my student, I would ask you to leave the class and change your major.  Yes, this is that bad. 

    The magical Kabbalah number IDENTITY is not RDBMS. You have the most over-sized columns I have seen in 35 years of SQL You have more NULL-able columns in one table than the entire payroll system of a major FORTUNE 500 corporation I worked for. Your table name says you have one Patient! 

    Why do you have both “birth_date” and “age”?  The second column is not just redundant, it is dangerous. How do you plan to keep them in synch?? 

    Did you know the E.123 standard for phone numbers? I cannot think of one industry standard that is VARCHAR(64) or longer, except the IBAN. But in email can  VARCHAR(256). This is all garbage.  And it is so insanely denormalized!! An employee has a relationship with an employer; the emplyer is not an attribute of someone, like his eye color. 

    I would like to find just one thing that correct in this DDL, Just one. But there is nothing. I spent over an hour looking at it. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 03, 2013 1:45 AM
  • Look man,

    I'm just trying to make SQL work for now. I'm in my sophomore year, this project is not due until September, I'm reading ahead in the syllabus, I'm far from expert. And I'm just trying to get a feel of this technology. I encounter this error, and cannot figure out why this is a problem. This is definitely not the final version. We haven't covered any of the ISO-11179 or E.123 standard, hell we haven't event covered queries yet. I've been doing research in "what" are queries and scripts, how do they work, after learning a little about them, I wanted to see a table being created, that's all....

    Wednesday, July 03, 2013 2:26 AM
  • If you search on the error message, you will find many discussions regarding the source of the problem, why it occurs, and how to get around it.  In short, there is a limitation to the use of the cascading delete option.  Is there a better design?  Well - you can't use this one so obviously anything is better.  Those same discussions you find in your search will also have suggestions; the general approach is to not use the cascade option and to delete "from the bottom up".   Just realize that there are some things sql server cannot automatically do for you. 

    Hopefully you've learned from this experience how important it is to post an example that actually demonstrates the problem.  Posting a script that does not generate the error only serves to confuse everyone and, quite often, leads the discussion in a not-so-useful direction.  There are many other issues with your table defintions - one of the more important ones is that you NEVER store a monetary amount in a floating datatype.  Since you're learning, I suggest you stick to more simple models - i.e., "smaller" tables.   

    One last comment.  Always indicate the version and edition of sql server that you are using.  Features vary by version and there is much to be gained by avoiding discussions of features that are not available to you.

    • Marked as answer by raac130 Thursday, July 04, 2013 12:52 AM
    Wednesday, July 03, 2013 8:29 PM
  • Thank you so much Scott.

    That is the constructive criticisms that I was looking for.

    Thursday, July 04, 2013 12:51 AM
  • Regardless of his SQL skills at least he can take solace in the fact he's probably a decent and respectful human being.
    Monday, October 14, 2013 9:43 PM