laika222
10/26/2016 - 10:58 PM

Creates table Orders2, which has OrderID as auto-incrementing number. Line 8 makes OrderID the Primary Key, and line 9 creates a Foreign Ke

Creates table Orders2, which has OrderID as auto-incrementing number. Line 8 makes OrderID the Primary Key, and line 9 creates a Foreign Key which resides in another table (Customers2). Therefore, each row in this table must have a value which corresponds to CustomerID column in the Customers2 table. For example, you can not enter an order if there isn't a customer ID that already exists in the Customers2 table (i.e. you can't have an order without a corresponding customer). Line 6 creates a Price column to store currency. The meaning of (19,2) is that 19 numbers are held in the column, of which the following 2 come after the decimal place.

CREATE TABLE with PRIMARY and FOREIGN KEY SS.sql
content_copyfile_download
CREATE TABLE Orders (
    OrderID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(ID) );
    
-- DETAIL!!!

-- Creates table Orders
CREATE TABLE Orders (

-- Creates column OrderID which auto-increments, can't be null, is a Primary Key
    OrderID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

-- Creates OrderNumber, integer, can't be NULL
    OrderNumber int NOT NULL,
    
-- Creates PersonID column, is a Foreign Key which references ID column of Persons table, this column must contain a valid value held in the ID column of the Persons table (i.e. there must be a valid Customer for each Order)
    PersonID int FOREIGN KEY REFERENCES Persons(ID) 
    );
CREATE TABLE with PRIMARY and FOREIGN KEY MS.mysql
content_copyfile_download
CREATE TABLE Orders (
OrderID INT NOT NULL AUTO_INCREMENT,
CustomerID INT NOT NULL,
OrderDate DATE,
Company VARCHAR(30),
Product VARCHAR(30),
Price DECIMAL(19,2) CHECK (Price >= 0),
PromoPoints INT,
RefundCode VARCHAR (10),
Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (OrderID),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);


-- DETAIL!!!

CREATE TABLE Orders (

-- Creates OrderID column, integer, not null, auto-increments
OrderID INT NOT NULL AUTO_INCREMENT,
CustomerID INT NOT NULL,
OrderDate DATE,
Company VARCHAR(30),
Product VARCHAR(30),
Price DECIMAL(19,2),
PromoPoints INT,
RefundCode VARCHAR (10),
Modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-- Assigns Primary Key status to OrderID column created above
PRIMARY KEY (OrderID),

-- Assigns Foreign Key status to CustomerID column created above, references CustomerID column of Customers table, there must be a valid CustomerID from the Customers table entered into this field (i.e. there must be a valid Customer for each Order)
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);