CODEWITHSUNDEEP
sqloracle-databaseconstraints
Cillín
Cillín

Reputation: 187

Oracle SQL constraint, name entered must be a member

I have created a tennis club database. This database contains the following tables:

The player table

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'))
);

This consists of members of the tennis club.

And then there is the team table

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
);

This is used to enter 2 players to form a doubles team.

My question is how could I create a constraint that only allows players(member_name) from the player table to be entered into the team table?

Upvotes: 0

Views: 43

Answers (1)

are
are

Reputation: 2615

you need create foreign key constraints see docs here

but in your sample you need change type of player1 and player2 columns from varchar to 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);

sample of createing FK contstraints in table

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)  
);

Upvotes: 1

Related Questions