DB Constraints

1. PRIMARY KEY

·         In Each Table there is only one Primary key will be Accepted.

·         If you need to use more than one column as primary key called Composite Primary key.

·         One table’s Primary key is used as another table’s Foreign Key.

·         If you give a columns as Primary key that column does not allow Null values.

·         If you give a columns as Primary key that column does not allow Duplicate values.

Syntax: 

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> <Column_name>

E.g.: 

Type 1:

            Create table Author (

                        AuthorCode varchar (20) constraint <Constraint_name> Primary key,

                        Name varchar (20)

)

Type 2:           

Create table Author (

                        AuthorCode varchar (20),

                        Name varchar (20)

Primary key (AuthorCode)

)

Type 3:

                        Alter table Author add Primary key (AuthorCode)

NOTE: The Constraint is identified by Specified name (given by you) or default name (given by DB). Names are used for manipulate the Constraints as ALTER, DELETE.

COMPOSITE PRIMARY KEY: 

Type 1:

Create table Author (

                        AuthorCode varchar (20),

                        Name varchar (20)

Primary key (AuthorCode, Name)

)          

Type 2:

                        Alter table Author add Primary key (AuthorCode, Name)

2. FOREIGN KEY 

·         In Each Table there is Multiple Foreign key columns has been accepted.

·         One table’s Primary key is used as another table’s Foreign key.

·         The foreign key datatype and length should be same as primary key column value.

·         If you give a columns as Foreign key that column allow Null values.

·         If you give a columns as Foreign key that column allow Duplicate values.

Syntax:

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> (Column_name) References <Primary key table name> (Column_name)

E.g.: 

Type 1:

            Create table Book (

                        BookName varchar (20),

                        AuthorCode (20) references Author (AuthorCode)

)

Type 2:           

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

Foreign key (AuthorCode) references Author (AuthorCode)

)

Type 3:

                        Alter table Author add Foreign key (AuthorCode) references Author (AuthorCode)

Type 4:

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20),

                        Name varchar (20)

Foreign key (AuthorCode, Name)

)          

Type 5:

Alter table Book add Foreign key (AuthorCode, Name) references Author (AuthorCode, Name) 

3. UNIQUE

·         In Each Table there is Multiple Unique columns has been accepted.

·         If you give a columns as Unique that column allow Null values only once.

·         If you give a columns as Unique that Does Not allow Duplicate values.

Syntax:

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> (Column_name) References <Primary key table name> (Column_name)

E.g.: 

Type 1:

            Create table Book (

                        BookName varchar (20) Unique,

                        AuthorCode varchar (20)

)

Type 2:           

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

Unique (BookName)

)

Type 3:

                        Alter table Author add Unique (AuthorCode)

Type 4:

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20),

                        Name varchar (20)

Unique (AuthorCode, Name)

)          

Type 5:

Alter table Book add Unique (AuthorCode, Name)

4. CHECK 

·         In Each Table there is Multiple Check columns has been accepted.

·         If you give a columns as Check that column you can define a validation.

Syntax:

Alter table <table_name> Add Constraint <Constraint_name> <Constraint_type> constraint

(Column_name_with_condition)

E.g.: 

Type 1:

            Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

                        Subject varchar (20) Check (Subject in (‘Tamil’, ‘English’)),

)

Type 2:           

Create table Book (

                        BookName varchar (20),

                        AuthorCode varchar (20)

Subject varchar (20),

Check (Subject in (‘Tamil’, ’English’)),

)

Type 3:

Alter table Author add Check (Subject in (‘Tamil’, ‘English’)) 

5. NOT NULL

·         In Each Table there is Multiple Not Null columns has been accepted.

·         If you give a columns as Not Null that column does not allow Null values.

·         If you give a columns as Not Null that column allow Duplicate values. 

Syntax:

Alter table <table_name> Alter Column <Column_name> <Datatype> Not Null.

E.g.:

Type 1:

            Create table Book (

                        BookName varchar (20) Not Null,

                        AuthorCode (20) references Author (AuthorCode)

)

Type 2:

                        Alter table Author alter column AuthorCode varchar (30) Not Null

Thanks and Regards, 

Pugal.

What is What? Part-I

