I have two tables, and when I'm trying to connect two columns it says >>> "ERROR: there is no unique constraint matching given keys for referenced table" .

First table:

CREATE TABLE store(
  course text,  
  name text,
  surname text,
  phone_number varchar 
  id INT PRIMARY KEY
);

Second table:

CREATE TABLE factory_production(
  student_course text,
  school_name,
  id INT PRIMARY KEY
);

So, I want to connect two columns between course and student_course. Could you please help me how I can do it.

CodePudding user response：

There are a few things about your question that makes it harder to answer.

1. The syntax of your CREATE TABLE commands are not valid (school_name is missing a type)
2. The table names are the same, so also would not work as the names need to be unique
3. You don't provide the command that you are trying to run to "connect" the two columns.

Based on the naming of the columns however, I'll adjust your CREATE TABLE statements to look like this:

CREATE TABLE courses (
  course_name text,
  school_name text,
  id INT PRIMARY KEY
);

CREATE TABLE students (
  course text,  
  name text,
  surname text,
  phone_number varchar,
  id INT PRIMARY KEY
);

There is still one thing that is needed to get your table schemas lined up however. Usually, you would not include the course column in your students table. Instead you would reference between the two through a join on an ID column. So with this, your create table statements would be more like:

CREATE TABLE courses (
  course_name text,
  school_name text,
  id INT PRIMARY KEY
);

CREATE TABLE students (
  course_id int,
  name text,
  surname text,
  phone_number varchar,
  id INT PRIMARY KEY
);

Now you can "connect" the two tables via a JOIN command. Something like:

SELECT *
FROM students
LEFT JOIN courses ON students.course_id = courses.id

You should get an output that provides you with the student row and the matching course row information from the courses table.

CodePudding user response：

You have a table of courses. Its primary key is called ID. Then you should have a table of students, and consistently its primary key should also be called ID, too. And then you have a table of student courses, i.e. the courses a student attends. This should refer to both IDs. It is a bridge table to establish the m:n relation, because one student can attend many courses and one course can be attended by many students.

CREATE TABLE course
(
  id INT PRIMARY KEY,
  course_name VARCHAR(50),
  school_name VARCHAR(50)
);

CREATE TABLE student
(
  id INT PRIMARY KEY,
  name VARCHAR(50),
  surname VARCHAR(50),
  phone_number VARCHAR
);

CREATE TABLE student_course
(
  course_id INT,  
  student_id INT,  
  CONSTRAINT fk_student_course_course FOREIGN KEY(course_id) REFERENCES course(id),
  CONSTRAINT fk_student_course_student FOREIGN KEY(student_id) REFERENCES student(id)
);

(A side note: You seem to be dealing with different schools. Is this so? Then you should ideally also have a school table and have the classes and students refer to it via school IDs. And If a student can only attend classes of the school they attend, then maybe working with composite keys would be beneficial.)