Solved

Need HELP with Oracle SQL.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Turning python script into an applet 12 116
Adoquery sql  left join does not work 25 99
ejb wildfly example 2 27
Restore Windows 7 Snipping Tool function to Windows 10 10 76
This is an explanation of a simple data model to help parse a JSON feed
This is about my first experience with programming Arduino.
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…

828 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