DML Data Manipulation Language

DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.

DML statements include the following:

INSERT – insert new records
UPDATE – update/Modify existing records
DELETE – delete existing records

DDL Data Definition Language

DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects.

CREATE – create a new Table, database, schema
ALTER – alter existing table, column description
DROP – delete existing objects from database

Create command to create table

create table Employee

(

EmployeeId int,

Username varchar(100)

)

Alter command to add new column

Alter Table Employee

Add password varchar(100) 

Alter command to change column definition

Alter Table Employee

Alter column password varchar(150) not null

Alter command to drop column

Alter Table Employee

Drop column password

Drop command to drop table

drop table Employee

Unique key and default constraint

We use UNIQUE constraint to enforce uniqueness of a column.

Column shouldn't allow any duplicate values.

 

Create table

 

create table Employee

(

EmployeeId int,

Username varchar(100)

)

 

Alter table to add unique key

 

Alter Table Employee

Add Constraint uk_uname Unique(Username)

 

Alter table to drop unique key

 

Alter Table Employee

Drop COnstraint uk_uname

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

create table Employee

(

EmployeeId int,

Name varchar(50),

age int,

salary money

)

ALTER TABLE Employee

ADD CONSTRAINT df_emp_sal default 0.0

ALTER TABLE Employee

drop CONSTRAINT df_emp_sal

IDENITY in sql Server

IDENITY is Auto-increment feature of sql server that allows a unique number to be generated when a new record is inserted into a table.

If a column is marked as an identity column, then the values for this column are automatically generated, when you insert a new row into the table. The following, create table statement marks StudentId as an identity column with seed = 1 and Identity Increment = 1. Seed and Increment values are optional. If you don't specify the identity and seed they both default to 1. 

 

Create Table Student

(

StudentId int Identity(1,1) Primary Key,

Name nvarchar(20)

)

In the statements, we only supply values for Name column and not for StudentId column. 

Insert into Student values (‘vinay’)
Insert into Student values (‘jay’)

If I try to execute the following query you will get following error,

Insert into Student values (1,'karan')

 

Error: - An explicit value for the identity column in table 'Student' can only be specified when a column list is used and IDENTITY_INSERT is ON.

 

 

To set identity off write following statement

 

 

SET Identity_Insert Student OFF

 

To set identity on write following statement

 

SET Identity_Insert Student ON

 

 

If you have deleted all the rows in a table, and you want to reset the identity column value, use DBCC CHECKIDENT command

 

DBCC CHECKIDENT(Student, RESEED, 0)

How to get the last generated identity column value in SQL Server

To retrieve the last identity value that is generated. We can use following built in methods

SCOPE_IDENTITY() :- returns the last identity value that is created in the same session and in the same scope.

@@IDENTITY :- returns the last identity value that is created in the same session and across any scope.

and IDENT_CURRENT('') :- returns the last identity value that is created for a specific table across any session and any scope.

Select SCOPE_IDENTITY()

Select @@IDENTITY

Select IDENT_CURRENT('Student')

 

Applying CHECK constraint and creating rules in SQL server

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.

Create table

create table Employee

(

EmployeeId int,

Name varchar(50),

age int,

salary money

)

Alter table and add check constraint

ALTER TABLE Employee

ADD CONSTRAINT CK_emp_Age CHECK (Age > 0 AND Age < 60)

Alter table to drop constraint

ALTER TABLE Employee

DROP CONSTRAINT CK_emp_Age

Rule - Rules are constraints bound to columns from tables or to user defined data types

CREATE RULE cannot be combined with other Transact-SQL statements in a single batch. Rules do not apply to data already existing in the database at the time the rules are created, and rules cannot be bound to system data types.

A rule can be created only in the current database. After you create a rule, execute sp_bindrule to bind the rule to a column or to alias data type. A rule must be compatible with the column data type. For example, "@value LIKE A%" cannot be used as a rule for a numeric column. A rule cannot be bound to text, ntext, image, varchar (max), nvarchar(max), varbinary(max), xml, CLR user-defined type, or timestamp column. A rule cannot be bound to a computed column.

