Difference Between Primary Key and Foreign Key in SQL

In the past, finding information took a lot of time because it was on paper. But today, when we order something or find information online, it doesn't take much time because the information is in a database, the process can be completed with a single click or a few keyword inputs. A database is a tool for collecting and organizing information. Also, it may collect orders, addresses, objects, or other items. As your company grows, using a database management system can greatly help your company grow.

Before creating or using any kind of database, you need to know two key constraints to be useful.

Primary Key
A primary key is a constraint that uniquely identifies each row in a table. Each table can have only one primary key. In a table, the primary key cannot be null and must have a unique value like the id number.

For example, before entering code to create a table in Python, you want to think about how you are going to normalize the table so you can use a statement like the SQL Join clause. Let's say you want to create a table like the example below, two students take several classes and the professor also teaches several classes, you have to find which key going to be the primary key and the foreign key.

Looking at the table above, we have the Student id, Student Name, Major, Course, Title, Instructor Name, Instructor Location, and Grade. The most unique key here is the student id because other keys can have many of the same content, but the student id is the only key that identifies that student.

Since the primary key cannot have nulls, you have to fill in the empty spaces by repeating the student number, name, and major.

Foreign Key
The foreign key is a column that references a primary key in another table. Also, it's a column or a group of columns in a relational database table that provides a link between data in other tables.

Let's look at the table and see which key can be both a foreign key and a primary key in another table. The major cannot be a foreign key because it could be various courses or another course with other professors, so it's not a unique key.

A course_id key can be both a foreign key and a primary key because it tells you what the course name is, who the professor is, and where the class gonna be.

However, both tables are not related to each other yet because there is no relationship between the Course to Student table, only Student to Course. At this time, creating a join table will define a many-to-many relationship on both Student and Course tables. The many-to-many relationship will contain both primary key as a foreign key and then using the relationship() function will define relationship on both tables.

If you look at the table below, there are three tables, one has only student information, the other one has course information, and student_course has both student_id and course_id that define a relationship on both tables.

Now that the table is organized using the Excel, all you have to do is enter the code in python.

# create join table
student_course = Table (
   "student_course
    Base.metadata, 
    Column ("id", Integer, primary_key=True),
    Column ("students_id", ForeignKey("students.id")),
    Column ("course_id", ForeignKey("course.id"))
)

#creating table 

class Student (Base):
    __tablename__= 'students'
    id = Column(Integer(), primary_key=True)
    name= Column(String())
    major=Column (String())

# repr () returns a string similar to how print() works
    def __repr__(self):
        return f"Student id:{self.id}: "\
            + f"Student name:{self.name} "\
            + f"Major:{self.major}"
    courses=relationship("Course ", secondary=student_course , 
    back_populates="courses")


class Course (Base):
    __tablename__= 'courses'
    id = Column(Integer(), primary_key=True)
    title= Column(String())
    instructor=Column (String())
    location = Column (String())


    def __repr__(self):
        return f"Course id:{self.id}: "\
            + f"Course title:{self.title} "\
            + f"Instructor:{self.instructor}"\
            + f"Location:{self.location}"
     students=relationship("Student", secondary=student_course , 
     back_populates="students")


if __name__ == '__main__':

    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)

    Session = sessionmaker(bind=engine)
    session = Session()
    session.query(Student).delete()
    session.query(Course).delete()

students=[
    Student (student_id="52146", 
              name="Kyuhee Lee", major="MTH"),
    Student (student_id="75486", 
              name="Lilly Smith", major="ART")]

courses=[
    Course (course_id="60205", 
              title="Algebra", instructor="West", location="B222"),
    Course (course_id="20105", 
              title="Art", instructor="Kim", location="B213"),
    Course (course_id="320101", 
              title="Basic English", instructor="Porter", location="D319"),
    Course (course_id="740102", 
              title="Science", instructor="Kim", location="B213")]

session.bulk_save_objects (students, courses)
session.commit ()

Conclusion
We discussed the importance of primary and foreign keys when creating tables in a database. You've learned which keys are primary keys and which are foreign keys. Identifying two keys is important when creating a table because it tells the relationship between the tables. Again, primary keys contain unique values, and foreign keys contain values referenced by the primary key and are keys that can be used as primary keys in other tables, thereby determining relationships between tables. I hope this helps you understand how to easily find primary and foreign keys.