“This is the sixth day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

preface

OpenGauss kernel is based on PostgreSQL, and its operation statements are similar to PostgreSQL, using SQL statements.

However, in openGauss1.0.0, foreign keys are not yet supported, so you need to upgrade openGauss to version 2.0.0.

In openGauss2.0.0, the restart operation is supported, and the files and directories are named db.

Create a basic table

Here, the student-course-teacher model often used in database principles is used as a demonstration.

Create a student table:

CREATE TABLE Student (
	id CHAR(9) PRIMARY KEY,
	name CHAR(10) NOT NULL,
	sex CHAR(2) CHECK (Sex IN ('F'.'M')),
	grade CHAR(4) NOT NULL,
	major CHAR(20) NOT NULL,
	class INT CHECK (class > 0),
	room CHAR(10) NOT NULL
);
Copy the code

Some integrity constraints are used in this table, such as NOT NULL fields that cannot be NULL, and CHECK’s custom integrity constraints. Use the student ID as the primary key.

Then there is the teacher table:

CREATE TABLE Teacher (
	id CHAR(9) PRIMARY KEY,
	name CHAR(10) NOT NULL,
	salary INT
);
Copy the code

Not much different from the student table above.

Then came the timetable:

CREATE TABLE Course (
	id CHAR(9) PRIMARY KEY,
	teacher_id CHAR(9),
	name CHAR(20) NOT NULL UNIQUE.FOREIGN KEY(teacher_id) REFERENCES Teacher(id)
);
Copy the code

Teacher_id = teacher_id = teacher_ID = teacher_ID = teacher_ID = teacher_ID = teacher_ID = teacher_ID = teacher_ID Use FOREIGN KEY(Teacher_ID) REFERENCES Teacher(ID) to do this.

Please use openGauss2.0.0 or above, otherwise foreign key operation will not be supported!!

Finally, the elective relationship between students and courses:

CREATE TABLE student_course(
	student_id CHAR (9),
	course_id CHAR (9),
	score INT CHECK (score BETWEEN 0 AND 100) DEFAULT 0.PRIMARY KEY(student_id,course_id),
	FOREIGN KEY (student_id) REFERENCES Student(id),
	FOREIGN KEY (course_id) REFERENCES Course(id)
);
Copy the code

Since a student can take more than one course and a course can be taken by more than one student at the same time, we need to realize the many-to-many relationship between students and courses through an intermediate table.

In this case, student_id and course_id are used as the primary key to uniquely identify the elective course. In addition, the two ids used as the primary key and the foreign key respectively correspond to a course and a student.

In addition, in addition to correlation, we can also add an additional score field to this table, which is used to store the score obtained by a certain student taking a certain course, namely the score field.

Finally, we can see the four tables we just created through Navicat: