Specifying Default Value

A default is a value that will be inserted into a column of a table when you do not specify a value for that column. A default value is very useful when you want to ensure that all values are not null. It is also useful when a large percentage of your column contains the same data. To specify a default value, you simply use the DEFAULT keyword, followed by the value assigned to the default for a specific column. You must also enclose character and text defaults within quotes.
For example, if you are storing the state in which every employee lives, and most live in ‘CA’, then you might want to create a default of ‘CA’ in the state column in the employee table. You would use the following code fragment:
In the next lesson, we will cover using identity flags.

State char(2) DEFAULT ‘CA’

A relationship can be used to enforce data integrity. In other words, if you are expecting data in one table because there is data in another, you can place a relationship between these two tables to ensure that no SQL command breaks this rule. However, do not confuse referential integrity with other processes that are associated with maintaining data integrity, such as placing checks or default values on columns to ensure that values for a specific column are valid. Referential integrity revolves around the idea that there are two tables in the database that contain the same information, and it requires that the duplicated data elements are kept consistent. For example, if you have a primary key in one table and a foreign key in another table that have data that matches exactly, it is important that both pieces of data either change together or do not change at all. Relationships are not the only way referential integrity can be enforced; you can also use triggers to ensure that data remains valid .
For instance, the example banking system includes the Customers and Transactions tables. It is not possible to record customer transactions without a customer record. As a result, you have to use referential integrity to enforce data integrity between these two tables so that a customer record cannot be removed from the database while there are customer transaction records for that customer. Similarly, this rule should allow the removal of a customer record when there are no customer transaction records.

Another result of enforcing referential integrity is that it is not possible for a customer transaction to be entered using a customer reference number that does not exist within the Customers table. Instead, to enter a customer transaction in this situation, you first have to create the customer record, and then you can carry out the transaction. Finally, if you had a customer record and related customer transaction records, you could not alter the customer reference number in the customer record without first altering the customer transaction records and checking that the reference you are altering the customer transaction records to already exists.
There are a number of rules to follow if you want to maintain the integrity of your data. If you so desired, you could use referential integrity to enforce data integrity. However, on the other hand you can keep data integrity within a system and not use referential integrity.
Instead, you can create stored procedures or triggers, which are types of programs within SQL Server, to do this task.
Using stored procedures and triggers is a possible but undesirable solution, because it leaves the system open to instances where data integrity is not kept, because of holes within the design of the system or perhaps because a developer does not have the correct processing sequence to ensure that all data are always valid. Not only that, but if someone adds data directly to a table, the referential integrity will be lost. That said, having the data integrity checks in an application does lead to less traffic flow over the network, because all the validation is done on the front end. There is one more important point about referential integrity before I move on to discuss database relationship types: if you want to maintain referential integrity by creating a relationship between two tables, these two tables must be in the same database. It is not possible to have referential integrity between two databases.