A primary key constraint and a unique constraint are both meant to enforce uniqueness in column(s). Since that’s true, it begs the question: What’s the difference?
In this very brief tutorial, we’ll discuss the two main differences between primary key constraints and unique constraints.
Difference # 1: Primary key constraints cannot contain NULL values. Unique constraints can contain one NULL value.
It’s a hard rule in SQL Server that primary key columns cannot contain NULL. Take the following example that creates a table with a primary key constraint on a BookID column:
Notice we aren’t outlining the nullability of the BookID column. Because we outlined a primary key constraint on this column, SQL Server automatically labels this column as NOT NULL:
Obviously, it’s impossible to enter NULL into a column that is labeled as NOT NULL!
When it comes to unique constraints, we can have one NULL value (just like we can have only one of any other value).
We’ll add a unique constraint to the Title column (it would indeed be strange to have two books with exactly the same title):
Notice we can add a row with NULL as the Title value:
(By the way, the BookID value is getting populated automatically because of the IDENTITY property established on the column)
We cannot add a second row with a title of NULL:
SQL Server won’t allow it!
Difference # 2: You can only have one primary key constraint on a table. You can have several unique constraints on a table if you want
We cannot add a second primary key constraint on our table. We get an error message if we try to establish a second primary key constraint on the Author column, for example:
But we can absolutely have multiple unique constraints if we want to. We already have a unique constraint on the Title column, and we can add another unique constraint on the Author column if we want to:
We can see both unique constraints in the Object Explorer:
(By the way, I’m not sure why someone would add a unique constraint on the Author column because it’s certainly plausible that an author writes more than one book, but I’m trying to make a point here)
So simple!
Next Steps:
Leave a comment if you found this tutorial helpful!
If you’re just starting out with SQL Server, and want to learn more about all the types of constraints we can create in Microsoft SQL Server, you should definitely check out the following FREE EBOOK:
FREE Ebook on SQL Server Constraints!
This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:
- Primary Key constraints
- Foreign Key constraints
- Default constraints
- Check constraints
- Unique constraints
Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
