Primary key purpose
-
I apologize if this isn't the most suitable platform for my question, but I'm frustrated by my inability to effectively express my thoughts and seek guidance on Stack Overflow.
Currently, I'm working on integrating a database system using the QT SQL library, while also attempting to learn and experiment with databases. I have a question regarding the concept of a primary key that has been bothering me for quite some time now:
I've frequently come across programmers stating that a primary key ensures the uniqueness of each tuple (record) within a table. However, I'm struggling to understand the mechanism behind this. What prevents me from doing something like the following:
-- Attempting to insert two identical records into the table. INSERT INTO Workers (first_name, last_name, address, salary) VALUES ('John', 'Doe', '120 Main Street', 50000), ('John', 'Doe', '120 Main Street', 50000); -- This is assuming that I have an auto-incrementing surrogate key as the primary key.If I actually go ahead and print each record:
SELECT * FROM Workers;+------------+-----------+-------+--------+ 1 | John | Doe | 120 Main Street | 50000 | 2 | John | Doe | 120 Main Street | 50000 | +------------+-----------+-------+--------+As far as I can tell, we have 2 records that can be considered a duplicate. The primary key does not seem to be really making anything unique.
-
I apologize if this isn't the most suitable platform for my question, but I'm frustrated by my inability to effectively express my thoughts and seek guidance on Stack Overflow.
Currently, I'm working on integrating a database system using the QT SQL library, while also attempting to learn and experiment with databases. I have a question regarding the concept of a primary key that has been bothering me for quite some time now:
I've frequently come across programmers stating that a primary key ensures the uniqueness of each tuple (record) within a table. However, I'm struggling to understand the mechanism behind this. What prevents me from doing something like the following:
-- Attempting to insert two identical records into the table. INSERT INTO Workers (first_name, last_name, address, salary) VALUES ('John', 'Doe', '120 Main Street', 50000), ('John', 'Doe', '120 Main Street', 50000); -- This is assuming that I have an auto-incrementing surrogate key as the primary key.If I actually go ahead and print each record:
SELECT * FROM Workers;+------------+-----------+-------+--------+ 1 | John | Doe | 120 Main Street | 50000 | 2 | John | Doe | 120 Main Street | 50000 | +------------+-----------+-------+--------+As far as I can tell, we have 2 records that can be considered a duplicate. The primary key does not seem to be really making anything unique.
@Saviz said in Primary key purpose:
The primary key does not seem to be really making anything unique
What is the primary key in Workers table?!
You did not show how that table is defined.
If there is no primary key or the primary key is not a combination of first name and last name then of course you can inserts as many records with same first/last name as you like... -
I apologize if this isn't the most suitable platform for my question, but I'm frustrated by my inability to effectively express my thoughts and seek guidance on Stack Overflow.
Currently, I'm working on integrating a database system using the QT SQL library, while also attempting to learn and experiment with databases. I have a question regarding the concept of a primary key that has been bothering me for quite some time now:
I've frequently come across programmers stating that a primary key ensures the uniqueness of each tuple (record) within a table. However, I'm struggling to understand the mechanism behind this. What prevents me from doing something like the following:
-- Attempting to insert two identical records into the table. INSERT INTO Workers (first_name, last_name, address, salary) VALUES ('John', 'Doe', '120 Main Street', 50000), ('John', 'Doe', '120 Main Street', 50000); -- This is assuming that I have an auto-incrementing surrogate key as the primary key.If I actually go ahead and print each record:
SELECT * FROM Workers;+------------+-----------+-------+--------+ 1 | John | Doe | 120 Main Street | 50000 | 2 | John | Doe | 120 Main Street | 50000 | +------------+-----------+-------+--------+As far as I can tell, we have 2 records that can be considered a duplicate. The primary key does not seem to be really making anything unique.
@Saviz In your table the primary key column, your auto-incrementing surrogate key, does contain unique values. You can update one record independent of the other using that number to identify the record.
If you intended that the combination of first and last name was unique (i.e. a "natural" key) then you need to enforce that either in your code, or with a second unique, compound index on the table, e.g.
create unique index blah on workers(first_name, last_name);(If you use the index be prepared to handle the failure to insert)
If that was your intent then please consider that real world names are generally not unique and this is precisely the reason you want a surrogate key in the first place.
The Qt models and views rely on a single column, numeric primary key to identify records without needing business domain knowledge to determine identity.
