SQL Commands: DML, DDL, DCL, TCL, DQL with Query Example

What is SQL?

SQL is a database language designed for the retrieval and management of data in a relational database.

SQL is the standard language for database management. All the RDBMS systems like MySQL, MS Access, Oracle, Sybase, Postgres, and SQL Server use SQL as their standard database language. SQL programming language uses various commands for different operations. We will learn about the like DCL, TCL, DQL, DDL and DML commands in SQL with examples.

Why Use SQL?

Here, are important reasons for using SQL

• It helps users to access data in the RDBMS system.
• It helps you to describe the data.
• It allows you to define the data in a database and manipulate that specific data.
• With the help of SQL commands in DBMS, you can create and drop databases and tables.
• SQL offers you to use the function in a database, create a view, and stored procedure.
• You can set permissions on tables, procedures, and views.

Brief History of SQL

Here, are important landmarks from the history of SQL:

• 1970 - Dr. Edgar F. "Ted" Codd described a relational model for databases.
• 1974 - Structured Query Language appeared.
• 1978 - IBM released a product called System/R.
• 1986 - IBM developed the prototype of a relational database, which is standardized by ANSI.
• 1989- First ever version launched of SQL
• 1999 - SQL 3 launched with features like triggers, object-orientation, etc.
• SQL2003- window functions, XML-related features, etc.
• SQL2006- Support for XML Query Language
• SQL2011-improved support for temporal databases

Types of SQL

Here are five types of widely used SQL queries.

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language(DCL)
• Transaction Control Language(TCL)
• Data Query Language (DQL)

Types of SQL

Let see each of them in detail:

What is DDL?

Data Definition Language helps you to define the database structure or schema. Let's learn about DDL commands with syntax.

Five types of DDL commands in SQL are:

CREATE

CREATE statements is used to define the database structure schema:

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]); 

For example:

Create database university;
Create table students;
Create view for_students;

DROP

Drops commands remove tables and databases from RDBMS.

Syntax

DROP TABLE ;

For example:

Drop object_type object_name;
Drop database university;
Drop table student;

ALTER

Alters command allows you to alter the structure of the database.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition; 

To modify an existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);  

For example:

Alter table guru99 add subject varchar; 

TRUNCATE:

This command used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;  

Example:

TRUNCATE table students;

What is Data Manipulation Language?

Data Manipulation Language (DML) allows you to modify the database instance by inserting, modifying, and deleting its data. It is responsible for performing all types of data modification in a database.

There are three basic constructs which allow database program and user to enter data and information are:

Here are some important DML commands in SQL:

• INSERT
• UPDATE
• DELETE

INSERT:

This is a statement is a SQL query. This command is used to insert data into the row of a table.

Syntax:

INSERT INTO TABLE_NAME  (col1, col2, col3,.... col N)  
VALUES (value1, value2, value3, .... valueN);  
Or 
INSERT INTO TABLE_NAME    
VALUES (value1, value2, value3, .... valueN);    

For example:

INSERT INTO students (RollNo, FIrstName, LastName) VALUES ('60', 'Tom', Erichsen');

UPDATE:

This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]   

For example:

UPDATE students    
SET FirstName = 'Jhon', LastName= 'Wick' 
WHERE StudID = 3;

DELETE:

This command is used to remove one or more rows from a table.

Syntax:

DELETE FROM table_name [WHERE condition];  

For example:

DELETE FROM students 
WHERE FirstName = 'Jhon';

What is DCL?

DCL (Data Control Language) includes commands like GRANT and REVOKE, which are useful to give "rights & permissions." Other permission controls parameters of the database system.

Examples of DCL commands:

Commands that come under DCL:

• Grant
• Revoke

Grant:

This command is use to give user access privileges to a database.

Syntax:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;  

For example:

GRANT SELECT ON Users TO'Tom'@'localhost;

Revoke:

It is useful to back permissions from the user.

Syntax:

REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}

For example:

REVOKE SELECT, UPDATE ON student FROM BCA, MCA;  

What is TCL?

