SQL Constraints
The rules that restrict the kind of data that can be entered into a database are known as SQL constraints, and they can be applied to individual data columns or the entire table. When you try to conduct any INSERT, UPDATE, or DELETE operation on the table, RDBMS will verify whether the data restricts any existing constraints and if there is a conflicts between the declared constraint and the data action, it aborts the process and returns an error.
Constraints can be defined at the table or column level. Table level restrictions are applied to the entire table, while column level constraints are applied to just one column.
Need for SQL Constraints
Let's look at the reasons why SQL constraints are required before moving into their specifics. We must first comprehend how information is stored in relational databases and why it is crucial to have frameworks in place that restrict what data can be entered or changed without harming the integrity of the data in the tables.
Facts and dimensions are the two broad categories into which information in relational databases is divided. knowledge regarding mathematics is included in fact tables; generally speaking, any knowledge that can be used to perform mathematical operations is considered a fact. Examples of facts are revenue, expenses, and sales figures. Dimensional information, on the other hand, includes details like name, birthdate, location, etc. In a database that has been optimally normalized, facts and dimensions are kept in different tables. Relationships between certain columns in the fact and dimension tables are constructed to enable quick information extraction. Multiple tables are connected based on these relationships, which is where relational databases receive their name.
This is where relational databases acquire their name, as the data in the tables is extracted and various tables are connected based on these relationships. Schemas are specific frameworks used to organize fact and dimension tables.
Two common schemas for organizing this data are the star schema and the snowflake model.
1. Schema Schema
A star schema is a database organizational layout that is best suited for usage in business intelligence or data warehouses. It holds qualities about the data in one or more smaller dimensional tables and transactional or measurable data in a single large fact table.
2. Snowflake Schema
In most cases, the PRIMARY KEY – FOREIGN KEY relationship is how fact and dimension tables are linked together. An individual row of data in a fact table can be uniquely identified by its PRIMARY KEY, which links to the FOREIGN KEY in a dimension table to locate dimension data for the same row in the dimension table. It is essential that the PRIMARY KEY column in the database not have duplicates, meaning that BCA different sales cannot have the same unique ID, since PRIMARY KEYS are unique identifiers for specific sets of information, such as a specific sale in the Sales table. Additionally, the PRIMARY KEY column cannot contain blank values. We wouldn't be able to recognize that specific Sale otherwise.
PRIMARY KEY columns in fact tables are typically subject to restrictions that make sure no row in the column is blank and that the column doesn't include duplicates. This is a quintessential SQL constraint! Relational databases are extremely brittle in that even minor modifications to the data contained in tables can interfere with the database's ability to store and retrieve data. SQL restrictions are put in place to guarantee that data insertions, updates, and deletions take place in a way that preserves the database's seamless operation.
Types of SQL Constraints
Table-level and column-level SQL constraints are also possible. With the exception of check constraints, column level constraints only apply to particular columns in a table and do not define a column name. They make reference to the column they belong to. The Table-level constraints of the columns to which they apply define the names.
Following is the list of the most commonly used column and table level constraints :
1. The NOT NULL Constraint
No cell value for any row in this column may be blank, according to a NOT NULL constraint. This criteria is typically used to columns that record data that is absolutely necessary for locating and extracting data from a database. Using the Sales database example once more, the NOT NULL constraint might be applied to the Sale-Id and Sales-Amount columns.
Applying the NOT NULL Constraints
Either the NOT NULL constraint can be defined at the time the table is created or it can be added later using a modify statement.
When a table is being created, a NOT NULL constraint is declared:
The following SQL creates a NOT NULL constraint in the columns 'Sale-Id', 'Sales-Amount' and 'Vendor-Name' where the table sales is created :
CREATE TABLE Sales (
Sale-Id int NOT NULL ,
Vendor-Name varchar (255) NOT NULL ,
Sale-Date date ,
Profit int
);
We build a "Sales" table where the "Sale-Id," "Sale-Amount," and "Vendor-Name" columns cannot be blank by adding the words NOT NULL after the column definition. Null values may be present in the "Profit" column.
Modifying a NOT NULL Constraint after Table Creation:
After the "Sales" table was created and data was stored in it, business logic changed, and we were told that no sales could be entered into the "Sales" table without recording the profit from the transaction. Then we'll need to include a restriction that the profit column can't be null. Here's how we would proceed:
ALTER TABLE SALES
MODIFY profit int NOT NULL;
2. The Unique Constraint
According to the UNIQUE constraint, no cell value in a column may appear more than once in the table. In other words, every row in the table for this column must be distinct and non-repeating. A column or group of columns is guaranteed to be unique by both the UNIQUE and PRIMARY KEY requirements. A UNIQUE constraint is automatically included in a PRIMARY KEY constraint. However, a table can have a lot of unique constraints, but it can only have one primary key constraint.
Applying the Unique Constraint :
An modify statement can be used to add the UNIQUE constraint later on, or it can be defined when the table is being created.
When creating a table, specifying a unique constraint:
In several relational databases, the following SQL generates a UNIQUE constraint in the columns "Sale-Id" when the table "Sales" is created:
SQL Server \ Oracle \ MS Access :
CREATE TABLE Sales (
Sale_Id int NOT NULL UNIQUE ,
Sale_Amount int NOT NULL ,
Vendor_Name varchar (255) NOT NULL ,
Sale_Date date ,
Profit int
);
My SQL :
CREATE TABLE Sales (
Sale-Id int NOT NULL ,
Sale-Amount int NOT NULL ,
Vendor-Name varchar (255)
Sale-Date date ,
Profit int,
UNIQUE (Sale-Id)
);
Use the following SQL syntax to construct a UNIQUE constraint on several columns and to name a UNIQUE constraint:
SQL Server \ Oracle \ MS Access :
CREATE TABLE Sales (
Sale-Id int NOT NULL ,
Sale-Amount int NOT NULL ,
Vendor-Name varchar (255) NOT NULL <
Sale-Date date ,
Profit int ,
CONSTRAINT uc-sales UNIQUE (Sale-Id , Sale-Amount));
Altering a UNIQUE Constraint after the creation of a Table :
Use the SQL below to apply a UNIQUE constraint to the "Sale-Id" column once the table has been created:
MY SQL \ Oracle \ MS Access :
ALTER TABLE Sales
ADD UNIQUE ( Sale_Id );
Use the following SQL syntax to construct a UNIQUE constraint on several columns and to name a UNIQUE constraint:
MY SQL \ ORACLE \ MS ACCESS :
ALTER TABLE Sales
DROP CONSTRAINT UC_Sales ;
Dropping a UNIQUE Constraint :
In order to remove a UNIQUE constraint, we must indicate the naming scheme that was applied when the constraint was created:
MY SQL :
ALTER TABLE Sales
DROP INDEX UC_Sales ;
MY SQL \ ORACLE \ MS ACCESS :
ALTER TABLE Sales
DROP CONSTRAINT UC_Sales ;
3. The Check Constraint :
To make sure that every record in a given column satisfies with a particular criterion, apply the CHECK constraint. To ensure that no inaccurate data is input, this constraint is typically used to apply business logic to values in a column. For instance, suppose that the company has established a rule in the "Sales" table that states that sales to a specific vendor, "ABC," are not to be recorded there. We add a CHECK constraint to the "Vendor-Name" column to prevent sales to this vendor from being entered into our table. This constraint will reject any operation that attempts to insert the value "ABC" in the "Vendor-Name" column.
Applying the Check Constraint :
The CHECK constraint can be defined at the time the table is created or it can be added later using an edit statement.
Declaring a Check Constraint during the creation of a table :
When the table "Sales" is established in different relational databases, the SQL that follows places a CHECK constraint on the field "Vendor-Name":
MY SQL :
CREATE TABLE Sales (
Sale-Id int NOT NULL UNIQUE ,
Sale-Amount int NOT NULL ,
Vendor-Name varchar (255)
Sale-Date date ,
Profit int ,
CHECK (Vendor-Name < > 'ABC '
);
SQL Server \ Oracle \ MS Access :
CREATE TABLE Sales (
Sale-Id int NOT NULL UNIQUE ,
Sale-Amount int NOT NULL ,
Vendor-Name varchar (255) CHECK (Vendor-Name < > 'ABC ' ),
Sal-Date date ,
Profit int
);
Now consider that in addition to the vendor "ABC" constraint, the company only wants transactions with a profit of more than $500 to be listed in the "Sales" table.
Use the SQL syntax below to define a CHECK constraint on several columns and to permit naming of a CHECK constraint:
MY SQL \SQL Server \ Oracle \ MS Access :
CREATE TABLE Sales (
Sale-Id int NOT NULL UNIQUE ,
Sale-Amount int NOT NULL ,
Vendor-Name varchar (255)
Sale-Date date ,
Profit int ,
CONSTRAINT chk-sales CHECK (Vendor-Name < > 'ABC ' and Profit >500)
);
Modifying a Special Restrictions Following Table Creation:
When the table has already been established, use the following SQL to add a UNIQUE constraint to the "Sale-Id" column:
MY SQL \SQL Server \ Oracle \ MS Access :
ALTER TABLE Sales
ADD CHECK (Vendor-Name <> 'ABC ' ) ;
To name a CHECK constraint and define a CHECK constraint on several columns, use the following SQL syntax:
MY SQL \SQL Server \ Oracle \ MS Access :
ALTER TABLE Sales
ADD CONSTRAINT Chk-Sales CHECK (Vendor-Name <> 'ABC ' and Profit >500 )
Dropping a CHECK Constraint :
To remove a CHECK constraint, we must give the naming convention used when it was created:
MY SQL :
SQL Server /Oracle / MS Access :
ALTER TABLE Persons
DROP CONSTRAINT CHK-Sales ;
4. THE INDEX Constraint
To create indexes on a table in a relational database, utilize the INDEX constraint. Tables in a relational database can grow to be quite long, with many rows in each table; as a result, accessing information via SQL can be a time-consuming procedure. By constructing an index, data retrieval queries can be significantly enhanced. Users cannot see the indexes; they are only utilized by the SQL engine to accelerate searches and queries.
APPLYING The INDEX Constraint :
We can design an INDEX that allows duplicates or a unique INDEX. Indexes can be created or dropped at any time and do not have to be included in the table definition when it is formed.
Creating an INDEX Constraint :
Creates an INDEX on a table with duplicate values allowed :
Creating a UNIQUE INDEX Constraint :
Creates a unique INDEX on a table .
Duplicate Values are not allowed :
To establish an index on a combination of columns, list the column names in parenthesis, separated by commas:
To establish an index on a combination of columns, list the column names in parenthesis, separated by commas:
Dropping an INDEX Constraint :
The DROP INDEX statement is used to delete an index in a table :
MS ACCESS
DROP INDEX idx_id ON Sales :
SQL Server :
DROP INDEX Sales . idx_id ;
DB2/Oracle
DROP INDEX idx_id ;
MY SQL :
Conclusion
This completes our list of commonly used SQL constraints. Which one do you believe is more useful? Tell us about your experience with SQL limitations in the comments area below. Happy learning!
