DATA
INTEGRITY
Data Integrity means data
validation or data checking process or Type Checking process. Before storing
user supplied information into the table server performs data integrity process
in order to verify whether user supplying valid information or not.
If user supplies valid
information then only it will stored into the table otherwise server raises an error message like
‘Data Type Mismatch’.
We can achieve this Data
Integrity in Three ways
- Data Types
(see previous concepts)
- Constraints
- Triggers
Constraints: Constraint is
nothing but condition on column. If we perform any operation against to
constraint server raises an error message.
OR
It is a mechanism automatically activated
when user performs DML operations on the table.
We can place constraints (with constraint
names or with out constraint names) on columns while creating the table or
after creating the table.
TYPES OF CONSTRAINTS:
1)
Unique Constraint: When we place UNIQUE
constraint on any column ,It will not allow duplicate values but it allows
single null value
2)
Not null Constraint: When we place NOT NULL
constraint on any column , it will not allow any null values. Entering value
for that column is mandatory.
3)
Check Constraint: It is used for evaluating range condition on
numeric columns. It will check values provided for column. Like salary should
greater than 5000 and less than 40000
It is used for evaluating character
comparison conditions on character columns
4)
Primary Key Constraint:
It
is a combination of UNIQUE+ NOT NULL +
CLUSTERED INDEX.
It means when we place PRIMARY KEY constraint
on any column then it will not any duplicate values and it does not accept any
null values mean time the data in that column will be arranged in ascending
order due to CLUSTERED INDEX.
5)
Foreign key Constraint:
a.
Foreign Key must be Primary Key
b.
Foreign Key can accept duplicate values and Null values
c.
Foreign Key has to take the values from its
corresponding Primary Key.
6)
Default Constraint: It is useful to provide
default value into a column when user will not provide any value while
inserting the data into the table then default value will be arranged.
Note:
1.
Only one PRIMARY KEY is allowed per table.
2.
PRIMARY KEY table is called parent table and FOREIGN
KEY table is called child table.
3.
PRIMARY KEY column is KEY COLUMN and the rest of the
columns in the same table are called NON-KEY COLUMNS.
4.
While providing FOREIGN KEY we should have to give the
reference of its corresponding PRIMARY KEY.
5.
one PRIMARY can be placed on more than one column then
that primary is called COMPOSITE PRIMARY KEY
Constraints can be added to table in two
levels
1.
Column Level
2.
Table Level
1.Column
level constraints: Here constraints
are going to placed on columns, after the definition of each and every individual
column and their corresponding data type.
Syntax:
(With out Constraint names)
CREATE
TABLE TABLENAME
(COLUMN
1 DATA TYPE CONSTRAINT TYPE,
COLUMN 2 DATA TYPE CONSTRAINT TYPE,
--------------------------------------------------------)
E.g.:
(DEPTNO INT PRIMARY KEY,
DNAME
VARCHAR (20) UNIQUE,
LOC
VARCHAR (10) DEFAULT ‘HYD’)
E.g.:
(EMPNO
INT PRIMARY KEY,
ENAME
VARCHAR (20) NOT NULL,
SAL MONEY
CHECK (SAL>=1000),
DEPTNO
INT FOREIGN KEY REFERENCES DEPT (DEPTNO)
ON DELETE CASCADE
ON UPDATE CASCADE)
Advantage
of ON DELETE CASCADE:
With out specifying the ON DELETE CASCADE it
is not possible to delete the record in the PARENT table if there are dependent
records from the child table for that record.
ON DELETE CASCADE if used when the record in the PARENT table is deleted
all the dependent records in the child table will be also be deleted.
Advantage
of ON UPDATE CASCADE:
With out specifying the ON UPDATE CASCADE it
is not possible to update the record in the PARENT table if there are dependent
records from the child table for that record.
ON UPDATE CASCADE if used when the record in the PARENT table is updated
all the dependent records in the child table will be also be updated
Syntax:
(With Constraint names)
CREATE
TABLE TABLENAME
(COLUMN
1 DATA TYPE CONSTRAINT CONSTRAINTNAME CONSTRAINT TYPE,
COLUMN
2 DATA TYPE CONSTRAINT CONSTRAINTNAME CONSTRAINT TYPE, --------------------------------------------------------)
E.g.:
CREATE TABLE DEPT
(DEPTNO
INT CONSTRAINT PK PRIMARY KEY,
DNAME
VARCHAR (20) CONSTRAINT UQ UNIQUE,
LOC
VARCHAR (10) CONSTRAINT DF DEFAULT ‘HYD’)
In the above example constraint have been
placed on columns with constraint names like
PK name of Primary Key Constraint on Deptno column
UQ name of Unique Constraint on Dname column
DF name of Default Constraint on Loc column
E.g.:
CREATE TABLE EMP
(EMPNO
INT CONSTRAITN PRK PRIMARY KEY,
ENAME
VARCHAR (20) CONSTRAINT NN NOT NULL,
SAL
MONEY CONSTRAINT CK CHECK (SAL>=1000),
DEPTNO
INT FOREIGN KEY CONSTRAINT FK REFERENCES DEPT (DEPTNO)
ON DELETE CASCADE
ON UPDATE CASCADE)
In the above example constraint have been
placed on columns with constraint names like
PRK name of Primary Key Constraint on Empno
column
NN name of Not Null Constraint on Ename
column
CK name of Check Constraint on Sal column
FK name of Foreign Key Constraint on Deptno
column
2.Table level constraints: Here
constraints are going to be placed on columns after the definition of all
columns and their corresponding data types. It means at the end of the table
definition constraints will be placed on columns.
Note:
In Table Level constraints DEFAULT and NOT NULL constraints are not allowed.
Syntax: (With out Constraint names)
CREATE TABLE TABLENAME
(COLUMN1 DATATYPE, COLUMN2 DATA TYPE,…………….,
CONSTRAINT TYPE (COLUMN1),
CONSTRAINT TYPE (COLUMN2),……………….)
E.g.:
CREATE TABLE DEPT
(DEPTNO INT, DNAME VARCHAR (20), LOC VARCHAR
(20),
PRIMARY KEY (DEPTNO), UNIQUE (DNAME))
E.g.:
CREATE TABLE EMP
(EMPNO INT, ENAME VARCHAR (20), SAL MONEY,
DEPTNO INT,
PRIMARY KEY (EMPNO), CHECK (SAL>=1000),
FOREIGN KEY (DEPTNO) REFERENCES DEPT
(DEPTNO))
Syntax:
(With Constraint names)
CREATE TABLE TABLENAME
(COLUMN1 DATATYPE, COLUMN2 DATA TYPE,…………….,
CONSTRAINT CONSTRAINTNAME CONSTRAINT TYPE
(COLUMN1),
CONSTRAINT CONSTRAINTNAME CONSTRAINT TYPE
(COLUMN2),
…………………………………)
E.g.:
CREATE TABLE DEPT
(DEPTNO INT, DNAME VARCHAR (20), LOC VARCHAR
(20),
CONSTRAINT PK PRIMARY KEY (DEPTNO),
CONSTRAINT UQ UNIQUE (DNAME))
E.g.:
CREATE TABLE EMP
(EMPNO INT, ENAME VARCHAR (20), SAL MONEY,
DEPTNO INT,
CONSTRAINT PRK PRIMARY KEY (EMPNO),
CONSTRAINT CK CHECK (SAL>=1000),
CONSTRAINT FK FOREIGN KEY (DEPTNO) REFERENCES
DEPT (DEPTNO))
Adding
constraints for the existing table with out any constraint names:
We can place constraints on columns after
creating the table
Step1:
Create any table with out any
constraints
E.g.:
CREATE TABLE DEPT
(DEPTNO INT, DNAME VARCHAR (20), LOC VARCHAR
(20))
E.g.:
CREATE TABLE EMP
(EMPNO INT, ENAME VARCHAR (20), SAL MONEY,
DEPTNO INT)
Step2: Make a single column as NOT NULL for which
we want provide Primary Key constraint
Syntax:
ALTER
TABLE TABLENAME ALTER COLUMN COLUMNNAME DATATYPE NOT NULL
Note: if
we want to provide only NOT NULL constraint, then we have to that process in
this step itself.
E.g.:
ALTER
TABLE DEPT ALTER COLUMN DEPTNO INT NOT NULL
E.g.:
ALTER
TABLE EMP ALTER COLUMN EMPNO INT NOT NULL
Step3: Add your required constraints to columns
Syntax:
ALTER
TABLE TABLENAME ADD CONSTRAINTTYPE (COLUMN1),
CONSTRAINTTYPE (COLUMN2),………..
E.g.:
ALTER
TABLE DEPT ADD PRIMARY KEY (DEPTNO),
UNIQUE (DNAME),
DEFAULT
‘HYD’ FOR LOC
E.g.:
ALTER
TABLE EMP ADD PRIMARY KEY (EMPNO),
CHECK (SAL>=1000),
FOREIGN
KEY (DEPTNO) REFERENCES DEPT (DEPTNO)
Adding
constraints for the existing table with constraint names:
Step1 and Step2 are common here also
Step3: Add your required constraints to columns
Syntax:
ALTER
TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE (COLUMN1),
CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE (COLUMN2),………………..
E.g.:
CONSTRAINT UQ UNIQUE (DNAME),
CONSTRAINT
DF DEFAULT ‘HYD’ FOR LOC
E.g.:
ALTER
TABLE EMP ADD CONSTRAINT PRK PRIMARY KEY (EMPNO),
CONSTRAINT CK CHECK
(SAL>=1000),
CONSTRAINT FK FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO)
Dropping The Constraints:
Syntax:
ALTER TABLE TABLENAME DROP CONSTRAINT
CONSTRAINTNAME
E.g.: ALTER TABLE DEPT DROP CONSTRAINT PK
E.g.: ALTER TABLE EMP DROP CONSTRAINT PRK
Note: If you create any table with out any
constraint names then server will arrange the constraint names in its own
format. Those constraint names will be displayed when you execute a stored
procedure SP_HELPCONSTRAINT Table_Name.
SP_HELPCONSTRAINT:
This Stored Procedure is used to display
the description of constraints which have been placed on different columns of a
specific table.
Syntax:
SP_HELPCONSTRAINT Table-Name
Ex:
For i = ubound(s) to 0 step -1
revstring = revstring&"
"&s(i)
Next
msgbox revstring
29 Print the data as a Pascal triangle
30 Join elements of an array as a string
31 Trim a given string from both sides
32 Write a program to insert 100values and to delete 50 values from an array
dim a(100)
For i = 1 to 100
a(i) = i
If a(i) = 51 Then
Exit
for
Erase
a(i)
End If
print a(i)
Next
33 Write a program to force the declaration of variables
34 Write a program to raise an error and print the error number.
35 Finding whether a variable is an Array
Isarray(a) = true ‘if the variable is really an
array’
Isarray(a) = false ‘if the variable is not an
array’
36 Write a program to Convert value into a
currency
37 Write a program to Convert an expression to a date
38 Display current date and Time
39 Find difference between two dates.
40 Add time interval to a date
41 Print current day of the week
42 Convert Date from Indian Format to US format
43 Find whether current month is a long month
44 Find whether given year is a leap year
45 Format Number to specified decimal places
46 Write a program to Generate a Random Number
47 Write a program to show difference between Fix and Int
48 Write a program to find subtype of a variable
49 Write a program to print the decimal part of a given number
50 Write a Function to return a random number
51 Write a Function to add and multiply two numbers
52 Write a Sub Procedure to print “Hello World”
53 Write a Sub Procedure to add and multiply two numbers
54 Write a program to list the Timezone offset from GMT
37 Write a program to Convert an expression to a date
38 Display current date and Time
39 Find difference between two dates.
40 Add time interval to a date
41 Print current day of the week
42 Convert Date from Indian Format to US format
43 Find whether current month is a long month
44 Find whether given year is a leap year
45 Format Number to specified decimal places
46 Write a program to Generate a Random Number
47 Write a program to show difference between Fix and Int
48 Write a program to find subtype of a variable
49 Write a program to print the decimal part of a given number
50 Write a Function to return a random number
51 Write a Function to add and multiply two numbers
52 Write a Sub Procedure to print “Hello World”
53 Write a Sub Procedure to add and multiply two numbers
54 Write a program to list the Timezone offset from GMT
55 what is the difference between ByVal and
ByVal
Function getval(byval x, byref y)
x =
x+1
y =
y+1
print "x="&x&" "&"y="&y
End Function
Dim a: a = 5
dim b: b
= 5
call getval( a, b )
print "a="&a&" "&"b="&b
No comments:
Post a Comment