mysql RDBMS，在许多表中应用外键约束

Question

Hello friends I am working on a school database system based on php mysql. the basic structure is as below:

1. Table Class-Details of all classes. Primary Key Class ID
2. Table Student-Details of all students, Primary key studentID. Foreign Key ClassID
3. Table Semester-Details of all Semesters, key SemesterID
4. Table class–Semester. This table solves many to many relation, primary key- IDs of both class and semester. Foreign Key ClassID, SemesterID
5. Table Subject -Details of all Subjects, key SubjectID
6. Table class–Subject. This table solves many to many relation, primary key- IDs of both class and semester. Foreign Key ClassID, SubjectID
7. Table marks- consists of student ID, Subject ID, Semester ID, Marks Achieved.Foreign Key ClassID, SemesterID, SubjectID

I have also applied foreign keys in all the tables which are referring back to the parent table. I am looking to apply integrity in my database so that a student for a particular class will automatically be assigned to subjects of that particular class.

If we try to change the subjects of the student, database should throw an error that these subjects belong to the class for which student is a part of.

I am sure this can be done using foreign key constraints. However, I am bit naive to do so. A working example is highly appreciated

ENGINE = InnoDB
AUTO_INCREMENT = 53
DEFAULT CHARACTER SET = utf8;

Answer 1

Ok, I'll try to help. :-) First make sure you know the syntax completely by using the MySQL Manual for creating tables.

MySQL 5.1: CREATE TABLE

Look for the sections that look like this.

reference_definition:

REFERENCES tbl_name (index_col_name,...)
  [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
  [ON DELETE reference_option]    <----
  [ON UPDATE reference_option]    <----

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

Here is an example (...attept ...) from a child table of contact statistics that links to a contacts (people) parent table.

CREATE TABLE IF NOT EXISTS contactStats_tbl(
id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Contact ID number.',
email VARCHAR(254) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'E-mail address from contacts_tbl.',
subscribeTime TIMESTAMP DEFAULT '0000-00-00 00:00:00' COMMENT 'Time of subscription.',
unsubscribeTime TIMESTAMP DEFAULT '0000-00-00 00:00:00' COMMENT 'Time of unsubscription.',
totalMessages INT(4) NOT NULL COMMENT 'Number of messages sent.',
newsLetter ENUM('Y', 'N') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N' COMMENT 'Newsletter subscription.',
CONSTRAINT csconstr01 FOREIGN KEY (id, email) REFERENCES contacts_db.contacts_tbl(id, email) ON UPDATE CASCADE ON DELETE RESTRICT)
ENGINE=InnoDB DEFAULT CHARACTER SET = utf8 COMMENT 'Contact statistics table.';

Essentially, with table constraints you are focusing on a time when someone attempts to DELETE or UPDATE a record in a child table containing fields that point to a parent table (foreign keys, in this case). For all of your child tables, my advice would be to set the ON DELETE options to RESTRICT (the default). But, for ON UPDATE, child tables should probably CASCADE to keep them consistent with their parents (I have not researched referential integrity for a while, but I think that's how it goes! Dang that MS Access! Don't vote me down if I am wrong. Just comment and I'll fix my answer. :-)). The best thing to do would be to make sure you know how referential integrity applies to the situation at hand. Truthfully, I forget how the ON UPDATE bit works because I have not used it in a while. :-)

Now, as far as automatically inserting field values into a record (in a secondary table) based on actively inserting a record into some other table (primary table), make sure that you are not in need of a trigger.

MySQL 5.1: CREATE TRIGGER

This should get you going. I tried! :-)

Anthony