CREATE TABLE & USE PRIMARY KEY AND UNIQUE KEY IN MYSQL

  #mysql -u db_user -p 

mysql>show database;

 +---------------+
| database()    |
+---------------+
| Student        |
+---------------+
1 row in set (0.00 sec)

mysql>use Student;

NOW CREATE TABLES IN MYSQL 

mysql> CREATE TABLE STUDENT   ( Stud_UID int  AUTO INCREMENT PRIMARY KEY,Stud_Name VARCHAR(255) NOT NULL ,DOB DATE NOT NULL ,DOA DATE NOT NULL ,Stud_Father_name VARCHAR(255) NOT NULL ,Stud_Mother VARCHAR(255) NOT NULL );

 

CREATE TABLE WITH PRIMARY KEY :

mysql>CREATE TABLE STATION ( ID INT (10) NOT NULL AUTO INCREMENT ,S_ID INT (10) NOT NULL ,D_DATE  TIMESTAMP NOT NULL DEFAULT '0000:00:00 00:00:00' ,T_MAX DOUBLE (10,2) NOT NULL ,T_AVG DOUBLE (10,2),T_MIN NOT NULL  , PRIMARY KEY (ID) ,UNIQUE KEY  S_ID_D_DATE (ID,D_DATE) );    

 Use

 Primary key : Unique value insert into table  

 Unique Key : I want to use more unique row in table so we use unique key .

Example :

mysql>CREATE TABLE STUDENT (ID INT AUTO_INCREMENT ,DOB date ,STUDENT_NAME VARCHAR(10),PRIMARY KEY (ID),UNIQUE KEY IDDOB (ID,DOB));

mysql> INSERT INTO STUDENT VALUE (1,'2020-01-01','A') ;

mysql> select * from STUDENT ;
 

 Output :

# ID, DOB, STUDENT_NAME
  '1', '2020-01-01', 'A'

Again Enter same row  :
 

mysql> INSERT INTO STUDENT VALUE (1,'2020-01-01','A') ;

 Error Code: 1062. Duplicate entry '1' for key 'PRIMARY'    0.036 sec