how can you apply primary keys in multiple columns
It was said in the video that the similarity between the two types (primary and unique) of constraints is that both can be applied to multiple columns, not just a single column. So how do you apply a primary key if there can be only one primary key in a table?
Hi Edu!
Thanks for reaching out.
There's the possibility to create a composite primary key, where not just a single column, but multiple columns define the uniqueness of the primary key.
For instance, if a single column determines the primary key, all values in the column must be different.
E.g. (id_001), (id_002) etc.
Otherwise, if you are using a composite key, the id column may be combined with, e.g. the date column, so the primary keys become of the following type:
(id_001, '2021-10-24'), (id_001, '2021-10-25'), (id_002, '2021-10-24'), (id_002, '2021-10-30') etc.
Hope this helps.
Best,
Martin
Hello,
how do we combine two columns to primary key ? Is there a specific format to define composite primary key?
tx
Hi Anuradha!
Thanks for reaching out.
When you define your table the syntax for a composite primary key is the following:
PRIMARY KEY (column_1, column_2, ...)
Hope this helps.
Best,
Tsvetelin
On which situation or circumstances do we consider having two primary keys. Explain with instances. Thanks
Hi RACHEAL!
Thanks for reaching out.
I think the follownig example could help you understand the concept.
Imagine you stored the data about each sale in a separate table called 'sales'. Amongst the fields, you'll have customerID (representing the person who executed the purchase), dateTime (to show the exact moment, in terms of data, hour, minute and seconds when the purchase was executed). Suppose also that we are talking about a huge supermarket chain that has tens of premises and hundreds of cash registers; to say that it is possible to register several purchases at the same moment in time.
Therefore, since a customer can only be at one cash register at a certain point in time, considering that they (hopefully for the supermarket chain) goes there often to do their groceries, in the table in your database it would be worthy to set a primary key composed of both the customerID and the dateTime value. Not just one or the other. Thus, you will be sure that in your 'sales' table, you will have unique records, identified by the customer ID and the moment of purchase.
Hope this helps.
Kind regards,
Martin