23 SQL – II in MySQL

Hiren Joshi

Objectives

• How to create primary key and foreign key and other constraints
• Write Query using more than one table using join
• Use of Union operators

    Constraints:

Data is important for any database. The data must be consistent in database table. To make data consistent, there is required to restrict inconsistent data input. Constraint is used to restrict inconsistent data.

Constraints are used to enforce the integrity of the data. To maintain integrity of the data by defining rules about the values that can be stored in the columns of the table.

Constraints can be defined at column level or table level.

MySQL support primary key, foreign key, unique and not null constraint. The primary key, foreign key and unique constraint can defined at table level and column level while not null is defined only at column level.

Primary key:

Primary key is used to uniquely identify a record in a table. In case the primary key consists of set of attributes, it is known as composite primary key.

Primary key is used to main entity integrity for a table. Entity integrity ensures that the attribute must be unique within a table and must not null.

The table has primary key defined is referred as master table or parent table.

There must be at most one primary key in any database table.

The syntax of column level primary key constraint is

<Column_name>  < dataType> primary key

Following example, show how to create a table general_ledger_accounts with primary key Accno at column level.

CREATE TABLE general_ledger_accounts

 (

AccNo        INT              PRIMARY KEY,

AccDes  VARCHAR(50)

);

In the above example, primary key defined at column level. To define primary key at table level, the general syntax is:

[constraint [constraintName]] primary key (columnName1 [, columnName2] …)

The table level constraint is defined  after defining all column.

create table accounts2

(accno int,

accdesc varchar(50),

constraint pk_accno primary key(accno)

);

In case , a primary key is consist of more than one attributes (composite key) , it must be defined at table level.

Foreign key:

Foreign key is used to create relationship between tables. Foreign key constraint requires values in one table to match values in another table. Foreign key constraint is used to enforce referential integrity.

The database is relational database because there is a relation can be set between tables of the database.

The relationship between tables is defined by using foreign key.

Referential integrity ensures that the values of foreign key column must have matching primary key values in the related table.

Foreign key is used to provide referential integrity between tables. Referential integrity means an attribute can take only values available in parent table. For instance, considered we have an order table. Then in order table only those customer can be placed order which are available in customer table.

The table in which foreign key is defined is known as child table or detail table.

A table can have zero or more foreign keys.

The foreign key column must have the same data type as the primary key column related to it.

Referential integrity can be violated in following cases.

• Delete a row from the primary key table while the foreign key table contains one or more rows with a matching primary key value.
• Update the value of a primary key while the foreign key table contains one or more rows having a matching value of primary key.
• Insert a row in the foreign key table but the foreign key value does not have a matching primary key value.
• Update a row in the foreign key table but the new (updated) value does not have a matching primary key value.

The on delete cascade statement is used to make referential integrity not violated when primary key value is deleted from primary key table. It deletes all the corresponding record from child table of matching primary key values.

The on update cascade statement is used to make referential integrity not violated when primary key value is updated from primary key table. It updates all the corresponding record foreign key values from child table of matching primary key values.

MySQL only enforces referential integrity if the table is defined using InnoDB storage engine. In case, for a table the storage engine is not InnoDB, then foreign key can be coded but MySQL does not enforce the referential integrity.

To define foreign key constraint at column-level, the general syntax is

references  <tableName>  (columnName1 [, columnName2] …)

[on update <reference option> ]

[on delete <reference option>]

• Reference option can be any one value of the
• {Restrict | cascade | set null | No action}
• Restrict rejects the delete or update operation for the parent table when referential integrity is violated.
• No action and restrict are the same as no action behave the same as restrict. It is also the same as omitting the on delete or on update clause.
• Set null is used to set null value(s) in the child table for the parent table primary key value is  updated  or deleted.

Following example create a column level foreign key.

CREATE TABLE vendors1

(

     Vid   INT              PRIMARY KEY ,

Vname                   VARCHAR(50),

AccNo                    INT         references accounts2(accno)

    );

Be remember that in MySQL though syntactically foreign key can be defined at column level, it does not enforce referential integrity. To enforce referential integrity it must be defined at table level.

To define foreign key constraint at table-level, the general syntax is [Constraint <constraintName> ]

Foreign key (columnName1 [, columnName2] …)

References <tablename>  (columnName1 [, columnName2] …)

[on update {cascade| set null } ]

[on delete {cascade | set null }]

Following example shows table level foreign key.

CREATE TABLE vendors2

(

Vid                     INT              PRIMARY KEY,

Vname                  VARCHAR(50),

AcctNo                      INT,

constraint fk_accno

foreign key (Acctno)

references accounts1(accno)

);

Unique

Unique constraint allows only unique value of the column. It allows null value also.A table can have zero or more columns have unique constraint.Following example shows table level unique constraint.

create table vendors3

(

vid int primary key,

vname varchar(50) unique,

accno int

)

To create constraint at table level.

create table vendors4

(

vid int primary key,

vname varchar(50),

accno int,

constraint uvname unique(vname)

) ;

Not null:

As we know that null is a special value. Null is neither zero nor empty string. Not null constraint can only defined at column level only. A table can have many columns defined as not null. Not null constraint does not allow null value.

create table vendors5

(

vid int primary key,

vname varchar(25) not null

);

Join:

To understand join, let us created following tables and insert values into it. For that you can also import a .sql file through phpmyadmin. The database is created with tables and values are inserted into it.

A join is used to retrieve data from multiple tables. A join is used to combine columns from two or more tables into a single result set. The result set is based on the join conditions specified by query writer.

Join types are classified as I. inner join and II. outer join

Inner join only included those rows in the result set which are satisfied the join condition. The implicit syntax for inner join is shown below.

