UNIT-2

10M Q &A
1. Explain SQL functions for string conversions with examples.
2. Write and explain the SQL functions (i)Date and Time(ii) Numeric.
3. Explain creating and modifying relations in SQL. With examples

4. Explain about integrity constraints over relations.

5. Explain domain constraints & key constraints with an example.
6. What is the importance of null values? Explain comparison of using null values and not
using null values with examples.
7. Explain with examples, different DDL and DML operations in SQL.
8. Discuss about arithmetic & logical operations in SQL with examples.
1)Explain SQL functions for string conversions with examples.

In SQL, string conversion functions are used to manipulate and convert data types, particularly
converting between strings and other data types, or modifying strings themselves. Here are some
commonly used SQL string conversion functions along with examples:

1. CAST() Function

The CAST() function is used to convert one data type to another. It can be used for converting
strings to integers, dates, etc.

Syntax:
sql
Copy code
CAST(expression AS data_type)

Example:
sql
Copy code
SELECT CAST('2024-10-17' AS DATE) AS ConvertedDate;

This converts the string '2024-10-17' to a date type.

2. CONVERT() Function

The CONVERT() function is similar to CAST() but provides more formatting options, especially for
date and time.

Syntax:
sql
Copy code
CONVERT(data_type, expression [, style])

Example:
sql
Copy code
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS USFormattedDate;

This converts the current date to a string in the format MM/DD/YYYY.

3. UPPER() and LOWER() Functions

These functions are used to convert strings to uppercase or lowercase, respectively.

Syntax:
sql
Copy code
UPPER(string)
LOWER(string)

Example:
sql
Copy code
SELECT UPPER('hello world') AS UppercaseString,
LOWER('HELLO WORLD') AS LowercaseString;

This will return:

 UppercaseString: HELLO WORLD

 LowercaseString: hello world

4. LTRIM() and RTRIM() Functions

These functions remove leading and trailing spaces from strings.

Syntax:
sql
Copy code
LTRIM(string)
RTRIM(string)

Example:
sql
Copy code
SELECT LTRIM(' Hello World') AS TrimmedLeft,
RTRIM('Hello World ') AS TrimmedRight;

This will return:

 TrimmedLeft: Hello World

 TrimmedRight: Hello World

5. TRIM() Function

The TRIM() function is used to remove both leading and trailing spaces from a string.

Syntax:
sql
Copy code
TRIM(string)

Example:
sql
Copy code
SELECT TRIM(' Hello World ') AS TrimmedString;

This will return Hello World.

6. SUBSTRING() Function

The SUBSTRING() function extracts a portion of a string.

Syntax:
sql
Copy code
SUBSTRING(string, start_position, length)

Example:
sql
Copy code
SELECT SUBSTRING('Hello World', 1, 5) AS SubStringResult;

This will return Hello.

7. LEN() Function

The LEN() function returns the length of a string.

Syntax:
sql
Copy code
LEN(string)

Example:
sql
Copy code
SELECT LEN('Hello World') AS StringLength;

This will return 11.

8. REPLACE() Function

The REPLACE() function replaces all occurrences of a specified substring with another substring.

Syntax:
sql
Copy code
REPLACE(string, old_substring, new_substring)

Example:
sql
Copy code
SELECT REPLACE('Hello World', 'World', 'SQL') AS ReplacedString;

This will return Hello SQL.

9. CONCAT() Function
The CONCAT() function is used to concatenate two or more strings.

Syntax:
sql
Copy code
CONCAT(string1, string2, ...)

Example:
sql
Copy code
SELECT CONCAT('Hello', ' ', 'World') AS ConcatenatedString;
2.Write and explain the SQL functions (i)Date and Time(ii) Numeric.
ANS:
SQL provides a variety of functions for handling date and time data as well as numeric data.
Below is an overview of some commonly used functions in both categories, along with
explanations and examples.

I. Date and Time Functions

1. GETDATE()

Returns the current date and time.

Syntax:
sql
Copy code
GETDATE()

Example:
sql
Copy code
SELECT GETDATE() AS CurrentDateTime;

This returns the current system date and time.

2. DATEADD()

Adds a specified interval to a date.