Create table

create table Employee

(

EmployeeId int,

age int

)

Create rule

CREATE RULE age_rule 

AS  

@age_rule>= 18 AND @age_rule <=60;

Bind rule

sp_bindrule age_rule, 'Employee.age'

Unbind rule

sp_unbindrule 'Employee.age'

Drop rule

DROP RULE age_rule

Difference in constraint and rule

We can bind rules to a datatypes whereas constraints are bound only to columns. This feature enable us to create our own data-type with the help of Rules and get the input according to that.

Rules are reusable so if you make rule one time you can use a rule multiple times to one or more columns.

Foreign key

Foreign key in SQL Server

A foreign key is a way to enforce referential integrity within your SQL Server database.

A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table

The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can be created using either a CREATE TABLE statement or an ALTER TABLE statement.

The foreign key constraint prevents invalid data form being inserted into the foreign key column.

Example 1

CREATE TABLE products

( product_id INT constraint product_pk PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

CREATE TABLE inventory

(

  inventory_id INT constraint inventory_pk  PRIMARY KEY,

  product_id INT NOT NULL,

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product_id FOREIGN KEY (product_id) REFERENCES products (product_id)

);

Example 2

CREATE TABLE products

(

product_name VARCHAR(50) NOT NULL,

  location VARCHAR(50) NOT NULL,

  category VARCHAR(25)

  CONSTRAINT products_pk PRIMARY KEY (product_name, location)

);

CREATE TABLE inventory

(

 inventory_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  location VARCHAR(50) NOT NULL,

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product FOREIGN KEY (product_name, location)

  REFERENCES products (product_name, location)

);

Foreign key - Using ALTER TABLE statement

Example 1

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id);

Example 2

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product

    FOREIGN KEY (product_name, location)

    REFERENCES products (product_name, location);

Foreign key with DELETE  Cascade

ON DELETE CASCADE

It specifies that the child data is deleted when the parent data is deleted.

Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.

CREATE TABLE products

( product_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

CREATE TABLE inventory

( inventory_id INT PRIMARY KEY,

  product_id INT NOT NULL,

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON DELETE CASCADE

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON DELETE CASCADE;

Foreign key with UPDATE Cascade

ON UPDATE

It specifies what to do with the child data when the parent data is updated. You have the options of NO ACTION, CASCADE, SET NULL, or SET DEFAULT.

CREATE TABLE products

( product_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

CREATE TABLE inventory

( inventory_id INT PRIMARY KEY,

  product_id INT NOT NULL,

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON update CASCADE

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON update CASCADE;

Foreign key with NO ACTION

NO ACTION

It is used in conjunction with ON DELETE or ON UPDATE. It means that no action is performed with the child data when the parent data is deleted or updated.

This is the default behaviour. No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.

CREATE TABLE products

( product_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

CREATE TABLE inventory

( inventory_id INT PRIMARY KEY,

  product_id INT NOT NULL,

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON update no action

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON update no action;

SET NULL

It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is set to NULL when the parent data is deleted or updated.
Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.

CREATE TABLE products

( product_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

CREATE TABLE inventory

( inventory_id INT PRIMARY KEY,

  product_id INT,

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON DELETE SET NULL

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON DELETE SET NULL;

SET DEFAULT

It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is set to their default values when the parent data is deleted or updated.
Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.

CREATE TABLE products

( product_id INT PRIMARY KEY,

  product_name VARCHAR(50) NOT NULL,

  category VARCHAR(25)

);

CREATE TABLE inventory

( inventory_id INT PRIMARY KEY,

  product_id  INT default(0),

  quantity INT,

  min_level INT,

  max_level INT,

  CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON delete set default

);

By using alter statement

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

    FOREIGN KEY (product_id)

    REFERENCES products (product_id)

    ON  delete set null

        Drop the foreign key

ALTER TABLE inventory

DROP CONSTRAINT fk_inv_product_id;