Are recursive foreign keys also marked as primary keys? (In Oracle SQL Developer)-CodePudding

Example: Is this recursive relationship written correctly? Would Mgr be marked as a primary key or is this code correct?:

create table Employee ( EMPNO number(10) not null, Mgr number(10), constraint pk_Employee primary key (EMPNO), constraint fk_emp_emp foreign key (Mgr) references Employee );

CodePudding user response：

Your code is correct. I checked it with the following sql server and It worked fine.

create table Employee (EMPNO int not null, Mgr int, constraint 
pk_Employee primary key (EMPNO), constraint fk_emp_emp foreign key (Mgr) references Employee );

CodePudding user response：

Would Mgr be marked as a primary key or is this code correct?

Code is correct (table gets created), but mgr will not be part of a primary key constraint; a table can have only one primary key. If you meant to say that it'll be a composite primary key (i.e. it would have two columns), it will not either. See the result of the 2nd query - only empno is a primary key column.

SQL> create table Employee ( EMPNO number(10) not null, Mgr number(10), constraint pk_Employee primary key (EMPNO), constraint fk_emp_emp foreign key (Mgr) references Employee );

Table created.

SQL> select a.constraint_type,
  2         b.column_name
  3  from user_cons_columns b join user_constraints a on a.constraint_name = b.constraint_name
  4  where a.table_name = 'EMPLOYEE';

CONSTRAINT_TYPE      COLUMN_NAME
-------------------- --------------------
C                    EMPNO
P                    EMPNO          --> constraint type = P = primary key
R                    MGR

SQL>