Adding constraints to a table

DAY-7

Adding constraints to a table

SQL Server 2012 allows you to add several constraints to a table. The primary goal of most constraints is data integrity. In other words, their purpose is to improve the validity and consistency of your data. This section covers five constraints: primary key, default, unique, check, and foreign key.

Primary key constraints

Primary key is a column that contains a unique list of values. Often an integer column is added to a table with the identity property and is used as the primary key. However, you can create a primary key from almost any column or combination of columns. The main limitations are that the column cannot allow nulls, the values must be unique, and you can have only one primary key per table. Since you’ve already created two tables, you’ll create primary keys on those tables. Both the Flower and Address tables have ID values that are unique and can be used as primary keys.

Default constraints

Default constraints are perfect when you have a column that typically contains a specific value. A really good candidate for this is a column that has a data type of bit. The bit data type only accepts 1 or 0 (true or false). If you add an Active column to the Flower table that specifies whether an Flower is currently working for the company, the default value will probably be true or 1.Therefore, you should set the default value for that column accordingly.

Unique constraints

Unique constraints are sometimes confused with primary keys. These constraints simply ensure that duplicate values cannot be inserted into the corresponding column. For example, assume that you must add a column for Social Security numbers to the Flower table. Since Social Security numbers are truly unique values, you should add a unique constraint to ensure that a given Social Security number is entered only once.

Check constraints

The final constraint, check, allows you to check the value that is being inserted against logical expres­sions. This constraint is similar to the foreign key column, in that it controls what values are inserted. The foreign key column gets its values from another table, while check constraints use expressions.

Add constraints using SSMS

1.      Ensure that SSMS is open and you are connected to your server.

2.      Expand the Databases folder.

3.      Expand the Thigalla database.

4.      Expand the Tables folder.

5.      Right-click the Flowers. Flower table, and then select Design.

6.      Right-click the Flower ID column, and then select Set Primary Key from the context menu.

7.      Select the Active column.

8.      In the Properties window, locate Default Value or Binding property.

9.      Type 1 as the property value.

10.  In the menu bar, click the Manage Indexes and Keys icon.

11.  Click the Add button in the Indexes/Keys window.

12.  Locate the Name property and type UQ_Flower_SSN as the property value.

13.  Locate the Is Unique property and set the value to Yes.

14.  Locate the Type property and set the value to Unique Key.

15.  Click Close.

16.  In Object Explorer, expand the Flowers .Flower table if it is not already expanded.

17.  Right-click the Constraints column, and then select New Constraint from the context menu.

18.  In the Check Constraint dialog box, change the value for the Name property to CK_Flower_Gender_MF.

19.  Click the Value box for the Expression property, and click the ellipsis that appears.

20.  In the Expression box, enter ([Gender = 'Female' OR [Gender] = 'Male'). Click “CLOSE & SAVE”

Foreign key constraints

The integrity of data is the most important concern in a database. If you allow the insertion of bad data, then that is what is going to come out. Foreign keys play a vital role in enforcing the referen­tial integrity of the database. You may have noticed the Flower ID column in the Address table. To ensure that only Flower IDs that exist in the Flower table are inserted into the Address table, you need to create a foreign key constraint.

Create foreign key constraints using SSMS

1.         Ensure that SSMS is open and you are connected to your server.

2.         Expand the Databases folder.

3.         Expand the THIGALLA database.

4.         Expand the Tables folder.

5.         Expand the Flowers. Address table.

6.         Right-click the Keys folder and select New Foreign Key.

7.          In the Foreign Key Relationships dialog box, locate the Name property and type FK_Flower_To_Address_On_FlowerID as the value.

8.         Click in the text box next to the Table and Columns Specification property.

9.         Click the ellipsis button that appears.

10.      In the Tables and Columns dialog box, select Flower (Flowers) from the Primary Key Table drop-down list.

11.      Select Flower ID from the drop-down list directly below the Primary Key Table drop-down list.

12.      In the drop-down list to the right, select Flower ID. Click ok, close and Save.

Thanks & Regards

Srikanth Goud- Virtualization Administrator