MySQL Problem 2: a. Create an index with UNIQUE option on SNO of STUDENT. Show t

Question

MySQL

Problem 2:

a. Create an index with UNIQUE option on SNO of STUDENT. Show that you successfully created the index.

b. This time try to create an index with UNIQUE option on ADDRESS of STUDENT. Discuss your result of this attempt..

Here is the table schema:

CNAME 21 Computer Science 17 Chemist 15 PhysicS 12 Mathematics l 13 Mathematics III RESULT CNO CNO STUDENT SNO SNAME ADDRESS SEX SCORE 31 L. Gatlin Holcomb F 11 N. Park Whitney M 22 S. Conner: Holcomb F 24 D. Davissc E. Quad M 19 1. Blake Holcomb M 4 T. Smith Cambridg F SNO 2 2 2 2 2 2 31 14 2 63 4 Histo 3 Geography 2 Biology 72 1 N. J. Sloan Whitney F 14 D. E. Knutl Whitney M 6 J. Brown Whitney 2 M. Robert Cambridg M 14 33 U. Smith Cambridg F 14 37 2 13 13 13 13 97 72 11 NULL 14 61 52 63

Explanation / Answer

syntax for creating unique index: CREATE UNIQUE  INDEX index_name ON tabel_name (coulmn_name);

solution (a):

CREATE UNIQUE INDEX idx_STUDENT _SNO ON STUDENT (SNO ASC);

This statement will successfully create an index of student table on SNO column with name idx_STUDENT _SNO, and the key SNO will be in ascending order in the index table.

solution (b):

CREATE UNIQUE  INDEX idx_STUDENT_ADDRESS ON STUDENT (ADDRESS);

When we will execute this statement in order to create an index on ADDRESS column, it throws an error message like ERROR 1061 (42000): Duplicate key name.

Hence a unique index cannot be created on an existing table, if the table contains duplicate values in the key column(column that we are using to create index).

In order to solve this issue we have several options available:

1. Delete or update the duplicate values.

2. Replace the key column from the index definition with a column_name that doesn't have duplicate value.

3. We can take composite key column (combination of 2 or more columns) to create an index.

syntax for composite column:  CREATE UNIQUE  INDEX index_name ON tabel_name (coulmn1, column2, ....);

CREATE UNIQUE INDEX idx_STUDENT ON STUDENT (SNO, ADDRESS);

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.