I'm trying to create a SQL table with the following syntax in MySQL Workbench (in Mac OS),
CREATE TABLE orders (
order_id INT NOT NULL PRIMARY KEY,
company_id INT NOT NULL,
customer_id INT NOT NULL,
order_number VARCHAR(200),
order_date TIMESTAMP NOT NULL,
created TIMESTAMP NOT NULL,
order_status INT NOT NULL,
CONSTRAINT orders_fk_company
FOREIGN KEY (company_id) REFERENCES companies (company_id),
CONSTRAINT orders_fk_customer
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
When I'm trying to execute this script, I'm getting the following message,
Error code: 1067. Invalid default value for 'created'
What is the issue here and how to create the table properly ? I have other 2 tables namely companies and customers provided below. There are executing properly and no issue there.
CREATE TABLE companies (
company_id INT NOT NULL PRIMARY KEY,
company_name VARCHAR(50) NOT NULL,
contact_name VARCHAR(50) NOT NULL,
contact_phone VARCHAR(30) NOT NULL,
contact_email VARCHAR(50) NOT NULL,
created DATETIME NOT NULL
);
CREATE TABLE customers (
customer_id INT NOT NULL PRIMARY KEY,
company_id INT NOT NULL,
customer_name VARCHAR(100) NOT NULL,
customer_company VARCHAR(100),
CONSTRAINT customers_fk_company
FOREIGN KEY (company_id) REFERENCES companies (company_id)
);