20 lines
699 B
SQL
20 lines
699 B
SQL
-- idempotent script to add students table
|
|
|
|
CREATE TABLE IF NOT EXISTS students (
|
|
id CHAR(36) NOT NULL PRIMARY KEY,
|
|
student_number VARCHAR(50) NOT NULL UNIQUE,
|
|
first_name VARCHAR(100) NOT NULL,
|
|
middle_name VARCHAR(100),
|
|
last_name VARCHAR(100) NOT NULL,
|
|
date_of_birth DATE NOT NULL,
|
|
gender VARCHAR(10) NOT NULL CHECK (gender IN ('Male', 'Female')),
|
|
admission_date DATE NOT NULL,
|
|
student_status VARCHAR(20) NOT NULL DEFAULT 'Active',
|
|
subledger_id CHAR(36) UNIQUE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT fk_subledger
|
|
FOREIGN KEY(subledger_id)
|
|
REFERENCES subledgers(id)
|
|
);
|