How do you create a one-to-many relationship in a database system?

Answer 1

There are usually two ways in how you can create a one-to-many relationship, either using the Structured Query Language (SQL) or by using a graphical managament tool like the MS Access interface, Sql Server Management Studio (for Sql Server) or Sql Yog (for Mysql).

SQL is the most common way to create a one-to-many relationship in a database.

Create a one-to-many relationship using SQL

CREATE TABLE Customers (

customer_id INT PRIMARY KEY,

first_name VARCHAR(30) NOT NULL,

last_name VARCHAR(30) NOT NULL

)

Then, create another table with a primary key AND a foreign key that references the primary key of the Customers table.

CREATE TABLE Orders(

order_id INT PRIMARY KEY,

order_date DATE NOT NULL,

customer_id INT NOT NULL,

FOREIGN KEY(customer_id) REFERENCES Customers(customer_id)

)

The important part is the FOREIGN KEY declaration in the Orders table. It says that the customer_id in the Orders table is a reference to the customer_id primary key field in the Customers table. Each Order is linked to a (one) Customer in this way and each Customer can be linked to multiple (many) orders, because multiple records in the Orders table can have the same customer_id.

Creating a one-to-many relationship in MS Access

Programs that have a large emphasis on the graphical user interface like Microsoft Access will let you create relationships in a graphical environment. In Microsoft Access you can create a one-to-manyby dragging tables into the relationships screen and dragging the primary key of one table onto the designated foreign key counterpart in another table.

In Access you can get to the Relationships screen by going to the Database tools tab and clicking the Relationships button next.