Microsoft SQL Server Constraints

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database. The following categories of the data integrity exist:

• Entity Integrity
  
• Domain Integrity
  
• Referential integrity
  
• User-Defined Integrity
  

Entity Integrity

Entity integrity ensures that there are no duplicate rows in a table. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint. For example, the ProductID column of the Products table is a primary key for the table.

Domain Integrity

Domain integrity ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including CHECK constraints, UNIQUE constraints, and DEFAULT constraints. These are the constraints we will cover in this article, but be aware there are other options available to enforce domain integrity. Even the selection of the data type for a column enforces domain integrity to some extent. For instance, the selection of datetime for a column data type is more restrictive than a free format varchar field.

Referential Integrity

Referential integrity ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. You can apply referential integrity using a FOREIGN KEY constraint. The ProductID column of the Order Details table has a foreign key constraint applied referencing the Orders table. The constraint prevents an Order Detail record from using a ProductID that does not exist in the database. Also, you cannot remove a row from the Products table if an order detail references the ProductID of the row.

Entity and referential integrity together form key integrity.

User-Defined Integrity

User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.

Each of these categories of the data integrity can be enforced by the appropriate constraints. Microsoft SQL Server supports the following constraints:

• PRIMARY KEY
  
• UNIQUE
  
• FOREIGN KEY
  
• CHECK
  
• NOT NULL
  

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

From a SQL point of view, there are three methods available to add a unique constraint to a table. The first method is to create the constraint inside of CREATE TABLE as a column constraint. A column constraint applies to only a single column. The following SQL will create a unique constraint on a new table: Products_2.

CREATE TABLE Products_2

(

ProductID int PRIMARY KEY,

ProductName nvarchar (40) Constraint IX_ProductName UNIQUE

)


A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

Check constraints contain an expression the database will evaluate when you modify or insert a row. If the expression evaluates to false, the database will not save the row. Building a check constraint is similar to building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. You can also build expressions around AND and OR operators. You can use check constraints to implement business rules, and tighten down the allowed values and formats allowed for a particular column.

CREATE TABLE Products_2

(

ProductID int PRIMARY KEY,

UnitPrice money CHECK(UnitPrice > 0 AND UnitPrice < 100)

)


A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

Using SQL you can use NULL or NOT NULL on a column definition to explicitly set the nullability of a column. In the following example table, the FirstName column will accept NULL values while LastName always requires a non NULL value. Primary key columns require a NOT NULL setting, and default to this setting if not specified.

CREATE TABLE Employees_2

(

EmployeeID int PRIMARY KEY,

FirstName varchar(50) NULL,

LastName varchar(50) NOT NULL,

)


Disabling Constraints

Special situations often arise in database development where it is convenient to temporarily relax the rules. For example, it is often easier to load initial values into a database one table at a time, without worrying with foreign key constraints and checks until all of the tables have finished loading. After the import is complete, you can turn constraint checking back on and know the database is once again protecting the integrity of the data.

Note: The only constraints you can disable are the FOREIGN KEY constraint, and the CHECK constraint. PRIMARY KEY, UNIQUE, and DEFAULT constraints are always active.

Disabling a constraint using SQL is done through the ALTER TABLE command. The following statements disable the CHECK constraint on the UnitsOnOrder column, and the FOREIGN KEY constraint on the CategoryID column.

ALTER TABLE Products NOCHECK CONSTRAINT CK_UnitsOnOrder

ALTER TABLE Products NOCHECK CONSTRAINT FK_Products_Categories

If you need to disable all of the constraints on a table, manually navigating through the interface or writing a SQL command for each constraint may prove to be a laborious process. There is an easy alternative using the ALL keyword, as shown below:

ALTER TABLE Products NOCHECK CONSTRAINT ALL

Joins examples in SQL Server 2005

Joins in SQL Server allows the retrieval of data records from one or more tables having some relation between them. Logical operators can also be used to drill down the number of records to get the desired output from sql join queries.

Inner Join: Inner Join is a default type join of SQL Server. It uses logical operators such as =, >,< to match the records in two tables. Inner Join includes equi join and natural joins.