Select  <column list>

From <tableName1> ,  <tableName2>  [,<tableName3> …]

Where <tableName1. columnName>   <operator>   <tableName2. columnName>

[And <tableName2. columnName>      <operator>    <tableName3. columnName> ] …

The operator can be any relation operator like = , >, >=, <,<= etc. If the operator is = (equal to) then the join is known as equi-join.

select ino, vname from vendors , invoices where vendors.vid = invoices.vid order by ino;

• Tables are generally joined on the relationship between the primary key in one table and foreign key in the other table. However, join can be done on tables based on relationship not defined in the database. These are called ad hoc relationship.
• In case , if the columns included in a join condition have the same name, then to differentiate them write tablename.columnname.

select ino, vname, vendors.vid from vendors , invoices where vendors.vid = invoices.vid order by ino;

• Table alias is an alternative table name assigned in the from clause. Table alias is typically one or two character long. Table alias is used to make SQL statement easier to code and read. If a table alias is assigned , then it must be used throughout the query. You cannot use original table name once alias is used.

      select ino, vname  from vendors v, invoices i  where v.vid = i.vid order by ino;

• An alias can be used for one table in a join without using an alias for another table. select ino, vname from vendors , invoices i where vendors.vid = i.vid
• A join condition can include two or more conditions connected by AND or OR logical operators.

select ino, lineitemamt, lineitemdes

from invoices, invoice_line_items iline

where invoices.iid = iline.iid

and accno = 150;

• A self- join is a join that joins a table to itself. When you code self- join, you must use table aliases and qualify column with <tablename>.<columnName>

select distinct v1.vname, v1.vcity, v1.vstate

from vendors v1, vendors v2

where v1.vcity = v2.vcity and v1.vstate = v2.vstate and v1.vname <> v2.vname;

• You can write query using explicit syntax of join. It is used after SQL-92 standards. SQL-92 standards are defined in 1992. Then SQL – 1999 , SQL – 2003 and SQL-2008 standard are defined. Current version of SQL standard is 2008. The syntax for join using SQL 2003/SQL-2008 is known as explicit syntax. The explicit syntax for join is

Select  <column list>

From <tableName1>  [inner]  join <tableName2>

on <tableName1. columnName>  <operator>   <tableName2. columnName>

[[inner]  join  <tableName2. columnName> <operator>   <tableName3. columnName> ] …

   select ino, vname

from vendors join invoices

on vendors.vid = invoices.vid

order by ino;

You can join more than two tables also.

select vname, ino, idate, lineitemamt, accdes, itotal,paymenttotal,credittotal

from vendors v, invoices i, invoice_line_items il, general_ledger_accounts gl

where v.vid = i.vid

and i.iid = il.iid

and il.accno = gl.accno

and (itotal – paymenttotal – credittotal) >= 0

order by vname, lineitemamt desc;

Outer Join

• An outer join is used to retrieve all rows that satisfied the condition plus unmatched rows from the left or right table.
• The left outer join retrieves all rows that satisfied the condition plus unmatched rows from the left hand side table.
• The right outer join retrieves all rows that satisfied the condition plus unmatched rows from the right hand side table.
• The unmatched rows value are null in the result set.
• The syntax for outer join is

Select  <column list>

From <tableName1> {Left | Right} [outer] join <tableName2>

On join_condition_1

[{Left | Right} [outer] join  <tableName3>

On join_condition_2 ]…

Left Outer Join

select  vname, ino,itotal

from vendors left join invoices

on vendors.vid = invoices.vid

order by vname;

Right Outer Join

select vname,ino,itotal

from vendors right join invoices

on vendors.vid = invoices.vid

order by vname;

• Outer join can be used to join more than two tables.
• Inner join and outer join can combined within a single select statement.

select  ino,itotal

from invoices i  join invoice_line_items  il

on i.iid = il.iid

left join general_ledger_accounts lg

on il.accno = lg.accno

order by ino;

Cross Join

• Cross join joins each row from the first table with each row from the second table. The result set of cross join is also known as Cartesian product.

select ino, vname  from vendors cross join invoices;

Union

• A union is used to combine the result set of two or more select statements into a single result set.
• For union, each result must have the same number of columns.
• Each corresponding columns of the each result must be compatible data type to make union.
• By default, a union eliminates duplicates rows. To include duplicate rows use All keyword after union keyword immediately.
• The final result set includes column name from first select clause only. Column alias assigned by the other select clause have no effect on the final result set.
• To sort the rows in the final result set, code an order by clause after the last select stamen. The order by clause must refer to the column name specified in the first clause.Syntax for union: <Select-statement-1> Union [All] <Select-statement-2> [ union [all] <Select-statement-3> ] … [order by <order-by-list> ]

Following code shows an example of union

select ‘Active’ as source, ino,idate, itotal

from invoices

where idate >= ‘2011-04-10’ union

select ‘paid’ as source, ino,idate,itotal

from invoices

where idate <= ‘2011-04-10’ order by itotal desc;

• Full outer join includes left outer join plus right outer join.
• MySQL does not provide keyword full outer join to implement full outer join. But Left Outer join union right outer join provides full outer joy.

    References:

1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,

2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress

3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,

4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O’Reilly Media

5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox

6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible

7. Joel Murach, Ray Harris: Murach’s PHP and MySQL, Shroff/Murach

8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML/Javascript/DHTML/PHP , BPB Publications

9. Joel Murach, “Murach’s MySQL”, Shroff/Murach

10. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams

11. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education

12. http://www.php.net/

13. http://www.w3schools.com/

14. http://www.tutorialspoint.com/