Scribd
Upload
19 views3 pages

SQL DBMS Constraints Notes

This document provides quick notes on SQL commands related to altering tables and applying constraints. It details how to add, drop, and modify columns in a table, as well as the types of SQL constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK. Examples are given for creating tables with these constraints and altering them as needed.

Uploaded by

bhavyachhajed2006
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
19 views3 pages

SQL DBMS Constraints Notes

This document provides quick notes on SQL commands related to altering tables and applying constraints. It details how to add, drop, and modify columns in a table, as well as the types of SQL constraints such as PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK. Examples are given for creating tables with these constraints and altering them as needed.

Uploaded by

bhavyachhajed2006
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

SQL & DBMS Internal Exam Quick Notes

ALTER TABLE & SQL Constraints Notes

1. ALTER TABLE

- Add Column:

ALTER TABLE Sales ADD order_date DATETIME;

- Drop Column:

ALTER TABLE Persons DROP COLUMN DateOfBirth;

- Modify Column:

ALTER TABLE Sales ALTER COLUMN customername CHAR(50);

2. SQL Constraints

Constraints apply rules to table data. Types include:

2.1 PRIMARY KEY

- Unique & NOT NULL, only one per table.

- Column Level:

CREATE TABLE Products (

ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,

ProductName VARCHAR(25)

);

- Table Level:

CREATE TABLE Products (

ProductID INT,

ProductName VARCHAR(25),

CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)

);

- Alter to Add:

ALTER TABLE Products ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID);

- Alter to Drop:

ALTER TABLE Products DROP CONSTRAINT pk_products_pid;


SQL & DBMS Internal Exam Quick Notes

2.2 FOREIGN KEY

- Links column to a primary key in another table.

- Column Level:

CREATE TABLE ProductSales (

SalesID INT CONSTRAINT pk_sid PRIMARY KEY,

ProductID INT CONSTRAINT fk_pid FOREIGN KEY REFERENCES Products(ProductID),

SalesPerson VARCHAR(25)

);

- Table Level:

CREATE TABLE ProductSales (

SalesID INT,

ProductID INT,

SalesPerson VARCHAR(25),

CONSTRAINT pk_sid PRIMARY KEY(SalesID),

CONSTRAINT fk_pid FOREIGN KEY(ProductID) REFERENCES Products(ProductID)

);

- Alter to Add:

ALTER TABLE ProductSales ADD CONSTRAINT fk_pid FOREIGN KEY(ProductID) REFERENCES

Products(ProductID);

- Alter to Drop:

ALTER TABLE ProductSales DROP CONSTRAINT fk_pid;

2.3 NOT NULL

- Ensures column cannot have NULL.

CREATE TABLE employee (

id INT,

name CHAR(20) CONSTRAINT nm_nn NOT NULL,

dept CHAR(10),

age INT,

salary INT,
SQL & DBMS Internal Exam Quick Notes

location CHAR(10)

);

2.4 UNIQUE

- Ensures all values in a column are unique.

- Column Level:

location CHAR(10) UNIQUE

- Table Level:

CONSTRAINT loc_un UNIQUE(location)

2.5 CHECK

- Ensures values meet a condition.

- Column Level:

gender CHAR(1) CHECK (gender IN ('M','F'))

- Table Level:

CONSTRAINT gender_ck CHECK (gender IN ('M','F'))

Example Table with Multiple Constraints:

CREATE TABLE employee (

id INT PRIMARY KEY,

name CHAR(20),

dept CHAR(10),

age INT,

gender CHAR(1),

salary INT,

location CHAR(10),

CONSTRAINT gender_ck CHECK (gender IN ('M','F')),

CONSTRAINT loc_un UNIQUE(location)

);

You might also like