SQL Server Management Studio Tutorial | Constraints - Primary Key, Foreign Key

 --CREATE DATABASE practice;

--CREATE TABLE employee_tbl(id INT NOT NULL IDENTITY(1,1), name VARCHAR(50) NOT NULL,gender VARCHAR(50) NOT NULL,age INT NOT NULL,salary INT NOT NULL,city VARCHAR(50) NOT NULL);

--INSERT INTO employee_tbl(name,gender,age,salary,city)

--VALUES('Ajay','Male',25,45000,'Ahmedabad'),

--('Parth','Male',28,47000,'Ahmedabad'),

--('Mohit','Male',35,40000,'Ahmedabad'),

--('Tirth','Male',27,20000,'Rajkot'),

--('Pratik','Male',28,22500,'Mumbai'),

--('Sonali','Female',30,15000,'Mumbai'),

--('Jayesh','Male',35,60000,'Ahmedabad'),

--('Monali','Female',40,46000,'Surat'),

--('Rashmi','Female',38,41000,'Surat');

--SELECT * FROM employee_tbl;

--Selected Columns and all rows

--SELECT name,gender,age FROM employee_tbl;

--Selected rows and all columns

--SELECT name,gender,age FROM employee_tbl WHERE id=2;

--SELECT name,age FROM employee_tbl WHERE id=4;

--Eliminating Duplicating Rows

--SELECT DISTINCT * FROM employee_tbl;

--Select Data in a Table

--SELECT * FROM employee_tbl ORDER BY name;

--SELECT * FROM employee_tbl ORDER BY name DESC;

--Delete all Rows

--DELETE FROM employee_tbl;

--Delete specific Row

--DELETE FROM employee_tbl WHERE id = 2;

--Update Records

--Update tablename SET columnName1=value,....WHERE condition;

--UPDATE employee_tbl SET name='Mohan' WHERE id=3;

--Alter table employee_tbl

--ALTER TABLE employee_tbl ADD postalCode VARCHAR(50), StateName VARCHAR(50);

--ALTER TABLE table_name

--ALTER COLUMN column_name column_type;

--ALTER TABLE employee_tbl

--ALTER COLUMN name VARCHAR(50) NOT NULL;

--ALTER TABLE table_name DROP COLUMN column_name;

--Types of Data Constraints

--(1) I/O Constraint: The data constraint determine the speed at which data can be inserted or extracted from a table

--(2) Business rule constraint

--I/O Constraints: Primary Key Constraint, Foreign Key Constraint, Unique Key Constraint, NOT NULL Constraint,Check Constraint

--The Primary Key

--A single Column primary key is called a Simple key.

--A multicolumn primary key is called a Composite primary key.

--------Example of Saled Order Table

--(1) Primary key is a column or set of columns that uniquely identifies a row.Its main purpose is the record uniqueness

--(2)Primary key will not allowed duplicate values.

--(3)Primary key will also not allowed null values.

--(4)primary key is not compulsory but it is recommended.

--(5)Primary key helps to identify one record from another record and also helps in relating tables with one another.

--(6)primary key cannot be long or long raw data type.

--(7)Only one primary key is allowed per table.

--(8)Unique index is created automatically if there is a primary key.

--(9)one table can combine upto 16 columns in a composite primary key.

--primary key constraint defined at column level

--Syntax: <columnName> <DataType>(<size>) Primary Key

--Primary key constraint defined at table level

--Syntax: Primary Key(<ColumnName>,<ColumnName>)

--CREATE TABLE Customer(Customer_No VARCHAR(10) NOT NULL PRIMARY KEY,FirstName VARCHAR(25) NOT NULL,LastName VARCHAR(25) NOT NULL,Contact VARCHAR(15) NOT NULL);

--CREATE TABLE FD(FD_Serial_No VARCHAR(20) NOT NULL,Branch_no VARCHAR(20) NOT NULL,Corp_cust_no VARCHAR(10) NOT NULL,Manager_Sign VARCHAR(10) NOT NULL,

--PRIMARY KEY(FD_Serial_No,Corp_cust_no));

--The Foreign Key Constraint : Foreign keys represent relationships between tables.A foreign key is a column whose values are derived from the primary key or

--unique key of some other table.

--Features of Foreign keys

--(1) Foreign key is a column that references a column of a table and it can be the same table also

--(2)Parent that is being referenced has to be unique or Primary key

--(3)Child may have duplicate and nulls but unless it is specified.

--(4)foreign key constraint can be specified on child but not on parent

