SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: constraints

  1. #1
    SitePoint Addict inverse.chi's Avatar
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict inverse.chi's Avatar
    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

  4. #4
    SitePoint Guru
    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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 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 ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was sloppy in my last post. apologies.

    bazz

  7. #7
    SitePoint Addict inverse.chi's Avatar
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that is the short form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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