Can I create a foreign key and primary key of the same column in SQL?-CodePudding

I want to create a composite primary key for the Enrolment table from the (STDNO & CORSNO) columns which they are also a foreign key from Student and Course tables but it keeps giving me errors. also, the same thing happened in student table I need to let each student belong to a department.

Here's what I did:

CREATE TABLE Student
(
    STDNO number(8),
    SNAME varchar2(30) NOT NULL,
    DEPTNO varchar(2),

    CONSTRAINT PK_Studnet PRIMARY KEY(STDNO,DEPTNO),
    FOREIGN KEY(DEPTNO) REFERENCES Department(DEPTNO)
);

CREATE TABLE Department
(
    DEPTNO varchar2(2) PRIMARY KEY,
    DNAME varchar2(20)
);

CREATE TABLE Course
(
    CORSNO number(3) PRIMARY KEY,
    CNAME varchar2(30),
    DETNO varchar2(30),
    CMAX number(2)
);

CREATE TABLE Enrolment
(
    STDNO number(8),
    CORSNO number(3),
    GRADE number(2),
    EDATE date date default CURRENT_TIMESTAMP,
    CONSTRAINT PK_Enrolment PRIMARY KEY (STDNO, CORSNO),
    FOREIGN KEY(STDNO) REFERENCES Student(STDNO),
    FOREIGN KEY(CORSNO) REFERENCES Course(CORSNO)
);

CodePudding user response：

A PRIMARY KEY should uniquely identify a thing it represents; in the case of a Student, we can assume that the student number should be unique to that student and should be the primary key by itself (as, it is assumed that, you are not going to issue the same student number to two students in different departments).

If you change your code so that STUDNO alone is the PRIMARY KEY and rearrange the orders of the tables so that the referenced tables are created before the tables that references them then you get:

CREATE TABLE Department
(
    DEPTNO varchar2(2) PRIMARY KEY,
    DNAME varchar2(20)
);

CREATE TABLE Course
(
    CORSNO number(3) PRIMARY KEY,
    CNAME varchar2(30),
    DETNO varchar2(30),
    CMAX number(2)
);

CREATE TABLE Student
(
    STDNO number(8),
    SNAME varchar2(30) NOT NULL,
    DEPTNO varchar(2),

    CONSTRAINT PK_Studnet PRIMARY KEY(STDNO),
    FOREIGN KEY(DEPTNO) REFERENCES Department(DEPTNO)
);

CREATE TABLE Enrolment
(
    STDNO number(8),
    CORSNO number(3),
    GRADE number(2),
    EDATE date default CURRENT_TIMESTAMP,
    CONSTRAINT PK_Enrolment PRIMARY KEY (STDNO, CORSNO),
    FOREIGN KEY(STDNO) REFERENCES Student(STDNO),
    FOREIGN KEY(CORSNO) REFERENCES Course(CORSNO)
);

(And also fix the typo where you have date date in the Enrolment table.)

Then the constraints in the Enrolment table work because each of the referential constraints references something unique. Before, you had said that the combination of both student number and department together was unique but were trying to reference just the student number which, by itself, was not unique.

db<>fiddle here