Download as PPTX, PDF, TXT or read online from Scribd
Download as pptx, pdf, or txt
You are on page 1/ 15
SQL Constraints
• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table • FOREIGN KEY - Prevents actions that would destroy links between tables • CHECK - Ensures that the values in a column satisfies a specific condition • DEFAULT - Sets a default value for a column if no value is specified • CREATE INDEX - Used to create and retrieve data from the database very quickly SQL Server Data Types Data type Description Max size Storage char(n) Fixed width 8,000 characters Defined width character string varchar(n) Variable width 8,000 characters 2 bytes + number character string of chars
image Variable width binary 2GB
string
int Allows whole numbers 4 bytes
between -2,147,483,648 and 2,147,483,647 bigint Allows whole numbers 8 bytes between - 9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 money Monetary data from - 8 bytes 922,337,203,685,477.5808 to 922,337,203,685,477.5807 float(n) Floating precision number 4 or 8 bytes data from -1.79E + 308 to 1.79E + 308.The n
date Store a date only. From 3 bytes
January 1, 0001 to December 31, 9999 time Store a time only to an 3-5 bytes accuracy of 100 nanoseconds
datetime From January 1, 1753 to 8 bytes
December 31, 9999 with an accuracy of 3.33 milliseconds FOREIGN KEY Constraint The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. FOREIGN KEY Constraint
Defined at either the table level or the column level:
CREATE TABLE employees(
employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL, ... department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE(email)); FOREIGN KEY Constraint: Keywords • FOREIGN KEY: Defines the column in the child table at the table-
constraint level
• REFERENCES: Identifies the table and column in the parent table
• ON DELETE CASCADE: Deletes the dependent rows in the child table
when a row in the parent table is deleted
• ON DELETE SET NULL: Converts dependent foreign key values to null
SQL: ALTER command
ALTER COMMAND IS USED FOR ALTERING THE TABLE STRUCTURE, SUCH AS,
•to add a column to existing table
•to rename any existing column
•to change datatype of any column or to modify its size.
•to drop a column from the table.
ADD new column
Using ALTER command we can add a column to any existing table. Following is the syntax, ALTER TABLE table_name ADD column_name datatype;
Here is an Example for this:
alter table employees add e_address varchar(30) ; TER Command: Add multiple new Columns
Using ALTER command we can even add multiple new columns to any existing table. Here is an Example for this,
alter table employees add
country vachar(20) , contracted int Gender varchar(10) ; change datatype column
To change the data type of a column in a table, use the following
syntax: alter table employees alter COLUMN Lname varchar(100); ALTER TABLE - DROP COLUMN
To delete a column in a table, use the following syntax (notice that
some database systems don't allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name; Example ALTER TABLE Customers DROP COLUMN Email; ALTER Command: Drop a Column ALTER command can also be used to drop or remove columns. Following is the syntax,
Here is an example for this,
ALTER TABLE Persons
DROP COLUMN DateOfBirth; TRUNCATE command TRUNCATE command removes all the records from a table. But this command will not destroy the table's structure. When we use TRUNCATE command on a table its (auto- increment) primary key is also initialized. Following is its syntax,
Here is an example explaining it,
truncate table employees;
The above query will delete all the records from the table student. DROP command DROP command completely removes a table from the database. This command will also destroy the table structure and the data stored in it. Following is its syntax,
Here is an example explaining it,
The above query will drop the database with name Test from the system.
