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
andOrderNumber
toINT
- Made
Orders.CustomerNumber
a foreign key with a relation to theCustomer
table (CustomerNumber
column)