Tuesday, 10 September 2013

Tools & Tech - Add data to a SQL table, set a constraint

Before we carry on our SQL server training and then onto SQL proper, then start adding data to our tables, there's one last item to set-up. 

In the previous post, Add / Delete Tables from a Database, we set up a Primary Key for our tables. However, it can prove useful to know about the Foreign Key constraint too, used to help ensure data integrity.

What is a Foreign Key?
A Foreign Key is a reference to a Primary Key in another table. It is required all the time but it allows us to relate data in one table to data in another. In our example tables, Grade in the SalesTeam table references data in the GradeID column of the EmpGrade table. The idea being that all employees have a grade, this then allows a cross check to the SalaryMax associated with that grade.

The use of the Foreign Key constraint ensures whatever Grade we enter in the SalesTeam table, matches what are valid grades as given in the EmpGrade table. If not, we'd have no idea what the SalaryMax for an employee should be.

Setting a Foreign Key Constraint (via UI)
To add a Foreign Key via the UI is a bit convoluted to say the least;

  • Right-click on the table name in the Object Explorer window, SalesTeam in this example
  • In the context menu that opens click 'Design', this opens the table designer in the right-hand pane
  • Right-click next to the column name and in the menu that opens select 'Relationships'

In the Foreign Key Relationships window that open click the [Add] button.

You'll see an auto generated ID under 'Selected Relationships' that will need editing in the next step. In the right hand panel click on [...] next to 'Tables and Columns Specification'.

The Primary Key we want to reference is in the EmpGrade table and the column is GradeID, select those in the 'Tables and Columns' window. Ensure SalesTeam is selected as the Foreign Key Table and Grade as the column.

Ensure the 'Relationship name' is set to something meaningful, click [OK] to save and exit the dialogue boxes. If you now expand 'SalesTeam > Columns' in the Object Explorer window, you will see Grade is now marked as 'FK' 

Setting a Foreign Key Constraint (via SQL)
To set the Foreign Key via a SQL statement is much more straightforward, we simply use the 'alter' keyword.

Open a new Query window and enter the following:

   alter table SalesTeam add constraint FK_SalesTeam_Grade
   foreign key (Grade) references EmpGrade(GradeID);

If we read this out-loud we're saying; Alter the table called SalesTeam and add a constraint called FK_SalesTeam_Grade of type foreign key on the column Grade, which references the EmpGrade tables's GradeID column.

Hit F5 to run the statement and make the change.

Adding Data to a Table (via UI)
To add data to our table using the UI;

  • Right-click on the table name in the Object Explorer
  • Select 'Edit Top 200 Rows
  • Enter data into the designer that opens in the right hand panel

To check if your data has saved, open a new query window and execute:

   select * from EmpGrade

Adding Data to a Table (via SQL)
To add data via an SQL statement we need to use the insert keyword and specify what data we want in which columns.

   insert into EmpGrade
   (GradeID, SalaryMax)
   values (1, 10000)

For our SalesTeam table we would need to specify the full list of columns and data, just the same as above.

   insert into SalesTeam
   (EmpID, FirstName, LastName, Location, Grade)
   values (1, 'Andrew', 'Johns','Manchester', 3)

To enter multiple data sets into a table at once, simply repeat the data values:

   insert into SalesTeam
   (EmpID, FirstName, LastName, Location, Grade)

   values (1, 'Andrew', 'Johns','Manchester', 3),
   (2, 'Steven', 'Carre','Baker', 4),
   (3, 'Daniel', 'McCormick','Edinburgh', 2)

We'll leave deleting and altering data to the next post / video, when we look at what useful SQL keywords and statements will be good to know from the tester perspective.

To see the above being run, watch the video below - don't forget to subscribe to the channel!