Solved

Need HELP with Oracle SQL.

Posted on 2004-08-26
4
224 Views
Last Modified: 2010-04-17
CREATE TABLE Student (
SID char(4) not null,
Name varchar(10) not null,
Birthdate DATE NOT NULL,
Email varchar(30));

SID is the student I.D. how can you specify tha it has to be exact 4 characters long, and the first character of the string have to be 'S'?
Email is the email address, how can you make it that te email address have to contain '@' character?

Thank you!
0
Comment
Question by:Celiowin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 12

Expert Comment

by:Giant2
ID: 11903185
At first thinking I  suggest the trigger.
0
 
LVL 4

Expert Comment

by:DaveyEss
ID: 11903429
Yes, put a before insert trigger on the table.  Validate your inputs in the trigger and reject/accept the insert depending upon your validation.
0
 
LVL 5

Expert Comment

by:prashantagarw10
ID: 11904367
you will need to enforce two check constraints as :
alter table student add check (SID like ('S___')); //3 underscores after S
alter table student add check (Email like ('%@%'));

and make sure you have not set @ sign as a escape character. If you have then insert two @@ instead of a single in the command

This will solve it.
0
 

Accepted Solution

by:
srautwar earned 100 total points
ID: 11907618
The constraint defined above will not enforce 4 characters for SID. You need to include the constraint for length and it can be done in only 1 constraint.

ALTER TABLE STUDENR ADD CONSTRAINT CHK_STUDENT_SIDEMAIL (SID LIKE 'S___' AND Email like ('%@%') AND LENGTH(SID) = 4 );
0

Featured Post

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A short article about problems I had with the new location API and permissions in Marshmallow
In this post we will learn different types of Android Layout and some basics of an Android App.
Six Sigma Control Plans
Starting up a Project

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question