Syntax:
sql
Copy code
DATEADD(interval, number, date)

Example:
sql
Copy code
SELECT DATEADD(DAY, 5, GETDATE()) AS NewDate;

This adds 5 days to the current date and returns the new date.

3. DATEDIFF()

Calculates the difference between two dates.

Syntax:
sql
Copy code
DATEDIFF(interval, start_date, end_date)

Example:
sql
Copy code
SELECT DATEDIFF(DAY, '2024-10-01', GETDATE()) AS DaysDifference;

This calculates the number of days between October 1, 2024, and the current date.

4. FORMAT()

Formats a date/time value according to a specified format.

Syntax:
sql
Copy code
FORMAT(value, format)

Example:
sql
Copy code
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS FormattedDate;

This formats the current date as dd/MM/yyyy.

5. YEAR(), MONTH(), DAY()

These functions extract the year, month, and day from a date.

Syntax:
sql
Copy code
YEAR(date)
MONTH(date)
DAY(date)

Example:
sql
Copy code
SELECT YEAR(GETDATE()) AS CurrentYear,
MONTH(GETDATE()) AS CurrentMonth,
DAY(GETDATE()) AS CurrentDay;

This returns the current year, month, and day as separate columns.

6. CONVERT()
Used to convert a date to a specific format.

Syntax:
sql
Copy code
CONVERT(data_type, expression [, style])

Example:
sql
Copy code
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS USFormattedDate;

This converts the current date to a string in the format MM/DD/YYYY.

7. LAST_DAY() (available in MySQL)

Returns the last day of the month for a given date.

Syntax:
sql
Copy code
LAST_DAY(date)

Example:
sql
Copy code
SELECT LAST_DAY('2024-10-17') AS LastDayOfMonth;

This returns the last day of October 2024, which is 2024-10-31.

II. Numeric Functions

1. ABS()

Returns the absolute value of a number.

Syntax:
sql
Copy code
ABS(number)

Example:
sql
Copy code
SELECT ABS(-10) AS AbsoluteValue;

This returns 10.

2. ROUND()

Rounds a numeric value to a specified number of decimal places.

Syntax:
sql
Copy code
ROUND(number, decimal_places)

Example:
sql
Copy code
SELECT ROUND(123.4567, 2) AS RoundedValue;

This returns 123.46.

3. CEILING()

Returns the smallest integer greater than or equal to a specified number.

Syntax:
sql
Copy code
CEILING(number)

Example:
sql
Copy code
SELECT CEILING(123.45) AS CeilingValue;

This returns 124.

4. FLOOR()

Returns the largest integer less than or equal to a specified number.

Syntax:
sql
Copy code
FLOOR(number)

Example:
sql
Copy code
SELECT FLOOR(123.45) AS FloorValue;

This returns 123.

5. POWER()
Raises a number to a specified power.

Syntax:
sql
Copy code
POWER(base, exponent)

Example:
sql
Copy code
SELECT POWER(2, 3) AS PowerValue;

This returns 8 (2 raised to the power of 3).

6. SQRT()

Returns the square root of a specified number.

Syntax:
sql
Copy code
SQRT(number)

Example:
sql
Copy code
SELECT SQRT(16) AS SquareRoot;

This returns 4.

7. SUM()

Calculates the sum of a set of values.

Syntax:
sql
Copy code
SUM(column)

Example:
sql
Copy code
SELECT SUM(Salary) AS TotalSalary FROM Employees;

This returns the total salary from the Employees table.

8. AVG()

Calculates the average of a set of values.

Syntax:
sql
Copy code
AVG(column)

Example:
sql
Copy code
SELECT AVG(Salary) AS AverageSalary FROM Employees;

This returns the average salary from the Employees table.

9. COUNT()

Counts the number of rows that match a specified condition.

Syntax:
sql
Copy code
COUNT(column)

Example:
sql
Copy code
SELECT COUNT(*) AS TotalEmployees FROM Employees;
3. Explain creating and modifying relations in SQL. With examples
ANS:

Creating and modifying relations (or tables) in SQL is fundamental for managing a database.
Here’s a detailed explanation of how to create, modify, and manage these relations.

Creating a Table

