Greetings! How are you, are you well? Today, I will talk about three magical keywords that are essential to databases, almost the foundational stones: PRIMARY KEY, FOREIGN KEY, and UNIQUE. Sometimes you write a program, everything works perfectly, everything is in its place… Then you realize you’ve registered the same user twice! That moment, you think “Oh no!”. These keywords exist precisely to prevent such situations. To me, this trio acts like superhero protectors ensuring both security and correctness of a database.
Imagine a company has a database storing customer information. If you save the same customer twice with different IDs, what happens? Confusion, incorrect reports, perhaps wrong invoices… Such mistakes can cause significant time loss and financial damage. Therefore, understanding and applying these constraints correctly is very important. Of course, there are some technical details involved, but don’t worry, I will explain.
Let’s start with PRIMARY KEY. Think of it like everyone having a national ID number. Each row in a database table should have a unique identifier. PRIMARY KEY is exactly this. It is a column or set of columns that uniquely identifies each record in a table. This key cannot be NULL and cannot be duplicated within the table. If an ID number is used once, you can never use it again for another record. This guarantees that each entry in the database has its own, unique identity. Isn’t that great?
Next, let’s discuss FOREIGN KEY. This is more about relationships. Like when you ask about a friend’s family, FOREIGN KEY establishes relationships between tables. A column in one table referencing a PRIMARY KEY in another table becomes a FOREIGN KEY. For example, suppose you have an ‘Orders’ table and want to specify which customer each order belongs to. In the ‘Customers’ table, each customer has a PRIMARY KEY (e.g., ‘CustomerID’). In the ‘Orders’ table, the ‘CustomerID’ column will be a FOREIGN KEY referencing ‘Customers’ table’s ‘CustomerID’. This way, you can’t add an order with a non-existent customer. If you try to put in a ‘CustomerID’ that doesn’t exist in ‘Customers’, the database will give an error. This maintains data consistency by establishing a logical link between the tables. By the way, I believe this FOREIGN KEY concept is very similar to real-world relationships—trying to put something in its correct place, like in a relation.
And finally, the UNIQUE constraint. It’s similar to PRIMARY KEY but more flexible. A column with a UNIQUE constraint can accept NULL values (usually only one, but it depends on the database system), and the values must be unique. For example, if you set an email address as UNIQUE, you can’t register two users with the same email. But because it’s not a PRIMARY KEY, a NULL value can be repeated in multiple rows (subject to DBMS rules). It doesn’t have to identify each row uniquely, just prevent duplicate entries within that column. For example, a username can be UNIQUE. Everyone should have a unique username, right? It simply prevents data duplication in that column.
By the way, you can define these constraints when creating a new database or adding to an existing table. For example, in PostgreSQL you might create a table like this:
“`sql CREATE TABLE Customers ( CustomerID SERIAL PRIMARY KEY, Name VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Email VARCHAR(100) UNIQUE, CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
CREATE TABLE Orders ( OrderID SERIAL PRIMARY KEY, CustomerID INT, OrderDate DATE, TotalPrice DECIMAL(10, 2), FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); “`
What do we do in this code? We set ‘CustomerID’ in ‘Customers’ as PRIMARY KEY, so each customer has a unique number. We make ‘Email’ UNIQUE, so no two people can register with the same email. In ‘Orders’, ‘OrderID’ is a PRIMARY KEY, and the important part is ‘CustomerID’ which is a FOREIGN KEY referencing ‘Customers’. When you add an order, if you type a ‘CustomerID’ not existing in ‘Customers’, the system will return an error. This is really one of the fundamental ways to protect your data.
Let me share a personal story… When I was in high school, I was creating a simple notebook app. Back then, I didn’t know SQL or these constraints. It stored everything in a plain text file. One day, I remembered my friend’s birthday and added a note titled “Mehmet’s Birthday.” I added it twice, thinking I recorded it wrongly the first time. The result? The file contained two identical lines. Isn’t that a bit absurd? I didn’t realize then how serious this could be, but now I understand that if that simple notebook was a database, things could have gone wrong.
Thanks to these constraints, database management becomes much easier, and data accuracy increases. When designing a database, keeping these three in mind prevents many future problems. For example, think of an e-commerce site selling products. If ‘ProductID’ is a PRIMARY KEY, you can’t add the same product twice. If you categorize products, making ‘CategoryID’ a FOREIGN KEY referencing a categories table ensures you can’t assign a product to a non-existent category. And, setting the product name as UNIQUE prevents duplicate product names. I’m sure sites like Trendyol or Hepsiburada use such constraints. Otherwise, managing thousands of products would be impossible.
In conclusion, PRIMARY KEY, FOREIGN KEY, and UNIQUE are essential in database design. They ensure data integrity, correctness, and consistency. Using these constraints correctly in your projects can save you from numerous future headaches. Pay attention to these trio, and they will always treat you well in the realm of databases 🙂