Notes

--SELECT * FROM user_constraints;
--TO show the primary key==SELECT constraint_name from user_constraints;


--Changes location's datatype
ALTER TABLE SITES MODIFY(
location VARCHAR2(40));

--CHANGES
ALTER TABLE TRAINERS MODIFY(
contact_no VARCHAR2(15) NOT NULL);


--TO ADD PRIMARY KEY IN THE SITES TABLE i.e (site_id)
ALTER TABLE SITES
ADD CONSTRAINT pk_sites
PRIMARY KEY (site_id);


-- TO primary key trainers_id in TRAINERS table
ALTER TABLE TRAINERS
ADD CONSTRAINT pk_trainers
PRIMARY KEY (trainer_id);

-- To ADD Primary key Subject_id
ALTER TABLE SUBJECT_AREAS
ADD CONSTRAINT pk_subject_areas
PRIMARY KEY (subject_id);

-- To ADD Primary key students
ALTER TABLE students
ADD CONSTRAINT pk_students
PRIMARY KEY (student_id);

--It Drops date_of_birth column in students table
ALTER TABLE students DROP
(date_of_birth);

--Add DOB column in students table
ALTER TABLE students ADD(
DOB DATE);

--To add car_reg column to trainers table
ALTER TABLE TRAINERS ADD(
car_reg NUMBER(5));


--To Add a unique constraint
ALTER TABLE TRAINERS
ADD CONSTRAINT u_car_reg
UNIQUE (car_reg);

--To Add pk in course table
ALTER TABLE COURSES
ADD CONSTRAINT pk_courses
PRIMARY KEY (course_id);

-- TO add foreign key to courses table
ALTER TABLE COURSES
ADD CONSTRAINTS fk_c_subject_areas
FOREIGN KEY (subject_id)
REFERENCES subject_areas(subject_id );

--ADD primary key
ALTER TABLE COURSE_RUNS
ADD CONSTRAINT pk_course_runs
PRIMARY KEY (running_id);


--ADD FK to course_runs
ALTER TABLE course_runs
ADD CONSTRAINT fk_cr_sites
FOREIGN KEY (site_id)
REFERENCES sites(site_id);

ALTER TABLE course_runs
ADD CONSTRAINT fk_cr_courses
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

ALTER TABLE course_runs
ADD CONSTRAINT fk_cr_trainers
FOREIGN KEY (trainer_id)
REFERENCES trainers(trainer_id);

--ADD primary key
ALTER TABLE attendances
ADD CONSTRAINT pk_attendances
PRIMARY KEY (running_id, student_id);

--ADD FK to attendances
ALTER TABLE attendances
ADD CONSTRAINT fk_a_students
FOREIGN KEY (student_id)
REFERENCES students(student_id);

ALTER TABLE attendances
ADD CONSTRAINT fk_a_course_runs
FOREIGN KEY (running_id)
REFERENCES course_runs(running_id);

--ADD pk in invoices
ALTER TABLE invoices
ADD CONSTRAINT pk_invoices
PRIMARY KEY (invoice_id);



ALTER TABLE invoices
ADD CONSTRAINT fk_i_students
FOREIGN KEY (student_id)
REFERENCES students(student_id);

--Check Constraints
ALTER TABLE students
ADD CONSTRAINT ck_students_fname
CHECK (st_fname = UPPER(st_fname));

ALTER TABLE students
ADD CONSTRAINT ck_students_sname
CHECK (st_sname = UPPER(st_sname));

--Check courses table IN
ALTER TABLE courses
ADD CONSTRAINT ck_courses_description
CHECK (duration IN ('3', '5'));


/*
SELECT constraint_name from user_constraints;
CONSTRAINT_NAME
------------------------------
SYS_C0012258
SYS_C0012254
PK_SITES
PK_TRAINERS
PK_SUBJECT_AREAS
PK_STUDENTS
U_CAR_REG
*/