How do I insert values for a column that has multiple values?

Question

In a university database, I have a self referencing relationship for a subject and a pre-requisite. Meaning that a subject can have 0 or more pre-requisites.

So I declared it in a table for subjects

 subject_code VARCHAR(7) NOT NULL CONSTRAINT subject_pk PRIMARY KEY,
 subject_name VARCHAR(50) NOT NULL,
 pre_requisite VARCHAR(7) NULL CONSTRAINT unit_pre_code FOREIGN KEY 
               REFERENCES subject(subject_code),

So I am just wondering if I am right heading this way or should there be another table that deals with pre-requisites.

If I am in the right track, how do I insert a data that has a pre-requisite for the subject? for example C++(C12345) subject is a prerequisite to Operating Systems(C34512) or something. I am still really new to this and I'm having a hard time looking for good and simple references for SQL. Any recommendations would be great too!

If you have multiple values you should add another values tables for them. — YvesR
– YvesR, Commented May 29, 2012 at 9:23
I would be careful to avoid circular dependency, i.e. referential integrity loops. 101 -> 201 - 301 -> 101 ... although it is not logical, it is not prevented by the table definition. — Jesse Harris
– Jesse Harris, Commented May 29, 2012 at 9:25

GarethD · Accepted Answer · 2012-05-29 11:20:09Z

Your relationship is one to many i.e. 1 subject can have many prerequisites, you should therefore be using another table. e.g.

CREATE TABLE SubjectPrerequisite
(       Primary_Subject_Code        VARCHAR(7) NOT NULL,
        Prerequisite_Subject_Code   VARCHAR(7) NOT NULL,
    CONSTRAINT PK_SubjectPrerequisite PRIMARY KEY (Primary_Subject_Code, Prerequisite_Subject_Code),
    CONSTRAINT FK_SubjectPrerequisite_Primary_Subject_Code FOREIGN KEY (Primary_Subject_Code) REFERENCES Subject (Subject_Code),
    CONSTRAINT FK_SubjectPrerequisite_Prerequisite_Subject_Code FOREIGN KEY (Prerequisite_Subject_Code) REFERENCES Subject (Subject_Code)
)

This will still maintain your referential integrity by having all the correct keys, but will make querying the database much easier where 1 subject has multiple prerequisites. e.g.

-- WILL RETURN ALL SUBJECTS AVAILABLE GIVEN A CERTAIN PERSONS
-- COMPLETED SUBJECTS.
SELECT  DISTINCT s.*
FROM    Subject s
        INNER JOIN SubjectPrerequisite sp
            ON s.Subject_Code = sp.Primary_Subject_Code
WHERE   sp.Prerequisite_Subject_Code IN ('C12345', 'C12346') 

-- WILL RETURN ALL PRERQUISITE SUBJECTS FOR ANY GIVEN SUBJECT
SELECT  s.*
FROM    SubjectPrerequisite sp
        INNER JOIN Subject s
            ON s.Subject_Code = sp.Prerequisite_Subject_Code
WHERE   sp.Primary_Subject_Code = 'C34512'

Collectives™ on Stack Overflow

How do I insert values for a column that has multiple values?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related