CODEWITHSUNDEEP
sql-server
user4190165
user4190165

Reputation:

SQL Server Error Incorrect Syntax of the 'TABLE' constraint

I have an SQL assignment due for class in the next few coming weeks and I have this one little error that I cant seem to find.

USE master

GO

IF EXISTS (SELECT name
    FROM sysdatabases
    WHERE name = 'travel')
    DROP DATABASE travel
    GO
    CREATE DATABASE travel
GO

USE travel
GO

CREATE TABLE customer
(
    customerID INT,
    lastname VARCHAR(70) NOT NULL,
    firstname VARCHAR(70) NOT NULL,
    phone VARCHAR(10) CONSTRAINT phoneCheck CHECK ((phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
    category VARCHAR(7) NOT NULL CONSTRAINT categoryDefault DEFAULT 'A',
    CONSTRAINT categoryCheck CHECK (category IN ('A', 'B', 'C')),

    CONSTRAINT customerPK
        PRIMARY KEY (customerID)
)

CREATE TABLE package    /*Still need to do the Zero Padding*/
(
    packageCode VARCHAR(6),
    destination VARCHAR(70),
    CONSTRAINT packageCodeCheck CHECK (packageCode LIKE ('YFK%')),
    price MONEY NOT NULL CONSTRAINT priceCheck CHECK ((price BETWEEN 1000 AND 10000)),
    passportRequired VARCHAR(7) NOT NULL CONSTRAINT passportRequiredDefault DEFAULT 'Y',
    CONSTRAINT passportCheck CHECK (passportRequired IN ('Y', 'N')),

    CONSTRAINT packagePK
        PRIMARY KEY (packageCode)
)

CREATE TABLE booking    /*Still need to do the Customer and Package delete*/
(
    customerID VARCHAR(6),
    bookingDate VARCHAR(70) NOT NULL DEFAULT GETDATE(),
    amountPaid MONEY,
    CONSTRAINT amountPaidDefault DEFAULT 0.00,

    CONSTRAINT bookingPK
        PRIMARY KEY (customerID)
)

INSERT INTO customer
(customerID, lastname, firstname, phone, category)
VALUES
(1, 'Douglas', 'Bryan', 1478523690, 'B'),
(2, 'Picard', 'Corey', 1234657890, 'B'),
(3, 'Bond', 'Devon', 0987654321, 'B')


INSERT INTO package
(packageCode, destination, price, passportRequired)
VALUES
('YFK001', 'Orlando', 1000.47, 'Y'),
('YFK002', 'Toronto', 1000.47, 'N')

INSERT INTO booking
(customerID, bookingDate, amountPaid)
VALUES
(2, '01-07-2014', 1000.51)

The execution runs and returns the following error. Msg 142, Level 15, State 2, Line 0

Incorrect syntax for definition of the 'TABLE' constraint.

If someone could help me out that would be awesome.

Thanks, Bryan

Upvotes: 0

Views: 1446

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

Problem is with the booking table. Default has to be in this syntax. CONSTRAINT is an optional keyword that indicates the start of the definition of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint (source MSDN)

CREATE TABLE booking   
(
    customerID VARCHAR(6),
    bookingDate VARCHAR(70) NOT NULL DEFAULT GETDATE(),
    amountPaid MONEY CONSTRAINT amountPaidDefault DEFAULT 0.00,
    CONSTRAINT bookingPK
        PRIMARY KEY (customerID)
)

Upvotes: 2

Paresh J
Paresh J

Reputation: 2419

You have problem with your Booking table. Check the corrected script below. There was an comma after statement amountPaid MONEY which is not required. As we dont use commma with column constraint.

Check the correct script. It is gettign complied succesfully.

CREATE TABLE booking    /*Still need to do the Customer and Package delete*/
(
    customerID VARCHAR(6),
    bookingDate VARCHAR(70) NOT NULL DEFAULT GETDATE(),
    amountPaid MONEY
    CONSTRAINT amountPaidDefault DEFAULT 0.00,

    CONSTRAINT bookingPK
        PRIMARY KEY (customerID)
)

Upvotes: 1

Related Questions