Oracle SQL Developer - 添加外键约束

Oracle SQL Developer - Adding Constraints on Foreign Key

提问by goodgirlatx347

I'm new to Oracle SQL and I am having a hard time adding a constraint. I am trying to add constraints on my table to enforce specific business rules that only allows students only to take 4 courses and a max of 25 students per class.

我是 Oracle SQL 的新手，我很难添加约束。我试图在我的表上添加约束以强制执行特定的业务规则，该规则只允许学生只上 4 门课程，每班最多 25 名学生。

Please let me know what additional information you need from me to help answer this question. I am at a loss...

请让我知道您需要我提供哪些其他信息来帮助回答此问题。我很茫然...

CREATE TABLE GRADES
(STU_ID       int NOT NULL ENABLE,
 CRSE_ID        CHAR(9) NOT NULL ENABLE,
 STU_CRSE_GRADE VARCHAR2(20) 
 check(STU_CRSE_GRADE='A' or 
       STU_CRSE_GRADE='B' or 
       STU_CRSE_GRADE='C' or 
       STU_CRSE_GRADE ='D' or 
       STU_CRSE_GRADE= 'F'),
 CONSTRAINT GRADES_PK PRIMARY KEY (STU_ID, CRSE_ID),
 constraint fk_Grades Foreign key(Stu_ID)
   REFERENCES Students,
 constraint fk_Grades_Crse_ID foreign key(Crse_ID)
   REFERENCES Courses
);

No problem! See tables below:

没问题！见下表：

CREATE TABLE Students
(Stu_ID int Constraint pk_Stu_ID Primary Key,
Stu_name VARCHAR(255) NOT NULL, Stu_Add varchar(255),
Stu_Maj CHAR(6)
);

CREATE TABLE Instructors
(Instr_ID char(3) Constraint pk_Instr_ID Primary Key,
Instr_Name VARCHAR(255) NOT NULL, Instr_Office varchar(8)
);

CREATE TABLE Courses
(Crse_ID char(9) Constraint pk_Crse_ID Primary Key,
Crse_Title VARCHAR(255) NOT NULL,
Student's name: Lai Xia
Instr_ID CHAR(3) not null,
constraint fk_Courses_Instr_ID Foreign key(Instr_ID) REFERENCES Instructors
);

回答by Branko Dimitrijevic

The foreign key alonecan only represents a one-to-many relationship. If you want to limit the "many" portion to a specific number, you'll need to either:

单独的外键只能代表一对多的关系。如果要将“许多”部分限制为特定数字，则需要：

1. Do it in the application code or triggers.
2. Or redesign the rest of the database to "help" the FK in achieving that goal.

1. 在应用程序代码或触发器中执行此操作。
2. 或者重新设计数据库的其余部分以“帮助”FK 实现该目标。

---

---

The (1) is easy to implement but easy to get wrong: you'll have to carefully employ locking to avoid race conditions in the concurrentenvironment:

(1) 易于实现但容易出错：您必须小心地使用锁定以避免并发环境中的竞争条件：

