Consider instead the following design: Notice here that we have eliminated the identity column, and have instead defined a composite (multi-column) primary key as the combination of the Customer ID and Product ID columns.
Therefore, we do not have to create an additional unique constraint.
Let’s also add a simple “Order Limit” column which indicates how many of that product they are allowed to order. I can only surmise that it is because of the lack of understanding what a primary key of a table really is, and that it can be something other than an identity and that it can be comprised of more than just a single column. This table allows me to enter the following data: In the above data, what is the order limit for customer ID #1, product ID #100? Remember, our primary key is just an identity, which does not constrain anything. ) that the data will be always be OK and there will be no duplicates. But even if you think that only one single form on one single application get in and out of your system in different ways.
(This is just a simple example, any attribute will do). For some reason, a very common answer is that we simply create a table with 4 columns: One that stores the Customer ID, one that stores the Product ID we are relating it to, the Order Limit, and of course the primary key column which is an identity: This is what I see in perhaps most of the databases that I’ve worked with over the years. As I mentioned, it seems that many database architects are simply not aware of this fact. What happens if you upgrade your system and have to move the data over?
It's possible to constrain data properly, it's efficient, it's easy to do, and it simply be done or you should not really be working with a database in the first place -- you are forgoing a very important advantage it provides.
So, to handle that issue with this table design, we need create a unique constraint on our Customer ID/Product ID columns: Now, we are guaranteed that there will only be exactly one row per combination of Customer ID and Product ID.
Suppose we have a system that tracks customers, and allows you to assign multiple products to multiple customers to indicate what they are eligible to order.As I often try to do, I will attempt to approach this topic from a slightly different perspective.Let's start things off with what I feel is a good interview question: aware of this.They are define that determine what makes the data in your table unique. It may be an identity column, it may be a varchar column or a datetime column or an integer column, or it may be a combination of multiple columns.When you define more than one column as your primary key on a table, it is called a used them and may not even be aware of them.This is called a relation between Customers and Products.