SitePoint Sponsor |
|
User Tag List
Results 1 to 8 of 8
Thread: constraints
-
Dec 24, 2008, 10:42 #1
- Join Date
- May 2006
- Location
- Oxford, UK | Durham, UK
- Posts
- 243
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
constraints
Hi,
I was wondering if anyone could give me any advice with using constraints in mySQL. I want to have one table reference another table such that if a row in that table is deleted it deletes all corresponding rows in the other table (am i wrong in believing this is what the ON DELETE CASCADE function does)
Here's what i've got so far, but i dont know if it works
Code:CREATE TABLE StudentMedication ( StudentID INT REFERENCES Student(StudentID) ON DELETE CASCADE ON UPDATE CASCADE, MedicationID INT REFERENCES Medication(MedicationID) ON DELETE CASCADE ON UPDATE CASCADE, Location VARCHAR(100) )
any help/links/advice welcome.
cheers in advanced.Malachi Soord
Something will come here
-
Dec 24, 2008, 11:12 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
what you have is correct but incomplete
- the tables all have to be InnoDB
- foreign keys need to be indexed (except i think they are created "silently" if you fail to declare them)
- the primary or unique keys need to exist before they can be referenced
- column datatypes have to match exactly
-
Dec 24, 2008, 11:18 #3
- Join Date
- May 2006
- Location
- Oxford, UK | Durham, UK
- Posts
- 243
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you for your feedback - I'll look into InnoDB (I don't know much about DB types)
Malachi Soord
Something will come here
-
Dec 25, 2008, 04:26 #4
- Join Date
- Sep 2008
- Posts
- 977
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Try this.
Code mysql:CREATE TABLE IF NOT EXISTS StudentMedication ( StudentID INT NOT NULL , medicationID INT NOT NULL , location varchar(100) , CONSTRAINT medication_fk FOREIGN KEY (medicationID) REFERENCES medication (medicationID) ON UPDATE CASCADE , CONSTRAINT student_fk FOREIGN KEY (StudentID) REFERENCES Student (StudentID) ON UPDATE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=latin1;
hth
bazz
-
Dec 25, 2008, 04:44 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
should probably add the primary key, and ON DELETE CASCADE to the FKs as well
Code:CREATE TABLE StudentMedication ( StudentID INT NOT NULL , CONSTRAINT student_fk FOREIGN KEY (StudentID) REFERENCES Student (StudentID) ON DELETE CASCADE ON UPDATE CASCADE , medicationID INT NOT NULL , CONSTRAINT medication_fk FOREIGN KEY (medicationID) REFERENCES medication (medicationID) ON DELETE CASCADE ON UPDATE CASCADE , PRIMARY KEY ( StudentID , medicationID ) , Location VARCHAR(100) ) ENGINE=INNODB ;
-
Dec 25, 2008, 10:49 #6
- Join Date
- Sep 2008
- Posts
- 977
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I was sloppy in my last post. apologies.
bazz
-
Dec 26, 2008, 08:32 #7
- Join Date
- May 2006
- Location
- Oxford, UK | Durham, UK
- Posts
- 243
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you all for your feedback! Another question regarding this - Is there a shorthand way of doing this? for example something like
StudentID INT REFERENCES Student(StudentID)
ON DELETE CASCADE ON UPDATE CASCADE
Or do i have to use the whole way you described with creating the FK etc...Malachi Soord
Something will come here
-
Dec 26, 2008, 09:04 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
that is the short form
Bookmarks