To create a table, you use the CREATE TABLE statement. This statement defines the table
structure, including the names and types of columns.

Syntax:
sql
Copy code
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);

Example:
sql
Copy code
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
HireDate DATE,
Salary DECIMAL(10, 2)
);

In this example, we created an Employees table with five columns. The EmployeeID is defined as
the primary key, ensuring that each employee has a unique identifier.

Modifying a Table

You can modify an existing table using the ALTER TABLE statement. This allows you to add,
modify, or delete columns and constraints.

1. Adding a Column

To add a new column to an existing table: Syntax:

sql
Copy code
ALTER TABLE table_name
ADD column_name datatype constraints;

Example:
sql
Copy code
ALTER TABLE Employees
ADD Email VARCHAR(100);

This adds a new column Email to the Employees table.

2. Modifying a Column

To change the datatype or constraints of an existing column: Syntax:

sql
Copy code
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype new_constraints;

Example:
sql
Copy code
ALTER TABLE Employees
MODIFY COLUMN Salary DECIMAL(12, 2);

This changes the Salary column to allow for larger decimal values.

3. Dropping a Column

To remove a column from a table: Syntax:

sql
Copy code
ALTER TABLE table_name
DROP COLUMN column_name;

Example:
sql
Copy code
ALTER TABLE Employees
DROP COLUMN Email;

This removes the Email column from the Employees table.

Example of Complete Flow

Here’s a complete example illustrating creating a table, modifying it, and altering it further:

1. Creating the Table:

sql
Copy code
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
Stock INT
);
2. Adding a New Column:
sql
Copy code
ALTER TABLE Products
ADD Description TEXT;

3. Modifying a Column:
sql
Copy code
ALTER TABLE Products
MODIFY COLUMN Price DECIMAL(15, 2);

4. Dropping a Column:
sql
Copy code
ALTER TABLE Products
DROP COLUMN Stock;
4. Explain about integrity constraints over relations.
ANS:
Integrity constraints are rules that ensure the accuracy and consistency of data in a relational database.
They help maintain the correctness of the data by enforcing specific conditions that the data must satisfy.
Here are the main types of integrity constraints in the context of relational databases:
1. Entity Integrity
 Definition: This constraint ensures that each entity (row) in a table is unique and identifiable.
 Key Points:
o Every table must have a primary key.
o The primary key must contain unique values, and it cannot contain NULL values.
2. Referential Integrity
 Definition: This constraint ensures that relationships between tables remain consistent.
 Key Points:
o Foreign keys are used to create links between tables.
o A foreign key in one table must match a primary key in another table or be NULL. This
prevents orphaned records and ensures valid relationships.
3. Domain Integrity
 Definition: This constraint ensures that all values in a column fall within a defined domain or set
of acceptable values.
 Key Points:
o Each column in a table must have a defined data type (e.g., integer, varchar).
o Constraints such as CHECK can be used to restrict values (e.g., age must be greater than
0).
4. User-Defined Integrity
 Definition: These are specific rules defined by users based on business requirements.
 Key Points:
o They can include complex constraints that might not fit into standard types, like ensuring
that a project start date must be before its end date.
o Implemented using triggers, stored procedures, or application logic.
5. Unique Constraints
 Definition: This constraint ensures that all values in a column or a set of columns are unique
across the table.
 Key Points:
o Unlike the primary key, a table can have multiple unique constraints.
o Columns with unique constraints can accept NULL values, but each NULL is considered
distinct.
6. Check Constraints
 Definition: This constraint allows for the specification of a condition that must be met for values
in a column.
5. Explain domain constraints & key constraints with an example.
ANS:
In the context of databases and relational data models, domain constraints and key constraints are
important concepts used to maintain data integrity. Here's an explanation of both, along with examples:
Domain Constraints
Domain constraints define the allowable values for an attribute (or column) in a database table. These
constraints ensure that data entered into a column adheres to specific rules related to its type, format, or
range.

Examples of domain constraints:

 Data Type: An attribute designated as an integer should only accept integer values.
 Range: An attribute for age may be constrained to values between 0 and 120.
 Format: An attribute for email addresses can be constrained to follow a specific pattern (e.g.,
must contain an '@' symbol).
 Non-Null: An attribute must not be empty (NULL).