SQL Inner Join Examples

SELECT C.CATEGORYID, C.CATEGORYNAME, P.PRODUCTID, P.PRODUCTNAME, P.UNITPRICE FROM CATEGORIES C INNER JOINPRODUCTS P ON P.CATEGORYID = C.CATEGORYIDWHERE P.UNITPRICE = 10ORDER BY C.CATEGORYNAME, P.PRODUCTNAME

SQL Inner Natural

This inner join query will return the categoryid, categoryname, productid, productname, unitprice where product unit price = 10

SQL Inner Natural Join Examples

SELECT C.*, P.PRODUCTID, P.PRODUCTNAME FROM CATEGORIES C INNER JOINPRODUCTS P ON P.CATEGORYID = C.CATEGORYID

This natural join query will return all the columns of categories table and prodcutId and productName from products table. You can further modify this natural inner join query as per your requirements to visualize the data by specifying the column names of categories table also.

SQL Inner Equi Join Examples

Inner join is a default type of SQL Join that return the records matching in all the tables joined in sql query satisfying the condition specified in WHERE clause.
Inner join includes 3 types of joins similar to one another.

Self Join: Self join joins a single sql database table to itself. Equi Join: Equi Join returns all the columns from both tables and filters the records satisfying the matching condition specified in Join “ON” statement of sql inner join query.

USE NORTHWIND SELECT * FROM CATEGORIES C INNER JOINPRODUCTS P ON P.CATEGORYID = C.CATEGORYID

Result will display the following columns: CategoryID, CategoryName, Description, Picture, ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued Above equi join sql query will display the categoryId two times in a row because both the tables have categoryId column. You can convert the result into natural join by elimination the identical columns and unnecessary columns.

Outer Join: Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause. Left Outer Join: Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. If in the left table any row has no matching record in the right side table then that row returns null column values for that particular tuple. Inner joins return only those rows from both sql database tables having matching records in both the tables whereas left outer join returns all the rows from the left table and related matching records from the other one.

SQL Left Outer Join Example:

SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAMEFROM AUTHORS A LEFT OUTER JOIN PUBLISHERS PON A.CITY = P.CITYORDER BY A.AU_LNAME, A.AU_FNAME

This left outer join query retrieves the author names and publisher name having same cities. Here all rows retrieved from the left table i.e. authors and publisher name having the similar city other columns of pub_name column are null due to no match found in the right table. Right Outer Join: Right Outer Join is exactly the reverse method of Left Outer Join. It returns all the rows from right table and returns null values for the rows having no match in the left joined table. Just change the left keyword to right outer join in above example; you will get the reverse output of left outer join in the form of right outer join.

SQL Right Outer Join query Example:

SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAMEFROM AUTHORS A RIGHT OUTER JOIN PUBLISHERS PON A.CITY = P.CITYORDER BY A.AU_LNAME, A.AU_FNAME

Full Outer Join: Full outer join returns all the rows from both left and right joined tables. If there is any match missing from the left table then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table. To retrieve all the records from left as well as right table unless the records have matching relations in each row you can use SQL FULL OUTER JOIN. You can consider the examples of last two articles about left outer join and right outer join, in which left outer join retrieves all records from the left table and as all records of right table in right outer join along with null values for the columns having no matching records in any tuple. To retain all the records of left as well as right table along with null values for non matching rows displaying the combination of results of left outer and right outer join, FULL OUTER JOIN is the best solution.

SQL FULL outer join example:

SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAMEFROM AUTHORS A FULL OUTER JOIN PUBLISHERS PON A.CITY = P.CITYORDER BY A.AU_LNAME, A.AU_FNAME

Cross Join: Cross join works as a Cartesian product of rows for both left and right table. It combined each row of left table with all the rows of right table. SQL Cross join returns the output result as a Cartesian product of both database tables. Let left table has 10 rows and right table has 8 rows then SQL CROSS Join will return 180 rows combining each record of left table with all records of right side table.

