You have already learnt about the SETL process, which is used to ingest data into a schema and perform operations on it. But can we add just any value that we want to a schema or are there any constraints to maintain the sanctity of the database schema?
Put yourself in the place of a data analyst at Uber. A database at Uber has several tables, which record several details, such as details on the rider, the driver, the vehicle used and transaction details. Each such table has several related attributes, which describe it in detail. Consider the ‘Rider’ table. You go through the values entered in a column that contains the fares for each ride. It would be right for you to expect that the fares can have a maximum of four digits – a fare more than even ?5,000 would raise concerns. This is definitely an outlier and needs to be replaced with the right value. Ensuring that you implement the right constraints for the right attributes in a table can help you avoid such fallacies.
In the next video, you will learn about the relational data model.
So, as you learnt in the video, constraints are the rules that are used in MySQL to restrict the values that can be stored in the columns of a database. This ensures data integrity, which is nothing but the accuracy and consistency of the data stored in the database. Let’s continue our discussion on the relational model in the next video.
So, as you learnt in the video, entity constraints are of the following different types:
- Unique: This constraint is used for columns that need unique values. For example, ’employee ids’ should be unique in an ’employees’ table.
- Null: This constraint is used to determine the columns that can have null values. For example, an employee may not need to specify their location, which means the ‘location’ column can have null values in an ’employees’ table.
- Primary Key: This constraint is used to determine the column that uniquely identifies a table. For example, ’employee ids’ uniquely identify every employee. Two employees may have the same name or the same salary, but not the same employee id.
Note that there may be a situation wherein, say, a company wants to store the records of its employees over multiple years. In this case, ’employee id’ may not be unique, since an employee will have multiple rows storing details of multiple years. Also, you will need a new column named ‘year’ in the table.
In this case, a combination of an employee id and a year, i.e., a variable EmpID-Year, can act as a composite primary key. To see how this is done in MySQL, you can refer to this Stack Overflow answer.
In the next segment, you will learn referential constraints.