What is the difference between primary key and unique key

Unique key :- It should be used when you have to give unique value. In the case of unique key it means null values are also allowed. Unique keys are those keys which are unique and non similar in that column like for example your pet name. Both Primary key and Unique Key are used to uniquely define of a row in a table.

Primary Key creates a clustered index of the column whereas a Unique creates an unclustered index of the column. The primary key provides a means to identity the row, using data within the row.

A primary key can be based on one or more columns, such as first and last name; however, in many designs, the primary key is an auto-generated number from an identity column. A unique key is also called a unique constraint. A unique constraint can be used to ensure rows are unique within the database. Yep, we do, but a table may have several sets of columns which you want unique. It must contain a unique value for each row of data.

It cannot contain null values. Only one Primary key in a table. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?

Collectives on Stack Overflow. Learn more. Asked 9 years, 8 months ago. Active 1 month ago. Viewed k times. I'm using mysql database. I have a confusion between primary key and unique key.

Vikas Katariya 5, 3 3 gold badges 9 9 silver badges 30 30 bronze badges. Anuj Anuj 2, 3 3 gold badges 12 12 silver badges 10 10 bronze badges. Take a look at dba. And stackoverflow. Add a comment. Active Oldest Votes. KB Mr. KB 2, 1 1 gold badge 12 12 silver badges 2 2 bronze badges. Also want to add on that primary key can be created on multiple columns, e. This is called composite primary key.

What is your mean of 'can be a candidate key'? Unique key can be null and may not be unique Means?? PratikCJoshi He probably means that the can be multiple rows with null on the otherwise unique key. Show 8 more comments. Then how can there be one and only one PK on a table?

Source: postgresqltutorial. Okay, it's not a column. I misread. It's a contraint, not a column. There's still the clustered index, but it's over two columns instead of one. And each column in it is not a primary key on its own, instead the whole set is a primary key. So there are not more than one PK in these instances.

Difference between Primary Key and Unique key 1. To do this, execute the following script:. In the script above, we set primary key constraints on the ID and Model columns. However, since only one column in a table can have a primary key constraint, you will see the following error:. Take a look at the following script:. Since a table can have multiple columns with unique key constraints, you will not see any error messages when executing the above script.

Look at the script below:. You can see that this script inserts null as a value into the ID column. When you execute it, you should see the following error in the output message window:. You will see that the above script will execute be executed without errors since the unique key column can accept null values by default. Remember, you cannot add more than one null value to a unique key column since the second null value will be the duplicate of the first one — and duplicates are not allowed.

Despite a few basic similarities, primary and unique keys have significant differences. You can only have one primary key per table, but multiple unique keys. And finally, the primary key column has a unique clustered index while a unique key column has a unique non-clustered index. CodingSight is open for new authors and partnership proposals. Article Contributed By :. Easy Normal Medium Hard Expert.

Writing code in comment? Please use ide. Load Comments. What's New. Most popular in DBMS. Most visited in Difference Between. We use cookies to ensure you have the best browsing experience on our website.