[postgresql] createDB

Viewer

copydownloadembedprintName: createDB
  1. CREATE TABLE student_groups(
  2. group_number SERIAL PRIMARY KEY,
  3. faculty VARCHAR(30) NOT NULL);
  4.  
  5. CREATE TABLE students(
  6. recordBook_number SERIAL PRIMARY KEY,
  7. lastname VARCHAR(30) NOT NULL,
  8. birthYear INTEGER CHECK(birthYear > 1800 AND birthYear < date_part('year', CURRENT_DATE)),
  9. receiptYear INTEGER CHECK(receiptYear > birthYear AND (receiptYear - birthYear) >= 18 AND receiptYear < date_part('year', CURRENT_DATE)),
  10. group_number INTEGER REFERENCES student_groups(group_number) ON DELETE SET NULL ON UPDATE CASCADE);
  11.  
  12. CREATE TABLE exam(
  13. semester INTEGER,
  14. subject VARCHAR(30),
  15. recordBook_number INTEGER REFERENCES students(recordBook_number) ON DELETE CASCADE ON UPDATE CASCADE,
  16. mark INTEGER NOT NULL CHECK(mark >= 0 AND mark <= 10),
  17. PRIMARY KEY(semester, subject, recordBook_number)); 

Editor

You can edit this paste and save as new:


File Description
  • createDB
  • Paste Code
  • 23 May-2020
  • 799 Bytes
You can Share it: