UNIQUE

Until this point, we have two tables "students" and "games" in our database sql_practice. In the table students, we can have many students with the same name, either first name or last name or both. To prevent such case, we created another column "student_id". Thus, we know that student_id should be unique to each students. To prevent user from inserting duplicate values into this column, we need to provide a constraint. The UNIQUE constraint is used in such situation. It makes sure that all the values that are inserted into a column are unique or different. 


When Creating Table, we use following syntax to create a UNIQUE constraint on "student_id"

CREATE TABLE students (

student_id INT NOT NULL,

   first_name VARCHAR(255) NOT NULL,

   last_name VARCHAR(255) NOT NULL,

   email varchar(255)

UNIQUE (student_id)

);

This will prevent having duplicate records in the column student_id. 

What if the table is already created? Like in our case, we already have the table students and we have not set the column any UNIQUE constraints. In this situation, we can use ALTER TABLE statement as below

ALTER TABLE students

ADD UNIQUE(student_id);

Lets see what error we get if we try to insert new data in the column student_id with duplicate id of 3...