mysqlprimary-keyrdbmsalter-tableunique-key

ADD COLUMN with CONSTRAINT

I have a table name Stu_d. In this, I have created 3 columns {Stu_id INT PRIMARY KEY, Name VARCHAR(20), Roll_no INT(5)}. I need to add 1 more column with the name of Mobile_no with INT data type and have a constraint of UNIQUE KEY. With the help of ALTER TABLE.

I have tried this syntax:-

ALTER TABLE table_name ADD COLUMN column_name datatype(size), ADD constraint_name column_name;

ALTER TABLE Stu_d ADD COLUMN Mobile_no INT(10), ADD UNIQUE KEY (Mobile_no);

ERROR 1072 (42000): Key column 'Moblie_no' doesn't exist in table

Then i try :-

 ALTER TABLE Stu_d
 ADD COLUMN Mobile_no INT(10),
 UNIQUE KEY (Moblie_no);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNIQUE KEY (Moblie_no)' at line 3

Solution

  • The syntax for this is

    ALTER TABLE Stu_d ADD COLUMN Mobile_no INT(10) UNIQUE ;

    if you want to name the constraint then use

    ALTER TABLE Stu_d ADD COLUMN Mobile_no INT(10) ;
ALTER TABLE Stu_d ADD CONSTRAINT uq_Mobile_no UNIQUE (Mobile_No);