Tables, Keys & Relationships

Databases in Visual Studio

Here we will create tables and show how to establish auto-incrementing keys (automatically generate new ID's by adding one to the last key created), and how to create relationships by declaring foreign keys.

To establish 'foreign keys' we must the table with linking primary key must already exist. Assuming you have followed the previous tutorial you will already have 'Site' and 'LocationTypes'. Our next table will store data on the different buildings on a site. As a single site can have many buildings, but any building only belongs to a single site, we call this a one-to-many relationship from Site to Building. The 'many' side of the relationship must have a field that links to the primary key from Site, and this field is known as the foreign key.

Start by creating a new table, in the same way we did previously by right-clicking on the 'Tables' folder in Server Explorer and selecting 'Add New Table'.

Create the fields you see below for 'BuildingID', 'BuildingName', and 'SiteID'. BuildingID will be set as our primary key.

Name the table in the T-SQL section, 'Buildings'.

We must ensure 'SiteID' has exactly the same data type as its counterpart in 'Site', i.e. 'varchar(4)', but its name can be different.

Next, we can right-click on 'Foreign Keys' on the right pane and select 'Add New Foreign Key'.

In the item created below this header we will see 'FK_Buildings_ToTable' and we will want to rename this to 'FK_Buildings_Site'.

Our T-SQL statements should reflect the new name following a 'CONSTRAINT' statement and we must configure other items from here.

The first is the [Column] field which is the foreign key field from our current table, i.e. [SiteID].

The second is the Table, and then the Primary Key from that table that we are linking to, i.e. [Site]([SiteID]).

'Update' the table to commit it to the database.

Be aware of the different ways a primary key may be declared in T-SQL. By default and before the first update Visual Studio will have it stated at the end of the field declaration (below-left). If you close and reopen the table definition you will see the primary key declaration has changed to reflect the image below-right.

Try creating a fourth table, 'Locations', that will have two foreign keys. Locations are rooms, or any space or zones where assets may reside.

A LocationType may be allocated to many Locations, but any Location will only have one LocationType indicating a one-to-many relationship between LocationType and Location. For example a LocationType might be an 'Office' of which there may be many, but the CEO Office or Sales Office will be a specific room.

A Building may hold a number of Location zones, but any one location is unique to a single building. A one-to-many relationship exists between Building and Location where a given building may have 'reception', 'sales office 1', 'sales office 2', 'west corridor', 'north corridor', 'closet', 'stairway' and 'roof'.

The picture shows what our screen may look like at the end, but below are some steps to create the 'Locations' table.

 

Add the fields below, and name the table 'Locations' in T-SQL.

(Foreign keys are covered again soon).

Many primary keys or identifiers may need to be numerical values that increment by 1 each time a new one is added. Invoices often have transaction numbers that increment by 1 with each sale (a requirement of VAT registered organisations in the UK for example).

With our sites, buildings and location types it made sense to have short alpha-numeric primary keys as it is possible users of the system may memorise some of these. There may not want to have to allocate each location with an ID, instead having them be an auto-incrementing whole number.

To achieve this kind of automatically assigned key, use an 'int' data type and we will set its identity property. You don't need to type this as the next step will show how to use the properties pane.

If you do not want to type it straight in T-SQL, then select the primary key in the Design pane then find and expand 'Identity Specification' in the properties pane.

The first item under this is (Is Identity) and you can change this to 'True'.

By default, the Identity Increment (how much the ID will increase by with each new record) and the Identity Seed (the number of the first ID to be assigned) will be set to 1.

Now add our Foreign Keys by creating them first in the right side of the design pane and renaming them to the tables we will link to ('FK_Locations_Buildings' and 'FK_Locations_LocationTypes).

Next set this table's foreign key field and corresponding table and primary key of the other side of the relationship for each constraint.

Well done, we've created two new tables each with foreign keys. We have also used an auto-incrementing primary key for one of those tables.

Databases in Visual Studio