MySQL PRIMARY Key

MySQL | PRIMARY Key: Learn about the MySQL PRIMARY Key, with its explanation, syntax, and query examples.
Submitted by Apurva Mathur, on September 15, 2022

PRIMARY Key

The PRIMARY key is the key that makes the record uniquely identified. If any column in the table is marked as a PRIMARY key then that column cannot contain two same values also it cannot be null. Usually, PRIMARY keys are also AI, i.e., auto-incremented, which means once initiated you don’t need to give value to the column, and it will automatically increment the number. Just like we increase the serial number, once our row is completed.

Similarly, when any key has been marked as primary key and also AI that means it will repeatedly increment and all the values will be unique.

It is important to know that, every table has one PRIMARY key, (usually IDs are kept as PRIMARY key). A single table cannot have more than 1 PRIMARY key; if you will try to implement this it will show you the error.

The PRIMARY key has always a number as its value, which means its data type would be INT or BIGINT. The PRIMARY key does not contain characters.

Let us see how we to apply the PRIMARY key constraint:


Suppose we are asked to create a table named "student_details" and inside this table, we have to make 6 columns named as, student_ID (this should be PRIMARY key as well as Auto incremented), student_name, student_department, year, gender, and marks.

So to make any column as the PRIMARY key we follow the syntax given below;

PRIMARY Key Syntax

CREATE TABLE statement table_name (
  Field1_name datatype PRIMARY KEY,
  Field2_name datatype, 
  ...
);

Now let us see how we will write the query in the situation given,

CREATE TABLE student_details(
  student_ID INT AUTO_INCREMENT PRIMARY KEY, 
  student_name VARCHAR(45) NULL, 
  student_department VARCHAR(45) NULL, 
  year INT NULL, 
  gender VARCHAR(45) NULL, 
  marks VARCHAR(45) NULL
);

This command will create a table in which "student_id" will be the PRIMARY key and AI.

Now, let us insert the value inside this table,

INSERT INTO student_details (`student_ID`, `student_name`, `student_department`, `year`, `gender`, `marks`) 
VALUES ('1', 'Rihan Jha', 'CSE', '1', 'Male', '99');

The data is inserted successfully.

Apply this PRIMARY key and AI constraints in MYSQL Workbench

Step 1: Select the schemas, in which you want to create your table,

Primary Key (Step 1)

Step 2: After selecting the schema, Go to tables, and click on CREATE TABLE.

Primary Key (Step 2)

Step 3: After clicking on create a table, give table name and column names,

Primary Key (Step 3)

Step 4: Then select the right-side check boxes, Here I am making student_ID as my PRIMARY key and also AI, so I will click on PK and AI,

Primary Key (Step 4)

Step 5: After step 4, just click on apply and done.

Your table is created which has a PRIMARY key.

NOTE: Always make your PRIMARY key auto-incremented, else you will not be allowed to edit your values.


ADVERTISEMENT
ADVERTISEMENT




Comments and Discussions!




Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.