Transaction control language or TCL commands deal with the transaction within the database.

Commit

This command is used to save all the transactions to the database.

Syntax:

Commit;

For example:

DELETE FROM Students  
WHERE RollNo =25;  
COMMIT;  

Rollback

Rollback command allows you to undo transactions that have not already been saved to the database.

Syntax:

ROLLBACK;  

Example:

DELETE FROM Students  
WHERE RollNo =25;  

SAVEPOINT

This command helps you to sets a savepoint within a transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example:

SAVEPOINT RollNo;

What is DQL?

Data Query Language (DQL) is used to fetch the data from the database. It uses only one command:

SELECT:

This command helps you to select the attribute based on the condition described by the WHERE clause.

Syntax:

SELECT expressions    
FROM TABLES    
WHERE conditions;  

For example:

SELECT FirstName  
FROM Student  
WHERE RollNo > 15;  

What are SQL constraints? Explain with examples.

 SQL constraints are a set of rules implemented on tables in relational databases to dictate what data can be inserted, updated or deleted in its tables. This is done to ensure the accuracy and the reliability of information stored in the table. Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.  Once the constraint is placed, if any operation in the database does not follow the rules specified by the constraint, the particular operation is aborted. In this article, we will go through what SQL constraints are, what are the different kinds of SQL constraints are commonly used and how to implement and get rid of them. First, however, we will take a brief look into why they are needed.

Need for SQL Constraints

Before we get into the details of what SQL constraints are, let’s take a look at why they are necessary. In order to arrive at that answer, we need to first understand the way in which information is stored in relational databases and why it is of primal importance to ensure that frameworks are in place governing what information can be entered or altered in a way that that information in the tables are not corrupted.

At a high level, information in relational databases is segregated into two types, facts and dimensions. Fact tables contain information relating to mathematical information, as a thumb rule, any information on which mathematical operations can be applied, are facts. Sales numbers, costs and revenues are examples of facts. Whereas information like name, date of birth, geographic location etc are examples of dimensional information. In an optimally normalised database, facts and dimensions are stored in separate tables. In order to facilitate rapid extraction of information, relations are built between particular columns in the fact and dimension tables.  This is where relational databases get their name from and it is on the basis of these relationships that multiple tables are joined and the data in the tables extracted. Fact and dimension tables are organized in particular structures known as schemas.  Star schema and snowflake schema are popular ways of organising this information.

Star Schema 

The below diagram is an example of a star schema where a central fact table is associated with multiple dimensional tables via relations between columns in the fact and dimension tables. 

Snowflake Schema 

In snowflake schemas, the central fact table is associated with multiple dimension tables which in their turn are then associated with multiple other dimension tables.

Generally, the association between fact and dimension tables happen via something called a PRIMARY KEY – FOREIGN KEY relationship. A PRIMARY KEY is a unique identifier of a particular row of information in a fact table, which connects to the FOREIGN KEY in a dimension table to identify dimension information for the same row in the dimension table. Since PRIMARY KEYS are unique identifiers for particular sets of information, for example, a particular sale in the Sales table, it is imperative that the PRIMARY KEY column in the table do not contain duplicates, that is, multiple sales cannot have the same unique ID. Also, values in the PRIMARY KEY column cannot be blank.  Otherwise, we wouldn’t be able to identify that particular Sale. So generally, there are rules placed on PRIMARY KEY columns in fact tables that ensure the column doesn’t contain duplicates and no row in the column is blank. This is a classic example of a SQL constraint!  Relational databases are quite fragile in the way that very small changes in the information stored in tables can upset the proper functioning of data storage and data extraction from the database.  SQL constraints are placed to ensure that insertions, updates, and deletion of information in a database occur in a way that the smooth functioning of the database is maintained.

Below is a list of common reasons why SQL constraints are applied on databases:

• Prevent bad data from being entered into tables of interest.
• Enforce business logic at the database level.
• Documentation of important database rules.
• Enforce relational integrity between any number of tables.
• Improve database performance.
• Enforce uniqueness.

Types of SQL Constraints:

