CREATE TABLE Students(
	idNumber VARCHAR( 6 ) NOT NULL UNIQUE,
	name VARCHAR( 30 ) NOT NULL,
	surname VARCHAR( 35 ) NOT NULL,
	birthDate DATE NOT NULL CHECK (
		birthDate > '1950-01-01' AND birthDate < YEAR(GETDATE()) - 18
	),
	CONSTRAINT pk_students PRIMARY KEY( idNumber )
)

CREATE TABLE Courses (	
	idCourse VARCHAR( 7 ) NOT NULL UNIQUE DEFAULT 'Z00-00x',	
	name VARCHAR( 30 ) NOT NULL,
	CONSTRAINT pk_courses PRIMARY KEY( courseCode )
	);
	
CREATE TABLE Passes(
	idCourse VARCHAR( 7 ) NOT NULL REFERENCES Courses,	
	idNumber VARCHAR( 6 ) NOT NULL REFERENCES Students,
	passDate DATE CHECK (
		SELECT birthDate FROM Studenci WHERE passDate > birthDate AND passDate <= GETDATE()
	),
	grade REAL DEFAULT 5.0 CHECK (
		grade = 2.0 OR 
		grade = 3.0 OR 
		grade = 3.5 OR 
		grade = 4.0 OR 
		grade = 4.5 OR 
		grade = 5.0 OR 
		grade = 5.5
	),
	CONSTRAINT pk_Passes PRIMARY KEY(idCourse, idNumber)
)

quote:

---

Originally posted by DaleTurley

"birthDate < YEAR(GETDATE()) - 18"
Without testing this id say that birthDate < DATEADD(YEAR, -18, GETDATE()) would be better.

---

quote:

---

"UNIQUE DEFAULT 'Z00-00x'" If there is a default value that is the same everytime then it can't be unique - unless the x means and incrementing number???

---