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

问题描述：

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

Table Class-Details of all classes. Primary Key Class ID
Table Student-Details of all students, Primary key studentID. Foreign Key ClassID
Table Semester-Details of all Semesters, key SemesterID
Table class–Semester. This table solves many to many relation, primary key- IDs of both class and semester. Foreign Key ClassID, SemesterID
Table Subject -Details of all Subjects, key SubjectID
Table class–Subject. This table solves many to many relation, primary key- IDs of both class and semester. Foreign Key ClassID, SubjectID
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;

你好朋友我正在研究一个基于php mysql的学校数据库系统。基本结构如下： p>

表类 - 所有类的详细信息。主键类ID li>
表格学生 - 所有学生的详细信息，主要学生ID。外键ClassID li>
表学期 - 所有学期的详细信息，关键SemesterID li>
表类 - 学期。该表解决了许多关系，类和学期的主键ID。外键ClassID，SemesterID li>
表主题 - 所有主题的详细信息，主题ID li>
表类 - 主题。该表解决了许多关系，类和学期的主键ID。外键IDID，主题ID li>
表标记 - 包括学生ID，主题ID，学期ID，成就标记。外键IDID，SemesterID，SubjectID li> ol>
我还在所有引用父表的表中应用了外键。我希望在我的数据库中应用完整性，以便将特定班级的学生自动分配给该特定班级的科目。 p>

如果我们尝试更改学生的科目，数据库应该抛出这些科目属于学生所属班级的错误。 p>

我确信这可以使用外键约束来完成。但是，我这样做有点天真。非常感谢一个工作示例 p>
```
  ENGINE = InnoDB 
AUTO_INCREMENT = 53 
DEFAULT CHARACTER SET = utf8; 
  code>  pre> 
  div>
```

答

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

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

相关推荐