Create Table query in SQL Server

Question

I have the following SQL instruction that I am trying to run in SQL Server, but I seem to have the format wrong. Why is it not working?

CREATE TABLE Order
(
    OrderID NUMBER(11, 0) NOT NULL,
    CustomerID NUMBER(11, 0),
    OrderDate DATE DEFAULT SYSDATE,

    CONSTRAINT Order_PK PRIMARY KEY (OrderID),
    CONSTRAINT Order_FK1 
        FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)
);

How does customer look like, especially what type is customerid? — sticky bit
– sticky bit, Commented May 18, 2019 at 16:31
DEFAULT SYSDATE are you sure you are using Microsoft SQL Server, it looks like it is Oracle? — Lukasz Szozda
– Lukasz Szozda, Commented May 18, 2019 at 18:00

forpas · Accepted Answer · 2019-05-18 16:45:09Z

2

Order is a keyword that you must enclose in square brackets.
NUMBER is not a data type for SQL Server, I think you need INTEGER.
Use SYSDATETIME() instead of SYSDATE.
For the reference to Customers I can't tell if the statement is right.

CREATE TABLE [Order]
(
 OrderID INTEGER NOT NULL,
 CustomerID INTEGER,
 OrderDate DATE DEFAULT SYSDATETIME(),
 CONSTRAINT Order_PK PRIMARY KEY (OrderID),
 CONSTRAINT Order_FK1 FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID)
);

answered May 18, 2019 at 16:45

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M. Kanarkowski · Accepted Answer · 2019-05-18 16:43:36Z

0

Please note that CustomerID from Customer must be a PRIMARY KEY.

CREATE TABLE [Order]
(
 OrderID NUMERIC(11, 0) NOT NULL,
 CustomerID NUMERIC(11, 0),
 OrderDate DATE DEFAULT GETDATE(),
 CONSTRAINT Order_PK PRIMARY KEY (OrderID),
 CONSTRAINT Order_FK1 FOREIGN KEY
 (CustomerID)
 REFERENCES Customer (CustomerID)
);

edited May 18, 2019 at 16:43

answered May 18, 2019 at 16:33

M. Kanarkowski

2,20510 silver badges14 bronze badges

5 Comments

Ashwini Soni Over a year ago

Thanks, but it is not running. I can identify that table name can not be order (being a keyword in sql) but what is the another error ?

M. Kanarkowski Over a year ago

I edited the query. Please try again, I forgot that Order is a reserved keyword and must be in square brackets.

Ashwini Soni Over a year ago

Can you please identify error in below query too -CREATE UNIQUE INDEX PVALLEY.OrderLine_PK ON PVALLEY.ORDERLINE (ORDERID, PRODUCTID) LOGGING STORAGE ( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT ) NOPARALLEL; ALTER TABLE PVALLEY.ORDERLINE ADD CONSTRAINT OrderLine_PK PRIMARY KEY (ORDERID, PRODUCTID);

M. Kanarkowski Over a year ago

CREATE UNIQUE INDEX PVALLEY_OrderLine_PK ON ORDERLINE (ORDERID, PRODUCTID) ALTER TABLE ORDERLINE ADD CONSTRAINT OrderLine_PK PRIMARY KEY (ORDERID, PRODUCTID); Please note that ORDERID and PRODUCTID cannot be NULLs.

Ashwini Soni Over a year ago

Actually I need to ask few questions on SQL . Can I text you ?

Collectives™ on Stack Overflow

Create Table query in SQL Server

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related