SQL constraints can be at a column or a table level. Column level constraints apply to specific columns in a table and do not specify a column name except the check constraints. They refer to the column that they follow. The names are specified by the Table-level constraints of the columns to which they apply.

Following is a list of the most commonly used column and table level SQL constraints:

Column Level Constraints include:

• NOT NULL Constraint
• UNIQUE Constraint
• DEFAULT Constraint
• CHECK Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint

Column Level Constraints include:

• UNIQUE Constraint
• CHECK Constraint
• PRIMARY KEY Constraint
• FOREIGN KEY Constraint
• INDEX Constraint

Let us now dive into the world of SQL constraints! We will browse in detail what each constraint is, why we use them and how to apply and remove them.

The NOT NULL Constraint

A NOT NULL constraint specifies that no cell value for any row in this column can be blank. Generally, this rule is applied to columns that capture information that is absolutely vital to identify and extract data from a table. Continuing the Sales table example, Sale_Id and the Sales_Amount would be potential columns for applying the NOT NULL constraint.

Applying the NOT NULL constraint:

The NOT NULL constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a NOT NULL Constraint during the Creation of a Table:

The following SQL creates a NOT NULL constraint in the columns ‘Sale_Id’, ‘Sale_Amount’ and ‘Vendor_Name’ when the table ‘Sales’ is created:

1 2 3 4 5 6 7

CREATE TABLE Sales (     Sale_Id int NOT NULL,     Sale_Amount int NOT NULL,     Vendor_Name varchar(255) NOT NULL,     Sale_Date date,     Profit int );

By specifying the words NOT NULL after the column definition, we create a ‘Sales’ table where the ‘Sale_Id’, ‘Sale_Amount’ and ‘Vendor_Name’ columns cannot be blank. The column ‘Profit’ can have null values.

Altering a NOT NULL Constraint after the Creation of a Table:

Consider, that after the creation of the ‘Sales’ table and storing information in the same, business logic changes and now we’re instructed that no sales can be recorded in the ‘Sales’ table without recording the amount of profit that was earned on the sale. In that case, we will now have to add a constraint that the profit column cannot be null. This is how we would do it:

1 2	ALTER TABLE Sales MODIFY Profit int NOT NULL;

The UNIQUE Constraint 

The UNIQUE constraint specifies that no cell value in a column can be repeated throughout the table.  That is, each row for this column in the table has to be unique and non-repetitive. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint has a UNIQUE constraint automatically. However, you can have many UNIQUE constraints in a table, but only one PRIMARY KEY constraint can be there in one table.

Applying the UNIQUE constraint:

The UNIQUE constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a UNIQUE Constraint during the Creation of a Table:

The following SQL creates a UNIQUE constraint in the columns ‘Sale_Id’ when the table ‘Sales’ is created in various relational databases:

SQL Server / Oracle / MS Access:

1 2 3 4 5 6 7

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 );

MySQL:

1 2 3 4 5 6 7 8

CREATE TABLE Sales (     Sale_Id int NOT NULL,     Sale_Amount int NOT NULL,     Vendor_Name varchar(255) NOT NULL,     Sale_Date date,     Profit int,     UNIQUE(Sale_Id) );

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

SQL Server / Oracle / MS Access:

1 2 3 4 5 6 7

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:

To create a UNIQUE constraint on the “Sale_Id” column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales ADD UNIQUE(Sale_Id);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales ADD CONSTRAINT UC_Sales UNIQUE(Sale_Id,Sale_Amount);

Dropping a UNIQUE Constraint:

To drop a UNIQUE constraint, we will need to specify the naming convention that was used during the creation of the constraint:

MySQL:

1 2	ALTER TABLE Sales DROP INDEX UC_Sales;

SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales DROP CONSTRAINT UC_Sales;

The CHECK Constraint 

The CHECK constraint is used to ensure that all the records in a certain column follow a specific rule. Generally, this constraint is used to enforce business logic on values in a column to make sure that no corrupt information is entered. For example, in the ‘Sales’ table,  let’s say that the business has specified a rule that sales made to a certain vendor ‘ABC’ should not be entered into the ‘Sales’ table. To ensure that sales to this vendor are not entered into our table, we add a CHECK constraint on the ‘Vendor_Name’ column which will reject any operation that tries to insert the value ‘ABC’ in the ‘Vendor_Name’ column.

