SQLTeam.com | Weblogs | Forums

Constraint Help


#1

Really new to SQL. I'm having some issues figuring out a constraint'

Heres what I want to do. I have a table with 2 columns CampusID and Location. I want a constraint that each element of the campus ID must be UMS followed by two digits. Not sure how to code this


#2

"Each element of the campus ID". I'm not sure what you mean by that. For a single value, the constraint would be:

LIKE 'UMS[0-9]0-9]'


#3

Scott,
This is what I got so far.

CREATE TABLE IF NOT EXISTS Campuses(
CampusID varchar(16) Primary Key,
Location varchar(16) NOT NULL,
CONSTRAINT CampusID Check CampusID LIKE (UMS[0-9]0-9]),

);
INSERT INTO Campuses VALUES ("UMS01","Augusta"), ("UMS02","Farmington"),("UMS03","Fort Kent"),("UMS04","Machais"),("UMS05","Orono"),("UMS06","Presque Isle"),("UMS07","Southern Maine");


#4

You are close. Review this page on the specifics for SQL Server: SQL

You have three specific things that need to be corrected.


#5

James, Thanks for the response. I have read what you asked and have tried a few different thing and still not able to figure it out. I am using SQL Fiddle to attempt this. This is what I'm working with at the moment.

CREATE TABLE IF NOT EXISTS Campuses(
CampusID varchar(16) Primary Key,
Location varchar(16) NOT NULL,
ADD CONSTRAINT CampusID
CHECK CampusID (LIKE "UMS[0-9]0-9]")

);
INSERT INTO Campuses VALUES (“UMS01”,“Augusta”), (“UMS02”,“Farmington”),(“UMS03”,“Fort Kent”),(“UMS04”,“Machais”),(“UMS05”,“Orono”),(“UMS06”,“Presque Isle”),(“UMS07”,“Southern Maine”);


#6

Sorry, I was in a hurry earlier and left out one bracket:

LIKE ‘UMS[0-9][0-9]’

Literals in SQL Server use single quotes ('), not double quotes ("). For dbms's other than SQL Server, you'd need to check that db's documentation to be sure whether to use single or double quotes.


#7

CREATE TABLE IF NOT EXISTS is MySQL syntax, might be for other DBs too of course.

@pyrotaz68 This is a Microsoft SQL Server forum, so you may not find folk here that know the answer to your questions for other flavours of SQL, or the syntax give in answers may not be correct. You might be better off with a MySQL forum?

MS SQL tackles this the other-way-round with a:

DROP TABLE IF EXISTS

construction.

I'm not really sure about the value of CREATE TABLE IF NOT EXISTS - what if the table does exist? Does it have all the right columns? what about conditionally creating all the other elements such as Indexes and FKeys? Seems to me to be a construction that I wouldn't have a use for, but that may well be because all my DDL scripts are MS-centric of course ...


#8

The missing bracket is one of three things I mentioned above. There are two issues remaining regardless of the platform. I will give you a nudge in the right direction. You need to add parenthesis and remove an extra comma. The link above illustrates the syntax and it applies to SQL Server and MySQL.

You are close so have patience and you will get it.


#9

This is what I have at the moment. When I run it in SQL fiddle I get this
ERROR: relation "campuses" does not exist Position: 15

CREATE TABLE IF NOT EXISTS Campuses (
CampusID varchar(16) Primary Key,
Location varchar(16) NOT NULL
CONSTRAINT CampusID Check (CampusID LIKE ('UMS[0-9][0-9]'))
);
INSERT INTO Campuses VALUES (“UMS01”,“Augusta”),(“UMS02”,“Farmington”),(“UMS03”,“Fort Kent”),(“UMS04”,“Machais”),(“UMS05”,“Orono”),(“UMS06”,“Presque Isle”),(“UMS07”,“Southern Maine”);


#10

No idea about MySQL but you may need a comma after that, but its also entirely possible that the CONSTRAINT clause cannot be in the CREATE TABLE statement (in MySQL), but again I have no idea

If we are down to the specific syntax of MySQL you are going to be much better off asking people that know about MySQL


#11

Kristen is right. The comma is causing you grief. The error message happens because it tries to run all statements at once. The table creation is failing meaning that when you try to insert data the table is not there and you get the error "campuses does not exist."

One thing I try to do is execute each statement individually. If you were to execute just the create table statement you would see the error more clearly.

Execute just the create table statement and see what it returns. Then, look into the comma after the Location column and then try again.

We learn best by making mistakes.