--(5)parent record can be delete provided no child record exist

--(6)master table cannot be updated if child record exist

--principles of foreign key/references constraint:

--(1)rejects an insert or update of a value, if a corresponding value does not currently exist in the master key table

--(2)if the on delete cascade option is set, a delete operation in the master table will trigger a delete operation for corresponding records in all detail tables

--(3)if the on delete set null option is set, a delete operation in the master table will set the value held by the foreign key of the detail tables to null

--(4)rejects a delete from the master table if corresponding records in the detail table exist

--(5)must reference a primary key or unique column(s) in primary table

--(6)requires that the foreign key column(s) and the constraint column(s) have matching data types

--(7)can reference the same table named in the create table statement

--Foreign key constraint defined at the column level

--Syntax: <ColumnName> <DataType> (<Size>) References <TableName> [(<ColumnName>)] [ON DELETE CASCADE]

--CREATE TABLE Department(DeptNo INT PRIMARY KEY,DeptName VARCHAR(50));

--CREATE TABLE Employee(EmpNo INT PRIMARY KEY,EmpName VARCHAR(50),DeptNo INT REFERENCES Department);

--Foreign Key Constraint defined at table level

--Syntax: Foreign Key(<ColumnName>,[<ColumnName>]) References <TableName> [(<ColumnName>,<ColumnName>)]

--CREATE TABLE Category(Id INT PRIMARY KEY,Title VARCHAR(100));

--CREATE TABLE product(Id INT PRIMARY KEY,Title VARCHAR(200),Price decimal,CategoryId INT,FOREIGN KEY(CategoryId) REFERENCES Category(Id));

--Foreign key constraints with ON DELETE CASCADE

--CREATE TABLE Courses(Id INT PRIMARY KEY,Title VARCHAR(50));

--CREATE TABLE Students(Id INT,StudentName VARCHAR(100),CourseId INT,Constraint f_CourseId_Students FOREIGN KEY(CourseId) REFERENCES Courses(Id) ON DELETE CASCADE);

--Foreign key constraints with ON DELETE SET NULL

--CREATE TABLE Teacher(Id INT PRIMARY KEY,TeacherName VARCHAR(100));

--CREATE TABLE Student2(Id INT PRIMARY KEY,StudentName VARCHAR(100));

--CREATE TABLE TeacherStudent(Id INT PRIMARY KEY,StudentId INT,TeacherId INT,

--Constraint f_Student FOREIGN KEY(StudentId) REFERENCES Student2(Id) ON DELETE SET NULL,Constraint f_Teacher FOREIGN KEY(TeacherId) REFERENCES Teacher(Id) ON DELETE SET NULL);

--The Unique Key Constraint

--The unique column constraint permits multiple entries of NULL into the column.

--Key Points

--Unique Key will not allow duplicate values

--Unique Index is created automatically

--A table can have more than one Unique key which is not possible in primary key.

--Unique key can combine upto 16 columns in a composite Unique Key.

--Unique Constraint defined at the column level

--<ColumnName> <DataType> (<Size>) UNIQUE

--UNIQUE Constraint defined at the table level

--Create table tableName(<ColumnName1> <DataType>(<Size>), UNIQUE (<ColumnName>));

--CREATE TABLE Student3(Id INT PRIMARY KEY,Enrollment_No INT,ContactNo VARCHAR(15),StudentName VARCHAR(100),UNIQUE(Enrollment_No,ContactNo));

--check constraint

------Business Rules validations can be applied to a table column by using CHECK constraint. CHECK constraints must be specified as a logical expression that 

--evaluates either to true or false.

--Check constraint defined at column level

--Syntax: <ColumnName> <DataType>(<Size>) CHECK(<Logical Expression>)

CREATE TABLE ApplicationUser(Id INT PRIMARY KEY,FNAME VARCHAR(50),Age INT CHECK(Age>18));

--NULL value Concepts

--Often there may be records in a table that do not have values for every fields.because the information is not available at the time of data entry or because

--the field is not applicable in every case.

---------Principles of NULL values

--Setting a NULL value is appropriate when the actual value is unknown,

--or when a value would not be meaningful

--A NULL value is not equivalent to a value of zero if the data type is number and is not equivalent to space if the data type is character

--A NULL value will evaluate to NULL in any expression (e.g NULL Multiplied by 20 is NULL NULL value can be inserted into columns of any data type.)

--Syntax : <ColumnName> <DataType>(<Size>) NOT NULL