What are some reasons to apply constraints to a table?

Question

In the context of this exercise, what are some reasons to apply constraints to a table?

Answer

Applying constraints to a table can be useful, and provide some important benefits such as reliability and consistency of your data. The following are a few reasons you might consider for applying constraints to a table.

One reason for adding constraints is to prevent invalid data in the table. This is very important, because invalid data can cause issues and unexpected results from calculations. We do not have to worry about new data being added that might otherwise violate our constraints and cause bigger issues.

Similar to the previous point, constraints can let us prevent missing data, which is usually filled as NULL within the table. Instead of having missing values set to NULL, we can set constraints so that the missing values are given some default value instead, like 0. This can make some calculations easier to do.

Another important reason to add a constraint is for uniqueness, usually in the form of values like the id, or identifier column. By using a constraint like the PRIMARY KEY, we can ensure that every row has their own unique id value.

17 Likes

Do I understand correct that PRIMARY KEY is meant to give identification criteria to the row, whereas UNIQUE works as a clause that helps seperate rows with identical values? For example if we have a column name_of_customers an set it as UNIQUE, and after that we want to group rows by name_of_customer, this UNIQUE clause will not prevent from grouping different customers with identical name from merging?

1 Like

My understanding is that UNIQUE is used for human readable identifiers. Say, we assign each customer with id as INTEGER PRIMARY KEY. But an integer doesn’t tell us who this customer is. So we want to have another identifier name_of_customers as TEXT UNIQUE. This is useful for us to view the data, if not useful for data manipulation.

7 Likes

But why not to use just TEXT? Because UNIQUE is a separate constraint, we can skip it. And there could be identical names in name_of_customers, so if UNIQUE constrain doesn’t let us hame to same names in the one column, that could be a problem. So i wonder, if UNIQUE doesn’t let us make new row if we had that name before, i would rather use e-mail, because there is no way, that 2 people may have one e-mail. But I also wonder if it works different way and helps to not merge identical names when you set the table view by name_of_customer. Or maybe there is another syntax, that helps to do that.

17 авг. 2019 г., в 8:46, Masayuki Kudamatsu via Codecademy Forums [email protected] написал(а):

4 Likes

If the data contains multiple persons with the identical name, I would just use TEXT, as you suggest, if I treat name_of_customers just as a variable to analyze. (Say, I’m interested in the popularity of particular surnames.)

But if I use name_of_customers as a human readable identifier, I would enter the data like ‘John Smith A’, ‘John Smith B’ etc. to make each name unique and impose TEXT UNIQUE, because I want to immediately know which John Smith I’m looking at. For customer datasets, this may not be necessary. But if you’re a political scientist working on the dataset of U.S. politicians, for example, you want to know which George Bush you’re looking at, when you’re browsing the data.

Email address can be used as a unique identifier, but it’s not human readable, because some people use a weird email address like emerald_green1032@gmail…, which doesn’t really tell who it is.

That’s my take on this issue.

8 Likes

Does using constraints overcome SQLite disadvantage of not restricting the user from inputting wrong values?

We don’t have the right to name someone John Smith A and John Smith B in case of real life. The workaround that I see here is to introduce another field such as email or address, which will be unique for two people with two similar full names.

5 Likes

Only one field in a given table can be the primary key, but there are going to be situations where two fields must have unique values. A good example would be a table containing employee information.

In the United States, at least, most companies will assign each employee an “Employee ID”, which will typically function as the primary key, but all employees also have unique Social Security Number (SSN). Because of the sensitivity of a Social Security Number, Employee ID is generally used in as many situations as possible, but it is necessary to collect SSN data for payroll and tax purposes.

As a result, it is imperative that we know that each record in the “employee” table will have both a unique “Employee ID” and a unique “SSN”.

I’m sure that there are many similar situations, but this is the first one that came to mind.

39 Likes

Great answer! I think this summarizes the reasoning perfectly!

amazing explanation thank u so much

By ‘wrong values’, do you mean ‘wrong type of value’?

Good answer.
This is the understanding that I got as well. I think some people in the comments are focused on the fact that, in the first example, ‘UNIQUE’ happens to be constraining the name column and, in life, names aren’t always unique. This assumes that we know the reasoning of the creator of the table and why they made that choice for this particular table, which we don’t. If they had used “username” as the column, I think it would intuitively make more sense.

That brings me to the question that actually brought me here. Other than ‘PRIMARY KEY’ only being able to be used once, does ‘PRIMARY KEY’ have any other features that ‘UNIQUE’ doesn’t give us?

6 Likes

Constraints are also used to prevent SQL injections, a type of exploit hackers use to gain unauthorized access to systems.

Name + Address are not unique - two same named persons may live together :wink: .

I´m receiving this error, and not sure why, since I´ve add the text in code editor as instructed:

Type Grammy in single quotes except of double quote; it is not a error, you can use bother single and double quote. But here to prevent error use single quote rather than double. :slight_smile:

  1. Data Integrity: Constraints ensure that the data in a table is accurate, consistent, and valid. For example, the NOT NULL constraint ensures that a column cannot have a null value, which can help prevent data entry errors.
  2. Data Consistency: Constraints ensure that the data in a table is consistent with the data in other related tables. For example, a FOREIGN KEY constraint ensures that a value in one table corresponds to a value in another table, helping to maintain referential integrity.
  3. Data Validity: Constraints ensure that the data in a table meets specific criteria or conditions. For example, a CHECK constraint can ensure that a value in a column meets a certain condition, such as being within a certain range of values.
  4. Performance: Constraints can improve query performance by creating indexes on the columns that are used in the constraints. This can speed up data retrieval and manipulation operations.
  5. Security: Constraints can be used to enforce access control policies by restricting the values that can be inserted, updated, or deleted in a table. For example, a constraint can be used to restrict certain users or roles from modifying certain columns in a table.
2 Likes

I couldn’t understand when to use PRIMARY KEY & UNIQUE when creating a table?

1 Like

@timsfine Excellent response. Thank you! I think it clears up a lot of the confusions other users had.

@guiwald @shaghdari I think not covered in this lesson and not completely foolproof but as @ruby7951645841 had mentioned there’s a different constraint you can put in called CHECK that can ensure that a value in a column meets certain condition such as being within a range of values. That could potentially limit and help ensuring the right types of data gets entered.