constraints

[inverse.chi]

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

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.

[r937]

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

[inverse.chi]

Thank you for your feedback - I'll look into InnoDB (I don't know much about DB types)

[IBazz]

Try this.

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

[r937]

should probably add the primary key, and ON DELETE CASCADE to the FKs as well

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 ;

[IBazz]

I was sloppy in my last post. apologies.

bazz

[inverse.chi]

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...

[r937]

that is the short form