Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Synopsis: ID Required

Explore the importance of primary keys in preventing duplicate rows and ensuring unique identification in your database tables. Understand when to use natural keys versus surrogate keys, and how different SQL databases implement pseudo keys. This lesson helps you apply best practices to manage database associations effectively.

We'll cover the following...

Recently, a software developer asked me a question about preventing duplicate rows. It’s a question I get asked frequently and the first response that comes to mind is to ask the developer to check if she lacks a primary key. But in this case, this wasn’t the problem.

The developer in this case had stored articles for publishing on a website in his content management database. He had used an intersection table for a many-to-many association between a table of articles and a table of tags.

MySQL
CREATE TABLE ArticleTags (
  id SERIAL PRIMARY KEY,
  article_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (article_id) REFERENCES Articles (id),
  FOREIGN KEY (tag_id) REFERENCES Tags (id)
);

He was getting incorrect results from queries when counting the number of articles with a given tag. He knew that there was only one article with a particular tag, but the query was telling him that there were three.

MySQL
SELECT tag_id, COUNT(*) AS articles_per_tag FROM ArticleTags WHERE tag_id = 327;

When he queried all the rows matching that tag_id, he saw that the tag was associated with one particular article thrice; three rows showed the same association, although they had different values for id.

MySQL
SELECT * FROM ArticleTags;

After seeing the details, we came to know ...