Home > Back-end >  Direct relation in SQL
Direct relation in SQL

Time:11-11

I'm a beginner in SQL and I'm trying to make a small database, but I was asked to make a direct relation between OrderNumber in Orders table and CustomerNumber in Customers table, aren't I using a direct relation in here?.

Here is my code:

Customers table:

CREATE TABLE Customers
(
    CustomerNumber VARCHAR(25) PRIMARY KEY NOT NULL,
    CustomerName   VARCHAR(50),
    Phone          INT NOT NULL,
    Country        VARCHAR(50),
    City           VARCHAR(50),
    State          VARCHAR(50),
    PostalCode     VARCHAR(5) NOT NULL
);

Orders table:

CREATE TABLE Orders
(
    OrderNumber    VARCHAR(25) ,
    CustomerNumber VARCHAR(25) PRIMARY KEY NOT NULL,
    ProductName    VARCHAR(200) NOT NULL UNIQUE,
    OrderDate      DATE NOT NULL,
    requiredDate   DATE NOT NULL,
    Status         VARCHAR(50)
);

CodePudding user response:

I believe, you probably want something like this:

CREATE TABLE Customers
(
    CustomerNumber INT PRIMARY KEY NOT NULL,
    CustomerName   VARCHAR(50),
    Phone          INT NOT NULL,
    Country        VARCHAR(50),
    City           VARCHAR(50),
    State          VARCHAR(50),
    PostalCode     VARCHAR(5) NOT NULL
);

CREATE TABLE Orders
(
    OrderNumber    INT PRIMARY KEY NOT NULL,
    CustomerNumber INT NOT NULL FOREIGN KEY REFERENCES Customers(CustomerNumber),
    ProductName    VARCHAR(200) NOT NULL UNIQUE,
    OrderDate      DATE NOT NULL,
    requiredDate   DATE NOT NULL,
    Status         VARCHAR(50)
);
  • Changed the datatype of CustomerNumber and OrderNumber to INT
  • Made Orders.CustomerNumber a foreign key with a relation to the Customer table (CustomerNumber column)
  • Related