Applying the CHECK Constraint:

The CHECK constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a CHECK Constraint during the Creation of a Table:

The following SQL creates a CHECK constraint on the column ‘Vendor_Name’ when the table ‘Sales’ is created in various relational databases:

MySQL:

1 2 3 4 5 6 7 8

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:

1 2 3 4 5 6 7

CREATE TABLE Sales (     Sale_Id int NOT NULL UNIQUE,     Sale_Amount int NOT NULL,     Vendor_Name varchar(255) CHECK (Vendor_Name<> ’ABC’),     Sale_Date date,     Profit int );

Consider now that along with the rule for the vendor ‘ABC’, the business only wants sales with a profit greater than 500 to be recorded in the ‘Sales’ table. 

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

1 2 3 4 5 6 7 8

CREATE TABLE Sales (     Sale_Id int NOT NULL,     Sale_Amount int NOT NULL,     Vendor_Name varchar(255),     Sale_Date date,     Profit int, CONSTRAINT Chk_Sales CHECK (Vendor_Name <> ’ABC’ and Profit>500) );

Altering a CHECK Constraint after the Creation of a Table:

To create a CHECK constraint on the ‘Vendor_Name’ column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access

1 2	ALTER TABLE Sales ADD CHECK (Vendor_Name<> ‘ABC’);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales ADD CONSTRAINT Chk_Sales CHECK (Vendor_Name <> ’ABC’ and Profit>500)

Dropping a CHECK Constraint:

To drop a CHECK constraint, we will need to specify the naming convention that was used during the creation of the constraint:

MySQL:

1 2	ALTER TABLE Persons DROP CHECK CHK_Sales;

SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Persons DROP CONSTRAINT CHK_Sales;

The DEFAULT Constraint

The DEFAULT constraint is used to specify a default value that is to be entered in any record in a particular column is left blank. The default value will be added to all new records if no other value is specified.

Applying the DEFAULT Constraint:

The DEFAULT constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a DEFAULT Constraint during the Creation of a Table:

The following SQL sets a DEFAULT value for the ‘Vendor_Name’ column when the ‘Sales’ table is created:

My SQL / SQL Server / Oracle / MS Access:

1 2 3 4 5 6 7

CREATE TABLE Sales (     Sale_Id int NOT NULL UNIQUE,     Sale_Amount int NOT NULL,     Vendor_Name varchar(255) DEFAULT ‘Unknown Vendor’,     Sale_Date date,     Profit int );

The DEFAULT constraint can also be used to populate columns with system values, for example, GETDATE().

1 2 3 4 5 6 7

CREATE TABLE Sales (     Sale_Id int NOT NULL UNIQUE,     Sale_Amount int NOT NULL,     Vendor_Name varchar(255),     Sale_Date date DEFAULT GETDATE(),     Profit int );

Altering a DEFAULT Constraint after the Creation of a Table:

To create a DEFAULT constraint on the ‘Vendor_Name’ column when the table is already created, use the following SQL:

MySQL:

1 2	ALTER TABLE Sales ALTER Vendor_Name SET DEFAULT 'Unknown Vendor';

SQL Server:

1 2 3

ALTER TABLE Sales ADD CONSTRAINT df_Vendor DEFAULT 'Unknown Vendor' FOR Vendor_Name;

MS Access:

1 2	ALTER TABLE Sales ALTER COLUMN Vendor_Name SET DEFAULT 'Unknown Vendor';

Oracle:

1 2	ALTER TABLE Sales MODIFY Vendor_Name DEFAULT 'Unknown Vendor';

Dropping a DEFAULT Constraint:

To drop a DEFAULT constraint, use the following SQL:

MySQL:

1 2	ALTER TABLE Sales ALTER Vendor_Name DROP DEFAULT;

SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales ALTER COLUMN Vendor_Name DROP DEFAULT;

The INDEX Constraint

The INDEX constraint is used to create indexes on a table in a relational database. Tables in a relational database can grow to be extremely long with a great number of rows present in each table, under the circumstances, retrieving information via SQL can sometimes be a very time taking process. By creating an index, the performance of data retrieval queries can be greatly improved. The users cannot see the indexes, they are just used by the SQL engine to speed up searches/queries.

Applying the INDEX Constraint:

We have the option of creating an INDEX that allows duplicates, or we can create a unique INDEX. Indexes can be created or dropped at any point in time and do not have to be a part of the table definition at the time of table creation.

Creating an INDEX Constraint:

Creates an INDEX on a table with duplicate values allowed:

1 2	CREATE INDEX idx_id ON Sales (Sale_Id);

Creating aUNIQUEINDEX Constraint:

Creates a unique INDEX on a table. Duplicate values are not allowed:

1 2	CREATE UNIQUE INDEX idx_id ON Sales (Sale_Id);

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

1 2	CREATE UNIQUE INDEX idx_sale ON Sales (Sale_Id, Sale_Amount);

Dropping an INDEX Constraint:

The DROP INDEX statement is used to delete an index in a table:

MS Access:

1	DROP INDEX idx_id ON Sales;

SQL Server:

1	DROP INDEX Sales.idx_id;

DB2/Oracle:

1	DROP INDEX idx_id;

MySQL:

1 2	ALTER TABLE Sales DROP INDEX idx_id;

The PRIMARY KEY Constraint

PRIMARY KEYS are unique identifiers for each row present in a table. They can be values present in a single column of a table or a combination of multiple columns in the table. The PRIMARY KEY column cannot be NULL and has to be UNIQUE. The value of the PRIMARY KEY in the table is a unique identifier for a particular row in the parent table which connects the row of the table to further information available in another table (the child table), where the same unique identifier exists as a FOREIGN KEY. Every FOREIGN KEY value in the second table has to exist in the first as a PRIMARY KEY. This is how information is kept consistent in relational databases when they are broken down into multiple Fact and Dimension tables. The PRIMARY KEY – FOREIGN KEY columns are used as the join condition between two tables and the contained information in the tables are extracted.

Applying the PRIMARY KEY Constraint:

The PRIMARY KEY constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a PRIMARY KEY Constraint during the Creation of a Table:

The following SQL creates a PRIMARY KEY on the ‘Sale_Id’ column when the ‘Sales’ table is created in various relational databases:

MySQL:

1 2 3 4 5 6 7 8

CREATE TABLE Sales (     Sale_Id int NOT NULL,     Sale_Amount int NOT NULL,     Vendor_Name varchar(255),     Sale_Date date,     Profit int,     PRIMARY KEY (Sale_Id) );

SQL Server / Oracle / MS Access:

1 2 3 4 5 6 7

CREATE TABLE Sales (     Sale_Id int NOT NULL PRIMARY KEY,     Sale_Amount int NOT NULL,     Vendor_Name varchar(255),     Sale_Date date,     Profit int, );

To allow naming of a PRIMARY KEY constraint and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

1 2 3 4 5 6 7 8

CREATE TABLE Sales (     Sale_Id int NOT NULL,     Sale_Amount int NOT NULL,     Vendor_Name varchar(255),     Sale_Date date,     Profit int,     CONSTRAINT PK_Sales PRIMARY KEY (Sale_Id, Sale_Amount) );

Note: In the example above there is only ONE PRIMARY KEY (PK_Sales). However, the VALUE of the primary key is made up of TWO COLUMNS (Sale_Id + Sale_Amount).

Altering a PRIMARY KEY Constraint after the Creation of a Table:

To create a PRIMARY KEY constraint on the ‘Sale_Id’ column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales ADD PRIMARY KEY (Sale_Id);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales ADD CONSTRAINT PK_ Sales PRIMARY KEY (Sale_Id,Sale_Amount);

Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been declared to not contain NULL values (when the table was first created) and have only UNIQUE values, otherwise the SQL statement will not be executed.

