As we know, the world is filled with data now, and this is considered to be one of the crucial assets to turn the future of any business. Each and every app we use is filled with data. Businesses, corporate, government, everyone gather and store data about the users and use it for various purposes. In the coming days, data will be crucial in terms of analytics and also fueling future technologies like the Internet of Things, Machine Learning, Artificial Intelligence, etc.
On social media platforms like Facebook, they store all information about the users as to:
- who are you?
- Where are you from?
- To whom you are connected?
- What activities do you like the most?
- What are you posting? And so on.
Bank of America, say for example stores data about their users as to how much money you hold in your account and into which all accounts. They gather information about your transactions, which are not only used to streamline their operations, but also to help users with proper budgeting of their expenses or to plan their investment.
Relational databases
So, how do all these applications store our data? Simple, they use a database which is a program to help store data, maintain it, modify it, and retrieve it whenever needed. There are various types of databases now in different formats, but the most popular and most conventional mode of the database is a relational database or SQL database.
Relational database stores data in structured table format, which is more or less like what we have seen in spreadsheets like MS Excel. There are rows and columns of data, in which a row represents a particular item, and the column has its property. Why it is called relational database is because it makes is so easy to establish a relationship between these tables.
Say, for example, if you store data about students and their badges at school, one can simply maintain a student table and a badge table, and then a student badges table to see which student has which badge by simply mapping the student IDs to the badge IDs. This is a much comfortable and accurate model of data storage than with the need to repeat everything regarding students and same for the badges.
When it comes to manipulating data in these databases, there is a need for a query language, which can interact with the database entries. Traditionally, SQL (structured query language) is used to access databases. We can use SQL not only to create the tables but also to change data, retrieve data we need, etc.
SQL as a database programming language
Structured Query Language is simply a computer language which has commands to store, retrieve, and manipulate data in relational databases. SQL was first introduced back in 1974 by a group of IBM developers as a prototype of the relational databases. Relational Software (later Oracle) released the first relational database for commercial purposes.
RemoteDBA.com list out the most commonly used SQL statements is as below.
- SQL Commands are the most basic SQL statements for data storing, retrieval, and manipulation of data in the relational database.
- Advanced SQL handles the more advanced SQL commands.
- SQL Functions to operate the largely used math function in SQL.
- SQL String functions to handle the major string functions.
- SQL Date function.
- Data Definition Language or DDL consists of commands to create, delete, or modify database related objects.
- SQL Constraint has commands which limit the data types to be inserted into columns and tables.
- SQL ALTER TABLE to change the table structure later.
- SQL NULL to discuss the NULL concept and the functions with the NULL concept.
Now let’s look into SQL Constraints in a bit more detail.
SQL Constraints
Constraints are actually database rules which are enforced on the data columns. Constraints help to limit the data types which can get into a table. It will help ensure the database accuracy and reliability of its data. Constraints can apply either at table level or at columnar level. Constraints at column level are applied to one column at a time; however, table constraints are applied as a whole.
The most common constraints in SQL are as below:
- NOT NULL − To ensure that the specific column cannot hold a NULL value.
- DEFAULT − Assigns a default value to a column if there is no data.
- UNIQUE − To make sure all the values in a given column are different.
- PRIMARY Key − Identify each row uniquely in a table.
- FOREIGN Key − Identify a row/record in the given DB table.
- CHECK − To ensure that all values in a specific column satisfies given conditions.
- INDEX − To create or retrieve data from the given database quickly.
We can specify SQL Constraints during the time of table creation itself or later by using the statement of ‘ALTER TABLE’ to create a new constraint and run it.
Removing constraints
The constraints you have defined at the time of table creation can be later dropped using an ALTER TABLE statement and then by choosing the option of DROP CONSTRAINT. Some implementation may have shortcuts to drop given. In some implementations, it is also possible to temporarily disable the constraints. Unlike dropping the constraints, you can enable these constraints if needed at a later date.
Checking data accuracy with Integrity Constraints
Integrity constraints can be used to make sure that the data in a relational database is accurate and also to check its consistency. Data integrity in a relational database is handled through the concept called referential integrity. There are different types of integrity constraints in databases which play a significant role in terms of Referential Integrity (RI). Such constraints include, but not limited to Foreign Key, Primary Key, and other Unique Constraints as we discussed above, etc.
If you are aiming at gaining knowledge in DBA, it takes significant time and effort to become an expert database administration. It all comes with a lot of experience in handling different types of database designs. However, SQL programming is not as difficult or complicated like the general coding in application development, but someone has a keen focus on it can become a successful DBA by learning these concepts.
