SQL Tutorial 14 Primary key & Foreign Key

SQL Tutorial 14 SQL Primary key SQL Foreign Key

SQL Tutorial 14 Primary key and SQL Primary key 

 In SQL, a primary key is a field or a combination of fields that uniquely identifies each record in a table. It must contain unique values, and it cannot have NULL values. The primary key is used to enforce entity integrity in a database.

 Sure, let’s dive into SQL primary keys with an example.

Here’s an example:

Let’s say we have a table called “Students” with the following fields:

StudentID (Primary Key)

FirstName

LastName

Age

Major

The “StudentID” field is designated as the primary key. It might look something like this:

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Age INT,

    Major VARCHAR(50)

);

 In this example, the “StudentID” is set as the primary key. It means that each student in the table must have a unique “StudentID” value. Also, the “StudentID” cannot be NULL.

 You can then use this key to uniquely identify and reference each student in other tables or in queries. For instance, if you want to get information about a specific student, you would use their unique “StudentID” to retrieve the data:

SELECT * FROM Students WHERE StudentID = 101;

SQL Foreign Key  with Example

Certainly! In SQL, a foreign key is a field in a table that is used to establish a link between two tables. It creates a relationship between the tables by referencing the primary key of another table. This relationship helps maintain referential integrity in a database.

Let’s take an example using two tables: “Students” and “Courses.”

— Create Students table with StudentID as the Primary Key

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Age INT,

    Major VARCHAR(50)

);

— Create Courses table with CourseID as the Primary Key

CREATE TABLE Courses (

    CourseID INT PRIMARY KEY,

    CourseName VARCHAR(50),

    Instructor VARCHAR(50)

);

Now, let’s establish a relationship between these tables using a foreign key. We’ll add a foreign key in the “Courses” table that references the “StudentID” in the “Students” table.

— Add a foreign key in Courses table referencing StudentID in Students table

ALTER TABLE Courses

ADD COLUMN StudentID INT,

ADD CONSTRAINT FK_StudentID

    FOREIGN KEY (StudentID)

    REFERENCES Students(StudentID);

In this example, the “StudentID” column in the “Courses” table is a foreign key that references the “StudentID” column in the “Students” table.

This foreign key relationship ensures that every value in the “StudentID” column of the “Courses” table must exist in the “StudentID” column of the “Students” table. It helps maintain consistency and integrity between the two tables.

You can then use this relationship to retrieve information related to students and their courses. For example:

— Retrieve all courses taken by a specific student

SELECT *

FROM Courses

WHERE StudentID = 101;

This query would retrieve all the courses associated with the student whose “StudentID” is 101.

SQL Foreign Key with Example

 Sure thing! In SQL, a foreign key is a field or a set of fields in a table that refers to the primary key of another table. This relationship between tables helps maintain referential integrity in a database.

Let’s say we have two tables: orders and customers. The orders table has a foreign key that references the primary key of the customers table.

Here’s an example:

— Create customers table

CREATE TABLE customers (

    customer_id INT PRIMARY KEY,

    customer_name VARCHAR(50)

);

— Create orders table with a foreign key

CREATE TABLE orders (

    order_id INT PRIMARY KEY,

    order_date DATE,

    customer_id INT,

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

In this example:

The customers table has a primary key customer_id.

The orders table has a primary key order_id and a foreign key customer_id that references the customer_id in the customers table.

This setup ensures that every customer_id in the orders table must exist in the customers table.

It establishes a relationship between the two tables, and you can’t insert an order with a customer_id that doesn’t exist in the customers table. This helps maintain data integrity and consistency in the database.

SQL Unique Constraint with Example

Certainly! In SQL, a unique constraint is used to ensure that all values in a column are unique. Unlike the primary key constraint, a unique constraint allows for NULL values, but it enforces that non-NULL values in the column must be unique.

Let’s consider an example with a table called “Employees” that has a unique constraint on the “EmployeeID” column:

— Create Employees table with EmployeeID as a unique constraint

CREATE TABLE Employees (

    EmployeeID INT UNIQUE,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Department VARCHAR(50)

);

In this example, the “EmployeeID” column has a unique constraint, which means that each value in this column must be unique across all rows in the “Employees” table.

You can also add a unique constraint to an existing table using the ALTER TABLE statement:

— Add a unique constraint to an existing column

ALTER TABLE Employees

ADD CONSTRAINT UQ_EmployeeID UNIQUE (EmployeeID);

This ensures that no two employees can have the same “EmployeeID.” However, NULL values are allowed in this column, and multiple rows can have NULL values without violating the unique constraint.

If you try to insert or update a record with a value in the “EmployeeID” column that already exists in another row (excluding NULL values), or if you try to insert multiple NULL values, the unique constraint will be violated, and the operation will fail.

— This would fail due to the unique constraint

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)

VALUES (101, ‘John’, ‘Doe’, ‘HR’);

— This would succeed

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)

VALUES (102, ‘Jane’, ‘Smith’, ‘IT’);

— This would also fail due to the unique constraint

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)

VALUES (101, ‘Bob’, ‘Johnson’, ‘Finance’);

In the last example, the insertion fails because it violates the unique constraint on the “EmployeeID” column.

SQL Create DB with Example

Sure! To create a new database in SQL, you can use the CREATE DATABASE statement. Here’s an example:

— Create a new database named “CompanyDB”

CREATE DATABASE CompanyDB;

This simple SQL statement creates a new database named “CompanyDB.” You can replace “CompanyDB” with your desired database name.

If you want to include additional options, such as specifying the collation or setting the owner, you can do so like this:

— Create a new database with additional options

CREATE DATABASE CompanyDB

    COLLATE Latin1_General_CI_AS,  — Specify collation (case-insensitive, accent-sensitive)

    OWNER = sa;  — Set the owner to the SQL Server login “sa”

— Create a new database with additional options

CREATE DATABASE CompanyDB

    COLLATE Latin1_General_CI_AS,  — Specify collation (case-insensitive, accent-sensitive)

    OWNER = sa;  — Set the owner to the SQL Server login “sa”

Make sure you have the necessary privileges to create databases on your SQL Server instance.

Once the database is created, you can use it by switching to it with the USE statement:

— Use the newly created database

USE CompanyDB;

After switching to the database, you can start creating tables, inserting data, and performing other operations within that specific database.

SQL Drop DB with Example

Dropping a database in SQL is a serious action, as it permanently removes all data and objects associated with the database. Make sure you really want to delete the database before executing the DROP DATABASE statement. Here’s an example:

— Drop (delete) the database named “CompanyDB”

DROP DATABASE CompanyDB;

This SQL statement will delete the database named “CompanyDB.” Replace “CompanyDB” with the name of the database you want to drop.

Make sure to exercise caution when using the DROP DATABASE statement, as it cannot be undone, and all data in the specified database will be permanently lost.

It’s a good practice to backup your database before performing such operations, especially in a production environment.

Leave a Comment

%d bloggers like this: