Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Need HELP with Oracle SQL.

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
Celiowin
Asked:
Celiowin
1 Solution
 
Giant2Commented:
At first thinking I  suggest the trigger.
0
 
DaveyEssCommented:
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
 
prashantagarw10Commented:
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
 
srautwarCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now