Heya,
Not sure this is the best place to post but can't see a better forum :P
Not 100% sure I've done this right so looking for some clarification.
Thankies Steph
--------------------------------------------------------------------------------
CreateTables.SQL
DROP TABLE student;
DROP SEQUENCE student_counter;
CREATE SEQUENCE student_counter INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 10;
CREATE TABLE student
(
studentId Number(6),
studentNumber VarChar(12),
firstName VarChar(75),
surname VarChar(75),
dateOfBirth Date,
nationality VarChar(45),
gender Char,
emailAddress VarChar(75)
);-------------------------------------------------------------------------
AddConStraints.SQL
All must be NOT NULL, StudentNumber Unique, StudentID Primary Key, DateofBirth must be before 16 years ago, gender either M or F, and email address must be done as XXX@XXX.
ALTER TABLE student ADD (
MODIFY (studentId CONSTRAINT pk_student PRIMARY KEY (studentId) NOT NULL),
MODIFY (studentNumber CONSTRAINT studentNumber_ck UNIQUE (studentNumber) NOT NULL),
MODIFY (firstName CONSTRAINT firstName_ck NOT NULL),
MODIFY (surname CONSTRAINT surname_ck NOT NULL),
MODIFY (dateOfBirth CONSTRAINT dateOfBirth_ck CHECK (dateOfBirth < (SYSDATE - (365*16))) NOT NULL),
MODIFY (nationality CONSTRAINT nationality_ck NOT NULL),
MODIFY (gender CONSTRAINT gender_ck CHECK (gender IN ('M', 'F')) NOT NULL),
MODIFY (emailAddress CONSTRAINT emailAddress_ck CHECK (emailAddress REGEXP_LIKE(checkemail '.*@.*') NOT NULL),
);----------------------------------------------------------
I'm pretty sure my create tables is right, but don't have a current database outside uni to test my constraints on, so looking for some help ^.^
Thankies again Steph
