使用主（外部）密钥和引用的SQL Oracle编码

Hi guys I am new to SQL and I'm trying to create 2 tables: employee and department with one column from a table that refer 2 columns from the other table, to link them to each other.

大家好我是SQL的新手，我正在尝试创建2个表：员工和部门，其中一个表中的一列引用另一个表中的2列，以将它们相互链接。

create table Employee
(
        employee_id Number,
        firstname varchar2(50 char),
        lastname varchar2(50 char),
        gender varchar2 (2 char),
        position varchar2(50 char),
        department_id number Not null,
        salary number Not null,
        Constraint employee_pk Primary key(employee_id)
);

create table products
(
        department_id Number, not null,
        department_name varchar2(50 char),
        Constraint products_fk Foreign key (department_id)
        references employee(department_id)
        );

Error report -

错误报告 -

ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

I don't know what am I doing wrong with that, I think the problem are the keys, I want to refer the department(department_id and department_name) to the department_id from the employee table....can anyone explain me what is the problem? Image of the 2 tables

我不知道我做错了什么，我认为问题是关键，我想将部门（department_id和department_name）从employee表中引用到department_id ....任何人都可以解释我是什么问题？ 2张桌子的图片

1 个解决方案

#1

Error you got says that there's an invalid column name, and yes - there is, because of a superfluous comma, in the CREATE TABLE PRODUCTS here:

你得到的错误是说有一个无效的列名，是的 - 由于多余的逗号，在这里的CREATE TABLE PRODUCTS中：

department_id Number, not null,
                    ^
                    this

Once you fix it and run the command again, you get this error:

一旦修复并再次运行该命令，就会出现此错误：

  5          Constraint products_fk Foreign key (department_id)
  6          references employee(department_id)
  7          );
        references employee(department_id)
                            *
ERROR at line 6:
ORA-02270: no matching unique or primary key for this column-list

It means that you can't create a foreign key which points to a column that is not unique nor primary key in the employee table. In there, the primary key is employee_id, which is correct. It wouldn't make sense to make the department_id column a primary key as, obviously, more than a single employee can work in the same department so you'd violate uniqueness.

这意味着您无法创建指向employee表中不唯一的列或主键的外键。在那里，主键是employee_id，这是正确的。将department_id列设为主键是没有意义的，因为很明显，不止一个员工可以在同一个部门工作，因此您违反了唯一性。

If you think twice, does it even make sense to reference a department within the employee table? I'd say not, but it would make a perfect sense to reference the department_id that is the primary key in the department table.

如果你三思而后行，那么引用员工表中的一个部门是否有意义？我会说不是，但是引用department_id作为department表中的主键是完全合理的。

But hey! Your product table looks exactly like the department should - it has department_id, department_name columns. What kind of a "product" is that? Are you sure you made it right?

但是嘿！您的产品表看起来与部门应该完全相同 - 它具有department_id，department_name列。什么样的“产品”是什么？你确定你做对了吗？

The following code makes much more sense to me; wouldn't you agree?

以下代码对我来说更有意义;你不同意吗？

SQL> -- former PRODUCT table
SQL> CREATE TABLE department (
  2    department_id     NUMBER
  3      CONSTRAINT pk_depart PRIMARY KEY,
  4    department_name   VARCHAR2(50 CHAR)
  5  );

Table created.

SQL> CREATE TABLE employee (
  2    employee_id     NUMBER,
  3    firstname       VARCHAR2(50 CHAR),
  4    lastname        VARCHAR2(50 CHAR),
  5    gender          VARCHAR2(2 CHAR),
  6    position        VARCHAR2(50 CHAR),
  7    department_id   NUMBER NOT NULL
  8      CONSTRAINT fk_emp_dept
  9        REFERENCES department ( department_id ),
 10    salary          NUMBER NOT NULL,
 11    CONSTRAINT employee_pk PRIMARY KEY ( employee_id )
 12  );

Table created.

SQL>

#1