FEEL FREE TO SHARE TUTORIALS, YOUR SKILS & KNOWLEDGE ON CODING, SCRIPTS, THEMES, PLUGINS OR ANY RESOURCES YOU HAVE WITH THE COMMUNITY- Click Here To Post Your Request,
JOIN COMPUTER REPAIR FORUM

Reply to thread

Message: <blockquote data-quote="Code55" data-source="post: 25" data-attributes="member: 14">In this article we look into the difference between Primary Key and Unique Key Constraints. We will see the description of both along with how to define a schema and create attributes in a table with these constraints.<h2>Primary Key</h2>A Primary Key is a key attribute or a column which is used to uniquely identify a row (tuple) in a table (relation). It maintains the integrity constraints of a table. A table can have only one primary key attribute. The data of primary key must be unique. Hence, no duplicate values are valid. Also, no NULL (empty) values are allowed during insertion as it violates the integrity constraint. The database automatically creates a Clustered Index over primary key which orders data on the basis of primary key.We define a Primary Key Attribute as follows:Column_Name DataType PRIMARY KEY<h2>Unique Key</h2>A Unique Key is a constraint or key attribute which also uniquely identifies a row in a table. It’s purpose is somewhat same as primary key. It differs in ways such as a table can have one or more than one Unique keys. It allows the possibility of NULL value insertion limited to only one null value insertion. In other words, Unique Key Constraints allow a column to have only one NULL value. No duplicate values are permitted. By default, the Unique key are generated in a Non-Clustered Index.We define a Unique Key Attribute as follows while creating a table:Column_Name Datatype UNIQUENow let us look at an example of creating a table with these constraints:CREATE TABLE CARS(Car_Id INT PRIMARY KEY,Car_Brand VARCHAR(10),Car_NumberPlate VARCHAR(10) UNIQUE);Here Car_Id is the Primary key and Car_NumberPlate is the Unique Key of table CARS.Let us assume we have these records in table CARS:<img src="https://www.thecrazyprogrammer.com/wp-content/uploads/2021/02/Screenshot-27.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />If we add a record like this:INSERT INTO CARS VALUES(3,'Hyundai','');This will add the record successfully since the unique key Car_NumberPlate allows one null value and show contents of database:<img src="https://www.thecrazyprogrammer.com/wp-content/uploads/2021/02/Screenshot-30.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />If we add any duplicate value in Primary key field Car_Id, it will show us errorINSERT INTO CARS VALUES(3,'Honda','TN056789');<img src="https://www.thecrazyprogrammer.com/wp-content/uploads/2021/02/Differencebetweenprimarykeyandunique.png" alt="" class="fr-fic fr-dii fr-draggable " style="" />So, when we try entering duplicate values to our Primary key Car_Id it shows the Unique constraint of the field is violated.<h2>Difference between Primary Key and Unique Key</h2>Now let’s look at the key differences between them:<table style='width: 100%'><tr><td>Primary Key </td><td>Unique Key </td></tr><tr><td>1. It is the SQL constraint to uniquely identify rows of a table.</td><td>1. It is a unique identifier for rows when primary key is not present.</td></tr><tr><td>2. Primary Key does not allow NULL value insertion.</td><td>2. It allows one NULL value insertion.</td></tr><tr><td>3. Only one Primary Key can be present in a table.</td><td>3.There can be one or more than one Unique keys in a table.</td></tr><tr><td>4.  The main purpose is to enforce entity integrity.</td><td>4. The main purpose is to enforce uniqueness of entity data.</td></tr><tr><td>5. By default, the primary key column has Unique Clustered Index.</td><td>5. The unique key column has Non-Clustered Index.</td></tr><tr><td>6. It is useful in creating relationships between other tables.</td><td>6. It is useful for Data Validation.</td></tr></table>That’s all for the article you can create a table like the example mentioned above, add some records and see it in working!Feel free to ask your doubts in the comments section.</blockquote>

Verification

Richest Freecoded User

Most Freecoin

freecoded

4,876 Freecoin

Johnhendrick

645 Freecoin

Smith16

607 Freecoin

Davy200

590 Freecoin

nathan69

426 Freecoin

Laureine

415 Freecoin

anajeen

395 Freecoin

Peterparker87

331 Freecoin

codeguru

282 Freecoin

Tekera

267 Freecoin