Example: Consider a table Students with the following attributes:
 StudentID (Integer)
 Name (String)
 Age (Integer, must be between 0 and 120)
 Email (String, must match a specific format)
In this case:
 The Age attribute has a domain constraint that restricts its values to be between 0 and 120.
 The Email attribute might have a domain constraint that requires it to follow a valid email format.
Key Constraints
Key constraints are used to ensure that each record (or row) in a table can be uniquely identified. A key
constraint can be a primary key or a unique key:
 Primary Key: This constraint ensures that a column (or a combination of columns) has unique
values and cannot contain NULL. It uniquely identifies each row in a table.
 Unique Key: This constraint also ensures that the values in a column (or a combination of
columns) are unique but can contain NULL values.
Example: Using the same Students table, we can set constraints as follows:
 StudentID is defined as the primary key, ensuring that each student has a unique identifier that
cannot be NULL.
 An attribute like Email can have a unique constraint, ensuring that no two students can have the
same email address, but it can allow for NULL values if a student does not provide an email.
Summary Example of a Table
Here's how the Students table might be defined with these constraints:

StudentID (Primary Key) Name Age (0-120) Email (Unique)

1 Alice 20 alice@example.com
2 Bob 22 bob@example.com
3 Charlie 25 NULL
In this example:
 The StudentID column uniquely identifies each student (primary key).
  The Age column is constrained to be between 0 and 120.
 The Email column must be unique, ensuring no duplicates (unique key).
By enforcing these domain and key constraints, the database maintains data integrity and consistency.
6. What is the importance of null values? Explain comparison of using null values and not
using null values with examples.
ANS:
Null values play a crucial role in data management and programming. They represent the absence of a
value or an unknown value, which can significantly affect how data is interpreted, stored, and processed.
Here’s a breakdown of their importance and a comparison of scenarios with and without null values.
Importance of Null Values
1. Indicating Missing Data: Null values are essential for representing missing, incomplete, or
unknown data. This is common in databases and data analytics, where not all data points may be
available.
2. Data Integrity: Allowing null values can enhance data integrity by explicitly acknowledging that
certain fields may not have data without forcing invalid entries (like zero or empty strings).
3. Statistical Analysis: In statistical analyses, null values can signify that a particular data point
should be excluded from calculations, thereby ensuring accurate results.
4. Default States: In programming, null can represent an uninitialized state, which can help prevent
errors associated with default or placeholder values.
Comparison: Using Null Values vs. Not Using Null Values
Example Scenario: Customer Database
Consider a customer database with the following columns: CustomerID, Name, Email, Phone, and
Address.
1. Using Null Values
o Table Structure:
CustomerID Name Email Phone Address
1 John Doe john@example.com NULL NULL
2 Jane Smith jane@example.com 123-456-7890 123 Main St
3 NULL NULL NULL NULL
o Interpretation:
 The NULL entries for Phone and Address indicate that the information is
missing. The NULL for Name shows that the customer ID 3 does not have an
associated name.
 Queries can be easily crafted to find all customers without phone numbers or
addresses.
o Benefits:
 Accurate representation of data: It’s clear which fields have no data.
 Easier data management: Specific queries can filter based on nulls, such as
SELECT * FROM Customers WHERE Phone IS NULL;.
2. Not Using Null Values
o Table Structure:

CustomerID Name Email Phone Address

1 John Doe john@example.com "N/A" "N/A"
2 Jane Smith jane@example.com 123-456-7890 123 Main St
 CustomerID Name Email Phone Address
3 "Unknown" "Unknown" "N/A" "N/A"
o Interpretation:
 The entries "N/A" and "Unknown" are used to indicate missing data.
 This can lead to ambiguity: it’s not clear if "Unknown" means the data is
missing, or if it is a valid entry.
o Drawbacks:
 Data misinterpretation: It may confuse users or systems as to whether "N/A" is a
valid entry or an indicator of missing information.
 Complicated filtering: Queries become more complex as they have to filter out
multiple placeholder values. For example, to find missing phone numbers, the
query would be SELECT * FROM Customers WHERE Phone = 'N/A';.