• Let's say two concurrent transactions are attempting to connect the same student to a different course.
• The first transaction counts the courses currently connected to the student and finds that there are 3 of them. All good and well.
• The second transaction does the same and also sees only 3 courses (because the first transaction hasn't committed yet).
• So both transactions think they won't exceed the allowed number and bothhappily proceed with inserting their student-course connection.
• End result: the student is connected to 5 courses, violating the rule that it can be connected to only 4.

• 假设两个并发事务试图将同一个学生连接到不同的课程。
• 第一个事务统计当前连接到学生的课程，发现有 3 个。一切都很好。
• 第二个事务做同样的事情，也只看到 3 个课程（因为第一个事务还没有提交）。
• 因此，两个事务都认为它们不会超过允许的数量，并且都愉快地继续插入他们的学生课程连接。
• 最终结果：学生连接到 5 个课程，违反了只能连接到 4 个的规则。

To avoid that, you'll need to serializethese operations, probably by locking the student through SELECT ... FOR UPDATE.

为了避免这种情况，您需要序列化这些操作，可能是通过SELECT ... FOR UPDATE.

---

---

The (2) could be implemented by changing the key design, and then limiting the values a key can have. For example, enforcing that a student can take at most 4 courses can be done like this:

(2) 可以通过更改密钥设计来实现，然后限制密钥可以具有的值。例如，强制一个学生最多可以参加 4 门课程，可以这样做：

CREATE TABLE STUDENT (
    STUDENT_ID INT PRIMARY KEY
);

CREATE TABLE COURSE (
    COURSE_ID INT PRIMARY KEY
);

CREATE TABLE STUDENT_COURSE (
    STUDENT_ID INT REFERENCES STUDENT,
    COURSE_ID INT REFERENCES COURSE,
    COURSE_NO INT NOT NULL CHECK (COURSE_NO IN (1, 2, 3, 4)),
    PRIMARY KEY (STUDENT_ID, COURSE_ID),
    UNIQUE (STUDENT_ID, COURSE_NO)
);

The combination of CHECK and UNIQUE constraints means the DBMS itself will refuse to connect the same student to more than 4 courses.

CHECK 和 UNIQUE 约束的组合意味着 DBMS 本身将拒绝将同一个学生连接到 4 门以上的课程。

Doing this will succeed:

这样做会成功：

INSERT INTO STUDENT_COURSE VALUES (11, 111, 1);
INSERT INTO STUDENT_COURSE VALUES (11, 222, 2);
INSERT INTO STUDENT_COURSE VALUES (11, 333, 3);
INSERT INTO STUDENT_COURSE VALUES (11, 444, 4);

But doing this obviously won't (CHECK constraint violation):

但这样做显然不会（检查约束违规）：

INSERT INTO STUDENT_COURSE VALUES (11, 555, 5);

BTW, when student is already connected to some courses and you want to find the remaining free "slots", you can do it like this:

顺便说一句，当学生已经连接到某些课程并且您想找到剩余的免费“插槽”时，您可以这样做：

SELECT NEW_NO
FROM (
    SELECT
        COURSE_NO + 1 NEW_NO,
        LEAD (COURSE_NO) OVER (ORDER BY COURSE_NO) NEXT_NO
    FROM STUDENT_COURSE
    WHERE STUDENT_ID = 11
)
WHERE NEW_NO <> NEXT_NO OR NEXT_NO IS NULL;

[SQL Fiddle]

[SQL 小提琴]

回答by vav

Looks like you already learned about basic constraints in oracle (like check and foreign key). For the more complicated scenarious you may use triggers. Triggers are executed on set of events (when you try to insert, update or delete a record into table).

看起来您已经了解了 oracle 中的基本约束（例如检查和外键）。对于更复杂的场景，您可以使用触发器。触发器在一组事件上执行（当您尝试向表中插入、更新或删除记录时）。

I assume that student takes only 4 courses mean that every student could have only 4 records in grades table. In trigger you can do exactly that. So you create a trigger before (there is also after ) insert or update, inside a trigger you check how many records student from inserting line already have and accept or decline particular insert/update operation.

我假设学生只上 4 门课程意味着每个学生在成绩表中只能有 4 条记录。在触发器中，您可以做到这一点。因此，您在插入或更新之前（也有之后）创建了一个触发器，在触发器内部，您检查插入行中已经有多少条记录并接受或拒绝特定的插入/更新操作。

Than you can write similar trigger for 25 students in a course. It will be another trigger on the same insert/update into grates table.

您可以为一门课程中的 25 名学生编写类似的触发器。这将是同一插入/更新到 grates 表的另一个触发器。

For exact syntax try documnentation first, triggers are fun :)

对于确切的语法首先尝试文档，触发器很有趣:)

Also, later you may consider writing stored procedures as a more advanced way of implementing business logic in database. good luck!

此外，稍后您可能会考虑编写存储过程作为在数据库中实现业务逻辑的更高级方法。祝你好运！