Consider the following example of CROSS Join:

USE PUBSSELECT AU_FNAME, AU_LNAME, PUB_NAMEFROM AUTHORS CROSS JOIN PUBLISHERS ORDER BY AU_FNAME

Above cross join will return 23 * 8 = 184 results by multiplying each row of authors table with publishers table. SQL CROSS Join with WHERE clause By just adding the where clause with Cross join sql query it turns the output result into inner join.

Call-by-value Vs Call-by-reference in Java

The terms "Call-by-value" semantics and "Call-by-reference" semantics have very precise definitions, and they're often horribly abused when folks talk about Java. I want to correct that... The following is how I'd describe these

Call-by-value
The actual parameter (or argument expression) is fully evaluated and the resulting value is copied into a location being used to hold the formal parameter's value during method/function execution. That location is typically a chunk of memory on the runtime stack for the application (which is how Java handles it), but other languages could choose parameter storage differently.

Call-by-reference
The formal parameter merely acts as an alias for the actual parameter. Anytime the method/function uses the formal parameter (for reading or writing), it is actually using the actual parameter.

When you call a method by reference, the callee sees the caller’s original variables passed as parameters, not copies. References to the callee’s objects are treated the same way. Thus any changes the callee makes to the caller’s variables affect the caller’s original variables. Java never uses call by reference. Java always uses call by value.

How do you fake call by reference in Java, or more precisely, how can a callee influence the values of it’s caller’s variables?

* Use a holder/wrapper object passed to the callee as a parameter. The callee can change the object’s fields. That object may be as simple as an Object[]. In Java, a callee may change the fields of a caller’s object passed as a parameter, but not the caller’s reference to that object. It can’t make the caller’s variable passed as a parameter point to a different object. It can only make its local copy point to a different object. A holder class is just a class with fields to hold your values. It has no methods other than accessors for the fields.

* Have the callee return the new values, perhaps wrapped in an holder class or Object[], and have the caller store them away somewhere.
* Communicate via a static or instance variable visible to both caller and callee.
* Use a delegate object. The callee calls its methods to save the results for the caller.

Dale King points out that attempts to fake call by reference are usually a sign of poor object-oriented design. A function should not be trying to return more than one thing. He uses the term thing because it is proper to return more than one value (e.g. returning a Point that contains two values). If you are trying to return two values, the test he like to apply is whether you can come up with a logical name for the values as a group. If you can’t, you had better look to see if maybe you what you have is really two functions lumped together.

Java is strictly Call-by-value

Serializing and Deserializing Objects

Serializing and Deserializing Objects

The serialization mechanism in Java provides the means for persisting objects beyond a single run of a Java program. To serialize an object, make sure that the declaring class implements the java.io.Serializable interface. Then obtain an ObjectOutputStream to write the object to and call the writeObject() method on the ObjectOutputStream. To deserialize an object, obtain an ObjectInputStream to read the object from and call the readObject() method on the ObjectInputStream. The following code excerpts illustrate how an object is stored in the file by Serialize and getting object from file by Deserialize.

private static String fileName = "./objectholder.ser";

public static void serializeObject(Object obj) {
try {
ObjectOutput out = new ObjectOutputStream(new FileOutputStream(fileName));
out.writeObject(obj);
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}

public static Object deSerializeObject(){
try {
Object obj = null;
File file = new File(fileName);
if(file.exists()) {
ObjectInputStream in = new ObjectInputStream(new FileInputStream(file));
// Deserialize the object
obj = in.readObject();
in.close();
}
return obj;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}

how to call these methods?
//serialize the object
MyObject fileObjs = new MyObject();
serializeObject(fileObjs);

// Deserialize the object
MyObject fileObjs = (MyObject)deSerializeObject();

Where MyObject is class with implements of Serializable interface.

Serialize the object myObject of type MyObject. The file output stream is created for the file named myObject.ser. The object is actually persisted in this file on ,and read the object back from the file objectholder.ser. If you list the files in the directory where this code's .class file is stored, you will see a new file called objectholder.ser added to the listing.