Oracle SQL约束，输入的名称必须是成员。

Question

我创建了一个网球俱乐部数据库。该数据库包含以下表格：

牌桌

CREATE TABLE player
(
  member_id INT PRIMARY KEY NOT NULL, 
  member_name VARCHAR2(70) NOT NULL,
  date_of_birth DATE,
  member_address VARCHAR2(300),
  contact_number INT NOT NULL,
  gender VARCHAR2(1) NOT NULL CHECK(gender IN('f','m')),
  club_seeding INT NOT NULL,
  county_seeding INT NOT NULL,
  renewal_date DATE,
  m_type VARCHAR(9) NOT NULL CHECK(m_type IN('junior','student', 'senior', 'family', 'associate'))
);

这由网球俱乐部的成员组成。

然后是团队桌

CREATE TABLE team
(
  team_id INT PRIMARY KEY NOT NULL,
  club_seeding INT,
  county_seeding INT,
  player1 VARCHAR2(70) NOT NULL,
  player2 VARCHAR2(70) NOT NULL,
  team_name VARCHAR2(145) NOT NULL
);

这是用来进入2名球员组成一支双打队。

我的问题是，如何创建一个只允许从player表中的球员(Member_name)输入到team表的约束？

Answer 1

您需要创建外键约束参见docs 这里

但是在您的示例中，需要将player1和player2列的类型从varchar更改为int

sample of alter table statements

-- Create foreign key constraints 
alter table TEAM
  add constraint fk_team_player_1 foreign key (PLAYER1)
  references player (MEMBER_ID);

alter table TEAM
  add constraint fk_team_player_2 foreign key (PLAYER2)
  references player (MEMBER_ID);

在表中创建FK控件的示例

CREATE TABLE team
(
  team_id INT PRIMARY KEY NOT NULL,
  club_seeding INT,
  county_seeding INT,
  player1 INT NOT NULL,
  player2 INT NOT NULL,
  team_name VARCHAR2(145) NOT NULL

,constraint fk_team_player_1 foreign key (PLAYER1)
  references player (MEMBER_ID)
,constraint fk_team_player_2 foreign key (PLAYER2)
  references player (MEMBER_ID)  
);

if you want to use member names as PK

CREATE TABLE player
( 
  member_name VARCHAR2(70) PRIMARY KEY NOT NULL,
  date_of_birth DATE,
  member_address VARCHAR2(300),
  contact_number INT NOT NULL,
  gender VARCHAR2(1) NOT NULL CHECK(gender IN('f','m')),
  club_seeding INT NOT NULL,
  county_seeding INT NOT NULL,
  renewal_date DATE,
  m_type VARCHAR(9) NOT NULL CHECK(m_type IN('junior','student', 'senior', 'family', 'associate'))
);

CREATE TABLE team
(
  team_id INT PRIMARY KEY NOT NULL,
  club_seeding INT,
  county_seeding INT,
  player1 VARCHAR2(70) NOT NULL,
  player2 VARCHAR2(70) NOT NULL,
  team_name VARCHAR2(145) NOT NULL

,constraint fk_team_player_1 foreign key (PLAYER1)
  references player (member_name)
,constraint fk_team_player_2 foreign key (PLAYER2)
  references player (member_name)  
);