Can anyone find what is wrong with this? I think I am using the keyword "Like" correctly. I am trying to enforce some rules by doing this this way. No obvious typos or syntax errors are jumping out at me.
CREATE DATABASE VetClinic
ON
(NAME='VetClinic',
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\VetClinic.mdf',
SIZE = 100MB,
MAXSIZE = 500MB,
FILEGROWTH = 10MB)
LOG ON
(NAME = 'VetClinicLog',
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\VetClinic.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB)
GO
CREATE TABLE Customers
(
CustID int IDENTITY NOT NULL PRIMARY KEY,
FirstName varchar(15) NOT NULL,
LastName varchar(15) NOT NULL,
StreetAddress varchar(25) NOT NULL,
City varchar(20) NOT NULL,
CustState char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Phone1 char(13) NOT NULL
LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",
Phone2 char(13) NULL
LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",
Email varchar(30) NULL,
DateInSystem smalldatetime NOT NULL
DEFAULT GETDATE()
)
GO
CREATE TABLE PetDetails
(
PetID int IDENTITY NOT NULL PRIMARY KEY,
CustID int NOT NULL
FOREIGN KEY REFERENCES Customers(CustID),
PetName varchar(20) NOT NULL,
BreedID int NOT NULL,
Gender char(1) NOT NULL
LIKE "m" OR "f",
Weight decimal NOT NULL,
DOB smalldatetime NULL,
DateFixed smalldatetime NULL,
DateLastSeen smalldatetime NULL,
VetLastSeen int NULL
)
GO
CREATE TABLE Employees
(
EmpID int IDENTITY NOT NULL PRIMARY KEY,
PositionID int NOT NULL
FOREIGN KEY REFERENCES Positions(PositionID),
FirstName varchar(15) NOT NULL,
LastName varchar(15) NOT NULL,
SSN char(11) NOT NULL
LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]",
StreetAddress varchar(25) NOT NULL,
City varchar(20) NOT NULL,
CustState char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Phone1 char(13) NOT NULL
LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",
Phone2 char(13) NULL
LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",
Email varchar(30) NULL,
DateInSystem smalldatetime NOT NULL
DEFAULT GETDATE(),
HireDate smalldatetime NOT NULL,
TerminationDate smalldatetime NOT NULL,
PaySatus char(6) NOT NULL
LIKE "hourly" OR "salary",
PayAmount money NOT NULL
)
GO
CREATE TABLE Appointment
(
ApptID int IDENTITY NOT NULL PRIMARY KEY,
EmpID int NOT NULL,
ApptDate smalldatetime NOT NULL,
ApptTime smalldatetime NOT NULL,
PetID int NOT NULL,
CustID int NOT NULL,
)
GO
CREATE TABLE PetProcedures
(
ProcedureID int IDENTITY NOT NULL PRIMARY KEY,
ProcedureDescrip varchar(20) NOT NULL,
Cost money NOT NULL
)
GO
CREATE TABLE Breed
(
BreedID int IDENTITY NOT NULL PRIMARY KEY,
BreedName varchar(20) NOT NULL
)
GO
CREATE TABLE Prescriptions
(
PrescriptionID int IDENTITY NOT NULL PRIMARY KEY,
PetID int NOT NULL,
MedID int NOT NULL,
EmpID int NOT NULL,
DatePrescribed smalldatetime NOT NULL
)
GO
CREATE TABLE Medications
(
MedID int IDENTITY NOT NULL PRIMARY KEY,
MedName varchar(30) NOT NULL,
MedType varchar(20) NOT NULL,
Dosage varchar(20) NULL,
Frequency varchar(10) NOT NULL
LIKE "daily", "weekly", "monthly" OR "annually"
)
GO
CREATE TABLE Positions
(
PositionID int IDENTITY NOT NULL PRIMARY KEY,
PositionName varchar(20) NOT NULL
LIKE "vet", "tech", "front desk" OR "office manager"
)
- --
Here are the errors that I got
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'LIKE'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'LIKE'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'LIKE'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'LIKE'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'LIKE'.
I will fix the first table, you can do the rest...
Code Snippet
CREATE TABLE Customers
(
CustID int IDENTITY NOT NULL PRIMARY KEY,
FirstName varchar(15) NOT NULL,
LastName varchar(15) NOT NULL,
StreetAddress varchar(25) NOT NULL,
City varchar(20) NOT NULL,
CustState char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Phone1 char(13) NOT NULL
constraint chk1 CHECK (Phone1 LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),
Phone2 char(13) NULL
constraint chk2 CHECK (Phone2 LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),
Email varchar(30) NULL,
DateInSystem smalldatetime NOT NULL
DEFAULT GETDATE()
)
