Oracle Project（使用外键和模型的真实方法）

Question

您好，我做了这个，但无法打开模型。我遇到了这个错误。我知道对于模型，我们需要使用外键和主键，但是执行第一代码时会遇到错误。是否有任何简单的打印模型的方法。另外，我没有得到我认为我们链接2的参考部分 放弃密钥。

ALTER TABLE Recipient
ADD FOREIGN KEY (firstName)
REFERENCES Donor(firstName);


CREATE TABLE Recipient( recipientID INT  NOT NULL,
firstName VARCHAR( 50 ) NOT NULL, lastname VARCHAR( 50 ) not null,
address VARCHAR( 60 ) not null, email VARCHAR( 100 ) not null, 
phone VARCHAR( 20 ) not null,  birthday INT not null, bloodType varchar (3)NOT NULL,
PRIMARY KEY (recipientID));

CREATE TABLE Donor( donorID INT  NOT NULL,firstName VARCHAR( 50 ) NOT NULL, 
lastname VARCHAR(50) not null, address VARCHAR(60) not null, email VARCHAR( 100 ) not null, 
phone VARCHAR(20) not null, birthday INT not null, bloodtype VARCHAR(3)NOT NULL, PRIMARY KEY (donorID));


CREATE TABLE Blood_BANK( donorID INT NOT NULL , Blood_BANK VARCHAR(50) not null, Adress VARCHAR(50) not null,PRIMARY KEY(donorID));

Create TABLE Hospital(donorID INT NOT NULL, 
recipientID INT NOT NULL ,hospital VARCHAR(50),room INT NOT NULL,doctor VARCHAR(50) not null, primary key(hospital) );


CREATE TABLE doctor(  doctor VARCHAR(50) not null, hospital VARCHAR(50) not null, primary key (doctor));

CREATE SEQUENCE seq20
INCREMENT BY 1 START WITH 1;
INSERT INTO Donor (donorID,firstName, lastname, address, email,phone, birthday , bloodType)
VALUES (seq20.NEXTVAL,'Neriman','Nerimanov' , 'Marneuli'   ,'[email protected]' , 597156, 2004,'O+' ); 

CREATE SEQUENCE seq21
INCREMENT BY 1 START WITH 1 ;
INSERT INTO Recipient(recipientID, firstName,lastname,address,email,phone,birthday,bloodType)
VALUES ( seq21.NEXTVAL, 'Nizami', 'Lom', 'Batumi', '[email protected]', 597931,2000,'O+');

drop sequence seq22;
CREATE SEQUENCE seq22
INCREMENT BY 1 START with 1;
INSERT INTO Blood_BANK (donorID, Blood_BANK , Adress) 
VALUES (seq22.NEXTVAL, 'Marneuli Hospital', 'Marneuli');

INSERT INTO doctor( doctor, hospital)
VALUES( 'Nana Oposhvili ', ' havit medical');

CREATE SEQUENCE seq23
INCREMENT BY 1 START with 1;

CREATE SEQUENCE seq24
INCREMENT BY 1 START WITH 1;

INSERT INTO Hospital(donorID,recipientID, hospital,room, doctor)
VALUES( seq23.NEXTVAL, seq24.NEXTVAL,'Havit medical',511,'Nana Oposhvili');

错误：

模型：

Answer 1

简化了，这就是您所做的：

create table donor
  (donorID     int   primary key,
   firstname   varchar2(50),
   ...
  );
create table recipient
  (recipientID int   primary key,
   firstname   varchar2(50),
   ...
  );
alter table recipient add foreign key (firstname) references donor (firstname);  

错误说供体表中没有匹配的主键或唯一键，然后可以从coceptient表中引用该键。

换句话说：任何表格 - 应该引用供体表的表格 - 必须引用其主键列，即donorid。您不能引用firstName，它只是只是列（对此没什么特别的） - 您当然不会使其成为主要键，并且将其作为唯一钥匙甚至更糟（肯定还有世界上一个Omar（或在供体表中）。如果不是现在，它将是

。 怎么办？

---

该 。 表明哪个

第三个

SQL> create table person
  2    (person_id number constraint pk_pers primary key,
  3     first_name varchar2(20),
  4     last_name  varchar2(20),
  5     date_of_birth date
  6    );

Table created.

SQL> create table role
  2    (role_id number constraint pk_role primary key,
  3     name    varchar2(20)
  4    );

Table created.

SQL> create table person_x_role
  2    (person_id number constraint fk_pxr_pers references person (person_id),
  3     role_id   number constraint fk_pxr_role references role (role_id),
  4     --
  5     constraint pk_pxr primary key (person_id, role_id)
  6    );

Table created.

角色

SQL> insert into person values (1, 'Little', 'Foot', date '2020-01-01');

1 row created.

SQL> insert into person values (2, 'Scott', 'Head', date '2010-02-03');

1 row created.

SQL> insert into role values (1, 'donor');

1 row created.

SQL> insert into role values (2, 'recipient');

1 row created.

SQL> insert into person_x_role values (1, 1);

1 row created.

SQL> insert into person_x_role values (2, 1);

1 row created.

SQL> insert into person_x_role values (2, 2);

1 row created.

。

SQL> select * from person;

 PERSON_ID FIRST_NAME           LAST_NAME            DATE_OF_BI
---------- -------------------- -------------------- ----------
         1 Little               Foot                 01.01.2020
         2 Scott                Head                 03.02.2010

SQL> select * From role;

   ROLE_ID NAME
---------- --------------------
         1 donor
         2 recipient

SQL> select * From person_x_role;

 PERSON_ID    ROLE_ID
---------- ----------
         1          1
         2          1
         2          2

人

SQL> select p.first_name, p.last_name, r.name role_name
  2  from person p join person_x_role x on x.person_id = p.person_id
  3                join role r on r.role_id = x.role_id;

FIRST_NAME           LAST_NAME            ROLE_NAME
-------------------- -------------------- --------------------
Little               Foot                 donor
Scott                Head                 donor
Scott                Head                 recipient

SQL>

的 机会）。