WHAT IS DATA?

                Constructed Data are given us the Information. These information used for Report, Analysis before that, Data would be stored in certain place. That is, DATABASE.

E.g.:  My name is Ranjith Kumar from Trichy. And I’m Student.

                Here, ‘Ranjith Kumar, Trichy, Student’ are notable Data.

WHAT IS COLUMNS?

                Column holds the same datatype values like an Array in Oops. Here, column is also known as ‘Attribute’. Which is identified by unique name.

Let me explain with above example:

Name		Location		Occupation
Ranjith Kumar		Trichy		Student

Columns are created when table would be created. We can set the Constraints for each column. And tables allows rename, delete and add new column(s).

WHAT IS ROWS?

                Row is nothing but the different type of Data belong to the same object. And row is called as 

‘Tuple’. The specific row is fetched by unique id. 

For example,

1001 (int)

Ranjith Kumar (text)

000 000 0000 (big int)

100000.00  (decimal)

                Here the Id (Unique id), Name, Phone number and Salary are shown as a row. The rows inserted on the table. And we can update and delete a specific row(s).

WHAT IS TABLE?

                Table contains the rows and columns. For both store and display the Data. In Database, we can create more tables. From the Column and Row examples, table looks like:

Id	Name	Phone Number	Salary
1001	Ranjith	000 000 0000	100000
1002	Pugal	111 111 1111	100000

WHAT IS VIEW?

                View is works like an actual table. Because, it is created from table(s) as select query. So, it is called as ‘virtual table’. We can INSERT, UPDATE, DELETE, when view refers a single table.

In other hand, we can say view as a ‘Window’. Through the window, we can see only the certain area. Generally, which is used for security purpose.

Employee Id	Name	Phone Number	Salary
1	Srinath	000 000 0000	200000
2	Mano	111 111 1111	200000
3	Pugal	222 222 2222	100000
4	Ranjith	333 333 3333	100000

If the red border columns (Employee Id, Name, Salary) are created as a ‘view’ from the whole table, then Accountant login is authorised only for these columns (Phone number is unnecessary). At the same time, Admin login is authorised for all columns.

**NOTE**

Click Here
for

Procedures,Indexes,Cursors,Triggersa

Introduction of SQL

WHAT IS SQL?

SQL is Structured Query Language. Which used for fetching the Data from Relational Database. SQL was designed by Donald D. Chamberlin and Raymond F. Boyce at 80’s.

WHY SQL?

·         Define the Database/ Tables.

·         Fetching the requested Data.

·         Manage the Actual Data.

·         Define the authorised user to the Data.

               

LIST OF SUBLANGUAGES:

1.       Data Definition Language:

It is used for define (create/modify) the SQL objects like Tables, Views.

·         Create

·         Alter

·         Truncate

·         Drop

Syntax: 

                 Create Database <databaseName>

                 Alter table <tableName> …

                Truncate table <tableName>

                Drop Proc <prcedureName>

Example:

                 Create table Number (No int, Word varchar (20))

2.       Data Manipulate Language:

It is used for manage the actual Data. Manage means adding, modify and removing the data.

·         Insert

·         Update

·         Delete

Syntax: 

                  Insert into <tablename> [columnNames…] values (values…..)

                  Update <tableName> set columnName = value [where columnName = value]

    Delete from <tableName> [where columnName = value]

                Example:

                                Insert into Number (No, Word) values (1,’One’)

3.       Data Query Language:

It fetches the Data from the Database.

·         Select

Syntax: 

                 Select <columnNames> from <tablename> [where columnName = value]

                Example:

                            Select * from Number

4.       Data Control Language:

It deals with access of user.

·         Grant

·         Revoke

Syntax: 

                Grant <privilege> on <objectName> to <userName> [with grant option]

                Example:

                            Grant Select, Insert on Number to traineeuser.

                            Revoke Select, Insert on Number from traineeuser.

5.       Transaction Control Language:

It is used for manage the Transactions in Database.

·         Set transaction

·         Savepoint

·         Commit

·         Rollback

Syntax: 

                Savepoint < savepointName >

                Rollback < savepointName >

                Example:

                            Savepoint afterInsert

                            Rollback afterInsert

Note: All above queries are will be explained on following days. So stay tuned.

Thanks and Regards,
Ranjith Kumar S
Trichirappalli
India.