Solved

Need HELP with Oracle SQL.

Posted on 2004-08-26
4
204 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
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

773 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