ClassNotes

Class Notes:

We learned about Primary Keys, and that even though we set up a column with a data type of int, and then add the “Is Identity” to auto-increment the int, we still have to set the Primary Key.

When you open a table in “Design” mode, you have an additional set of menu options that control keys, indexes and relationships.

 

The first icon is for the Primary Key, with the column selected in the table design, you can then select the Primary Key to set it. You can also right-click on the column and then select “Set Primary Key”.

The next icon is to view Relationships, tables that are joined together by foreign keys.

The next icon is Manage Keys and Indexes, where you can create unique keys and indexes.

The next (2) icons manage different types of indexes (XML and FullText).

The final icon allows you to create check constraints.

 

We also learned that you can view the created keys through the organized folder structure for the table.

We learned we can set default constraints, by setting an initial default value for a constraint, for instance setting the Active = 1 or setting State = FL. To set the default constraints, you update the column properties.

 

To set the default constraint for Active column, Select the Active column.

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

Type 1 as the property value.

 

To set a Unique constraint for SSN:

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

 

Click the Add button in the Indexes/Keys window.

Locate the Name property and type UQ_Employee_SSN as the property value.

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

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

Click Close.

 

 

We then discussed Foreign Keys and how they are used to join tables with a like column.   In our case we are creating a new Addresses table, and we are going to join CustomersID column back to the Customers table.

 

Create the New Addresses Table

Refresh and Expand the Tables folder.

Expand the Address table.

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

In the Foreign Key Relationships dialog box, locate the Name property and type FK_

Customers_To_Address_On_CustomersID as the value.

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

Click the ellipsis button that appears.

In the Tables and Columns dialog box, select Customers from the Primary Key Table drop-down list.

Select CustomersID from the drop-down list directly below the Primary Key Table drop-down list.

In the drop-down list to the right, select CustomersID.

Click OK.

Click Close.

Click Save.

If a warning window appears, click Yes.

 

Choosing a primary key is one of the most important steps in good database design. A primary key is a table column that serves a special purpose. Each database table needs a primary key because it ensures row-level accessibility. If you choose an appropriate primary key, you can specify a primary key value, which lets you query each table row individually and modify each row without altering other rows in the same table. The values that compose a primary key column are unique; no two values are the same.

Each table has one and only one primary key, which can consist of one or many columns. A concatenated primary key comprises two or more columns. In a single table, you might find several columns, or groups of columns, that might serve as a primary key and are called candidate keys. A table can have more than one candidate key, but only one candidate key can become the primary key for that table.

 

Difference between primary key and unique key:

Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.