Solved

Need HELP with Oracle SQL.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mapAB Challlenge 35 123
advertisement module in core php 4 144
Hide vba in gp 7 79
detecting  the added row index in a datagridview 3 54
I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
A short article about problems I had with the new location API and permissions in Marshmallow
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

932 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now