How to Create a Relational Database

A relational database is a type of table design that promotes data integrity. A relational database is created using tables with primary and foreign keys. These keys link tables so that all information is consistent across the entire database. For instance, a customer may have many orders. The customer's private information is held in a table separate from the order information. A relational database setup with referential integrity prohibits the deletion of a customer record without first deleting the associated orders. This creates data integrity by eliminating orphaned records.

Instructions

1. Create the customer table. In the example with a customer and related orders, the first step to creating a relational database is creating a table with a primary key. In this example, the primary key is the customer ID. The primary key must be unique, which makes an incrementing integer a good candidate.

   create table customer
   (CustomerId int identity (100,1) primary key,
   First_Name varchar (50))

2. Create the order table with a foreign key. This foreign key is the customer identification column created in step 1. This constraint links the two tables.

   create table
   order (OrdierId int, CustomerId int references customer (CustomerId))

3. Test the relationship. An easy way to test that referential integrity has been established is running a delete command on the customer table. Run the following command on the SQL Server.

   delete from customer where customerid=1
   The database returns an error indicating that the delete query cannot be performed due to referential constraints.

References

• Microsoft TechNet