7. Explain with examples, different DDL and DML operations in SQL.

ANS:
In SQL (Structured Query Language), operations are typically categorized into two main types:
Data Definition Language (DDL) and Data Manipulation Language (DML). Here’s a
breakdown of each category along with examples:

Data Definition Language (DDL)

DDL commands are used to define, modify, and manage the structure of database objects such as
tables, indexes, and schemas. Common DDL operations include:

1. CREATE: Used to create a new database object.

o Example: Creating a new table called Employees.

sql
Copy code
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
BirthDate DATE,
HireDate DATE
);

2. ALTER: Used to modify an existing database object.

o Example: Adding a new column Email to the Employees table.

sql
Copy code
ALTER TABLE Employees
ADD Email VARCHAR(100);

3. DROP: Used to delete a database object.

o Example: Deleting the Employees table.

sql
Copy code
DROP TABLE Employees;

4. TRUNCATE: Used to remove all records from a table without logging individual row
deletions. This operation is faster than DELETE but cannot be rolled back.
o Example: Truncating the Employees table.

sql
Copy code
TRUNCATE TABLE Employees;

5. RENAME: Used to rename an existing database object.

o Example: Renaming the Employees table to Staff.

sql
 Copy code
RENAME TABLE Employees TO Staff;

Data Manipulation Language (DML)

DML commands are used to manipulate data within existing database objects. Common DML
operations include:

1. INSERT: Used to add new records to a table.

o Example: Inserting a new employee record into the Employees table.

sql
Copy code
INSERT INTO Employees (EmployeeID, FirstName, LastName, BirthDate, HireDate, Email)
VALUES (1, 'John', 'Doe', '1985-06-15', '2023-01-10', 'john.doe@example.com');

2. UPDATE: Used to modify existing records in a table.

o Example: Updating the email address of an employee with EmployeeID 1.

sql
Copy code
UPDATE Employees
SET Email = 'john.new@example.com'
WHERE EmployeeID = 1;

3. DELETE: Used to remove existing records from a table.

o Example: Deleting the employee record with EmployeeID 1.

sql
Copy code
DELETE FROM Employees
WHERE EmployeeID = 1;

4. SELECT: Although primarily used to query data, it’s also considered part of DML as it
retrieves data from a table.
o Example: Selecting all employees from the Employees table.

sql
Copy code
SELECT * FROM Employees;

8. Discuss about arithmetic & logical operations in SQL with examples.

ANS:
Arithmetic and logical operations in SQL are fundamental for manipulating data, allowing you to
perform calculations and control the flow of data. Let’s dive into both types of operations with
examples.

Arithmetic Operations

Arithmetic operations are used to perform mathematical calculations. The main arithmetic
operators in SQL are:

 Addition (+): Adds two values.

 Subtraction (-): Subtracts one value from another.
 Multiplication (*): Multiplies two values.
 Division (/): Divides one value by another.
 Modulus (%): Returns the remainder of a division operation.

Example of Arithmetic Operations

Assume we have a table named Sales with the following columns:

Item Quantity Price

Apples 10 2.00
Bananas 20 1.50
Oranges 15 1.75

1. Total Sales Value for Each Item

sql
Copy code
SELECT Item, Quantity, Price,
(Quantity * Price) AS TotalSales
FROM Sales;

Result:
 Item Quantity Price TotalSales
Apples 10 2.00 20.00
Bananas 20 1.50 30.00
Oranges 15 1.75 26.25

2. Calculate Total Quantity of All Items

sql
Copy code
SELECT SUM(Quantity) AS TotalQuantity
FROM Sales;

Result:

TotalQuantity
45

Logical Operations

Logical operations allow you to evaluate conditions and return boolean values (TRUE, FALSE, or
UNKNOWN). The main logical operators are:

 AND: Returns TRUE if both conditions are true.

 OR: Returns TRUE if at least one of the conditions is true.
 NOT: Reverses the boolean value of the condition.

Example of Logical Operations

Continuing with the Sales table:

1. Find Items with Quantity Greater than 15 and Price Less than 2.00

sql
Copy code
SELECT Item, Quantity, Price
FROM Sales
WHERE Quantity > 15 AND Price < 2.00;

