CODEWITHSUNDEEP
mysqlsqldatabase
Jahidul Islam
Jahidul Islam

Reputation: 119

Is there a way to insert more than one data in a field in SQL database?

I want to insert more than one course in a field of per student_id under course_taken column where each value has separate by a ','.

+-------------------------------------------+
| student_id |         course_taken         |
+-------------------------------------------+
|   133072   | CSE 1101, CSE 1102, CSE 1103 |
+-------------------------------------------+

Is it possible?

Upvotes: 0

Views: 108

Answers (1)

Boris Schegolev
Boris Schegolev

Reputation: 3701

course_taken should be an M:N relation between student and course. It is implemented with a separate table in relational databases, not inline data.

Simply put:

CREATE TABLE course (
    id int NOT NULL PRIMARY KEY autoincrement,
    course_code varchar NOT NULL
);
CREATE TABLE student (
    id int NOT NULL PRIMARY KEY autoincrement,
    full_name varchar NOT NULL
);
CREATE TABLE course_taken (
    course_id int NOT NULL,
    student_id int NOT NULL,
    CONSTRAINT pk_course_taken
        PRIMARY KEY (course_id, student_id),
    CONSTRAINT fk_course_taken_course_id 
        FOREIGN KEY (course_id)
        REFERENCES course (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_course_taken_student_id 
        FOREIGN KEY (student_id)
        REFERENCES student (id)
        ON DELETE CASCADE
);

To answer your question, yes, you can store such data inline in some text field. But you really should not.

Upvotes: 4

Related Questions