Creating Unique Index And Insert Data

31
April 15, 2019, at 12:00 PM

I am making different question regarding on my problem. They suggest me on this thread Best way to avoid duplicate entry into mysql database Thank you for pointing out this thread on me.

Well what i want to happen here is to prevent if the subject is already exist then don't insert.

Don't be so hard on me. I am new to SQL and VB im trying to figure it out all myself i don't know all possible sql query. Thank you.

First this is only a example database.

subject_bsit  Table
--------------------------------------------
| subject_id | subject_name | subject_code  |
---------------------------------------------
|     1      |  ALGEBRA     |     MATH 101  |
|     2      |  ENGLISH     |     ENG101    |
|     3      |  PROGRAMMING |     IT101     |
|     4      |  NETWORKING  |     IT203     |
|     5      |  ALGORITHM   |     IT100     |

   student_subject Table
------------------------------------
| student_id |  subject_id | grade |
--------------------------------------
|    1235    |       1     |  1.25  |
|    1235    |       2     |  2.25  |
|    1235    |       3     |  1.75  |
|    1233    |       1     |  1.5   |

Here is the query that i got. But it seems my query is WRONG ?? I am trying to create a unique index for student id and subject id so i can compare it if it is already exist there or not.

Thank you.

Myquery:

Dim query5 = "ALTER TABLE student_subject ADD UNIQUE INDEX(@student_id,@sub_id) INSERT IGNORE INTO student_subject (student_id, sub_Id, Grade, Enrolled) Values (@student_id, @sub_id, 0, 1),(@student_id,@sub_id)"

If you just want to see my whole code Vb.Net how to check before inserting if the data is already there It is already flag as duplicated.

UPDATE: Query 1

Dim query7 = "ALTER TABLE student_subject ADD CONSTRAINT unq_student_substent UNIQUE (@student_id, @sub_id);"

Query 2

Dim query5 = "INSERT IGNORE INTO student_subject (student_id, sub_Id, Grade, Enrolled) Values (@student_id, @sub_id, 0, 1),(@student_id,@sub_id)"

My Vb Code

Using cn As New MySqlConnection(constring)
            Using cmd As New MySqlCommand(query7, cn)
                cn.Open()
                cmd.ExecuteNonQuery() // i have error here
                Using cmd2 As New MySqlCommand(query5, cn)
                    cmd2.Parameters.Add("@student_id", MySqlDbType.Int32).Value = StudentID
                    cmd2.Parameters.Add("@sub_id", MySqlDbType.Int32).Value = SubjectID
                    cn.Open()
                    cmd2.ExecuteNonQuery()
                End Using
            End Using
        End Using

ERROR CODE:

Fatal error encountered during command execution

Answer 1

I think the syntax you want is two queries:

ALTER TABLE student_subject ADD CONSTRAINT unq_student_substent
    UNIQUE (student_id, sub_id);
INSERT IGNORE INTO student_subject (student_id, sub_Id, Grade, Enrolled)
    Values (@student_id, @sub_id, 0, 1);

The first should be run only once, preferably when the table is created.

READ ALSO
How can I set up password-less in ssh2_connect login?

How can I set up password-less in ssh2_connect login?

I have gone through the post where I can do ssh without giving password [https://askubuntucom/a/296574/905016] But I want to login through ssh2_connect in php, where I need to give password detail

51
search nested array of objects and return full parents as results | javascript

search nested array of objects and return full parents as results | javascript

Hello I need a bit of help finishing up my functionI've successfully written a recursive function to loop through a nested object and find the results

45
Android GridLayout with dynamic number of columns per row

Android GridLayout with dynamic number of columns per row

I have cards with different width and I would like to create a Grid Layout where width = match the parent (filling the screen) and fixed heightI would like to set the cards in the Grid layout so the number of columns changes accordingly to the width of the elements...

19