Result:
 Item Quantity Price
Bananas 20 1.50

2. Find Items that are Either Bananas or Have a Price Greater than 1.50

sql
Copy code
SELECT Item, Quantity, Price
FROM Sales
WHERE Item = 'Bananas' OR Price > 1.50;

Result:

Item Quantity Price

Bananas 20 1.50
Oranges 15 1.75

3. Find Items that are Not Apples

sql
Copy code
SELECT Item, Quantity, Price
FROM Sales
WHERE NOT Item = 'Apples';

Result:

Item Quantity Price

Bananas 20 1.50
Oranges 15 1.75

UNIT-2
2M Q&A
 1. Explain any two TCL commands
2. Give brief description of DCL command.
3. Why does SQL allow duplicate tuples in a table or in a query result?
4. Define primary key. Give example.
5. What is the difference between primary key and foreign key?
6. Define referential integrity.

1. Explain any two TCL commands

ANS:
Tcl (Tool Command Language) is a scripting language that is widely used for rapid prototyping, scripted
applications, and GUIs. Here are two basic commands in Tcl along with their explanations:
1. set
The set command is used to create or modify variables in Tcl. It can also retrieve the value of a variable if
it already exists. The syntax is:
set variableName value
 Example: set x 10
puts $x ;
# This will output: 10
1. Example: the variable x is set to 10, and then its value is printed using the puts command.
2. puts
The puts command is used to output a string to the standard output (usually the console). It can be used to
print messages, variable values, or any string.
2.Example: puts "Hello, World!"
This command will output Hello, World!

2. Give brief description of DCL command.

ANS:
DCL, or Data Control Language, is a subset of SQL (Structured Query Language) used to control access
to data stored in a database. It primarily deals with permissions and security, allowing database
administrators to manage user privileges. The main DCL commands are:

1. GRANT: This command is used to give users specific privileges to perform actions on database
objects (e.g., tables, views).

2. REVOKE: This command removes previously granted privileges from users, restricting their
access to certain database operations.

3. Why does SQL allow duplicate tuples in a table or in a query result?

ANS:
SQL allows duplicate tuples (rows) in a table or in the results of a query due to its design principles,
which prioritize flexibility and practicality for data management. Here are several key reasons why
duplicates are permitted:
1. Relational Model Foundation: The relational model, on which SQL is based, allows for sets of
tuples where duplicates can exist. This is because a relation is defined as a set of tuples, and while
mathematical sets do not allow duplicates, the practical implementation allows for the same tuple
to appear multiple times, especially in the context of unordered data.
2. Data Representation: In many real-world scenarios, duplicate records are meaningful. For
example, a sales table might record multiple transactions for the same customer on the same day.
Each transaction is distinct and important, even if the values are identical.

4. Define primary key. Give example.

ANS:
A primary key is a specific type of database constraint that uniquely identifies each record in a table. It
ensures that no two rows in the table can have the same value for the primary key column(s) and that the
primary key value cannot be NULL. This uniqueness constraint helps maintain data integrity and allows
for efficient data retrieval.
Consider a table named Students that stores information about students in a school. Here’s how it might
look:
StudentID FirstName LastName DateOfBirth
1 John Doe 2000-01-01
2 Jane Smith 1999-05-12
3 Mike Brown 2001-09-22

In this example:
 The primary key is StudentID.
 Each student has a unique StudentID, ensuring that no two students share the same ID, and it
cannot be NULL.

5.What is the difference between primary key and foreign key?

ANS:
A primary key and a foreign key are both fundamental concepts in relational database design, but they
serve different purposes and have distinct characteristics. Here’s a breakdown of their differences:
Primary Key
1. Definition: A primary key is a column or a set of columns that uniquely identifies each record in
a table.
2. Uniqueness: Each value in a primary key column must be unique across the table, meaning no
two rows can have the same primary key value.
Foreign Key
1. Definition: A foreign key is a column or a set of columns in one table that refers to the primary
key in another table.
2. Referential Integrity: A foreign key establishes a relationship between two tables, ensuring that
the value in the foreign key column corresponds to an existing value in the primary key of the