Dropping a PRIMARY KEY Constraint:

To drop a PRIMARY KEY constraint, use the following SQL:

MySQL:

1 2	ALTER TABLE Sales DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales DROP CONSTRAINT PK_Sales;

The FOREIGN KEY Constraint

The foreign key constraint is used to prevent operations in a relational database that would destroy links between tables. The FOREIGN KEY is a column (or a group of columns) in one table, that refers to the PRIMARY KEY of another table. The table with the FOREIGN KEY is called the child table while the referenced table with the PRIMARY KEY is called the parent table. 

Consider the two following tables:

Sales table:

Sale_Id	Sale_Amount	Vendor_Name	Sale_Date	Profit
123	100	ABC	01-12-2018	20
234	200	BCD	14-06-2019	55
345	500	CDE	22-03-2020	32
456	100	EFG	25-04-2021	40

Sales_Person table:

Sales_Person_Id	Sales_Person_Name	Sales_Person_Location	Sale_Id
1	Rahul	Kolkata	234
2	Sweta	Mumbai	456
3	Atul	New Delhi	123
4	Shruti	Mumbai	345

The ‘Sale_Id’ column in the ‘Sales_person’ table refers to the ‘Sale_Id’ in the ‘Sales’ table. 

The ‘Sale_Id’ in the ‘Sales’ table is the PRIMARY KEY.

The ‘Sale_Id’ in the ‘Sales_Person’ table is the FOREIGN KEY.

Notice that every value for ‘Sale_Id’ present in the ‘Sales_Person’ table is also available in the ‘Sale_Id’ column in the ‘Sales’ table. This is so due to the PRIMARY KEY – FOREIGN KEY relationship defined between the two tables. No value for a ‘Sale_Id’ can be entered into the ‘Sales_Person’ table that does not already exist in the ‘Sales’ table. If we try to insert such a value, because of the PRIMARY KEY – FOREIGN KEY constraint, the insertion will be rejected.

Applying the FOREIGN KEY Constraint:

The FOREIGN KEY constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a FOREIGN KEY Constraint during the Creation of a Table:

The following SQL creates a FOREIGN KEY on the ‘Sale_Id’ column when the ‘Sales_Person’ table is created:

MySQL:

1 2 3 4 5 6 7 8

CREATE TABLE Sales_Person (     Sales_Person_Id int NOT NULL,     Sales_Person_Name varchar(255),     Sales_Person_Location varchar(255),     Sale_Id int NOT NULL,     PRIMARY KEY (Sales_Person_Id),     FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id) );

SQL Server / Oracle / MS Access:

1 2 3 4 5 6

CREATE TABLE Sales_Person (     Sales_Person_Id int NOT NULL PRIMARY KEY,     Sales_Person_Name varchar(255),     Sales_Person_Location varchar(255),     Sale_Id int FOREIGN KEY REFERENCES Sales(Sale_Id) );

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

1 2 3 4 5 6 7 8 9

CREATE TABLE Sales_Person (     Sales_Person_Id int NOT NULL,     Sales_Person_Name varchar(255),     Sales_Person_Location varchar(255),     Sale_Id int NOT NULL,     PRIMARY KEY (Sales_Person_Id),     CONSTRAINT FK_Sales_Sales_Person FOREIGN KEY (Sale_Id)     REFERENCES Persons Sales(Sale_Id) );

Altering a FOREIGN KEY Constraint after the Creation of a Table:

To create a FOREIGN KEY constraint on the ‘Sale_Id’ column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales_Person ADD FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id);

To allow naming of a FOREIGN KEY constraint and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

1 2 3

ALTER TABLE Sales_Person ADD CONSTRAINT FK_Sales_Sales_Person FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id);

Dropping a FOREIGN KEY Constraint:

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:

1 2	ALTER TABLE Sales_Person DROP FOREIGN KEY FK_Sales_Sales_Person;

SQL Server / Oracle / MS Access:

1 2	ALTER TABLE Sales_Person DROP CONSTRAINT FK_Sales_Sales_Person;