Incorrect syntax for definition of the 'TABLE' constraint.

Question

I couldn't figure out the error in this block of code:

CREATE TABLE Booking
(
booking_id VARCHAR(5),
date_of_booking DATE NOT NULL, DEFAULT getDate(),
payment_date DATE DEFAULT null,
payment_amt MONEY DEFAULT 0,
total_rental_amt MONEY DEFAULT 0,
penalty_fee MONEY DEFAULT 0,
refund_amt MONEY DEFAULT 0,
payment_status VARCHAR(10) DEFAULT 'unpaid',
payment_mode VARCHAR(15),
cheque_no VARCHAR(15),
bank VARCHAR(20),
cc_no VARCHAR(19),
u_id VARCHAR(7) NOT NULL
CONSTRAINT booking_pk PRIMARY KEY (booking_id)
)

Error message:

Msg 142, Level 15, State 2, Line 0
Incorrect syntax for definition of the 'TABLE' constraint.

Can someone provide some advice?

Thank you.

Answer 1

Try this
CREATE TABLE Booking(booking_id VARCHAR(5) CONSTRAINT booking_pk PRIMARY KEY,date_of_booking DATE NOT NULL, DEFAULT getDate(),payment_date DATE DEFAULT null,payment_amt MONEY DEFAULT 0,total_rental_amt MONEY DEFAULT 0,penalty_fee MONEY DEFAULT 0,refund_amt MONEY DEFAULT 0,payment_status VARCHAR(10) DEFAULT 'unpaid',payment_mode VARCHAR(15),cheque_no VARCHAR(15),bank VARCHAR(20),cc_no VARCHAR(19),u_id VARCHAR(7) NOT NULL):

---

Many Thanks & Best Regards, Hua Min

Answer 2

It doesn't matter if the CONSTRAINT is placed below the table definition as I've tried to create other tables with that successful.

Answer 3

Problem solved. The problem lies here:

DATE NOT NULL, DEFAULT getDate(),

There's a comma after NOT NULL that has to be removed.

Answer 4

Hi there

There is an extra comma after not null

I hop this will help

Many thanks

CREATE TABLE Booking
(
booking_id VARCHAR(5),
date_of_booking DATE NOT NULL DEFAULT getDate(),
payment_date DATE DEFAULT null,
payment_amt MONEY DEFAULT 0,
total_rental_amt MONEY DEFAULT 0,
penalty_fee MONEY DEFAULT 0,
refund_amt MONEY DEFAULT 0,
payment_status VARCHAR(10) DEFAULT 'unpaid',
payment_mode VARCHAR(15),
cheque_no VARCHAR(15),
bank VARCHAR(20),
cc_no VARCHAR(19),
u_id VARCHAR(7) NOT NULL
CONSTRAINT booking_pk PRIMARY KEY (booking_id)
)