Warm tip: This article is reproduced from serverfault.com, please click
database-design foreign-keys oracle11g rdbms sql

Foreign Key Reference

发布于 2020-11-28 17:59:07

I addressed in class that the 2 foreign keys in the FlightData table are Depart_Code and Ariv_Code that there isn't any table to make references to them being a primary key in, in the relational schema we were given.

In class I was told that they reference Airport_Code in the Airport table. I was wondering I would go about doing that? I feel like I am missing something obvious. I appreciate any help offered I am still new to database in general and I am currently on Oracle 11g.

Airport table

CREATE TABLE Airport
(
     Airport_Code VARCHAR2(7) CONSTRAINT pk_Airport Primary Key,
     City_Code VARCHAR2(3), 
     CONSTRAINT fk_Airport_City_Code 
         FOREIGN KEY(City_Code) REFERENCES City, 
     Airport_Name VARCHAR2(30)
);

FlightData table:

CREATE TABLE FlightData
(
    Flt_Nbr VARCHAR2(3) CONSTRAINT pk_FlightData Primary Key,
    Depart_Code VARCHAR2(30), 
    Ariv_Code VARCHAR2(30)
);
Questioner
user14658431
Viewed
0
分享
The Impaler 2020-11-29 02:07:30

To make sure Depart_Code and Ariv_Code always reference an airport in the Airport table you need to:

  • Make these columns NOT NULL.
  • Ensure they have the same data type as the key in Airport. Make them have a length of 7.
  • Add two foreign key constraints, each one based on each column.

For example, the second table could look like:

CREATE TABLE FlightData (
  Flt_Nbr VARCHAR2(3) CONSTRAINT pk_FlightData Primary Key,
  Depart_Code VARCHAR2(7) not null,
  constraint fk1 foreign key (Depart_Code) references Airport (Airport_Code),
  Ariv_Code VARCHAR2(7) not null,
  constraint fk2 foreign key (Ariv_Code) references Airport (Airport_Code)
);
user14658431 2020-11-28 18:58:42

Got it! Does that include making the Airport_Code NOT NULL also or just the departure and arrival code NOT NULL?

The Impaler 2020-11-28 19:08:50

@user14658431 Airport_Code is the primary key of the Airport table. Primary keys are always NOT NULL, by definition.

user14658431 2020-11-28 19:46:21

I made the edits to the FlightData but I get: ORA-02291: integrity constraint (IT2309.FK2) violated - parent key not found My code adjusted is -- CREATE TABLE FlightData (Flt_Nbr VARCHAR2(3) CONSTRAINT pk_FlightData Primary Key, Depart_Code VARCHAR2(7) not null, CONSTRAINT fk1 FOREIGN KEY (Depart_Code) REFERENCES Airport (Airport_Code), Ariv_Code VARCHAR2(7) not null, CONSTRAINT fk2 FOREIGN KEY (Ariv_Code) REFERENCES Airport (Airport_Code) ); @The Impaler

The Impaler 2020-11-29 00:18:33

@user14658431 Your data is inconsistent. Remove all the data, create the foreign keys, and then insert the data again.

热门帖子

1
推荐一些好玩的/大众的手游
2
求指教后端项目迁移方案
3
迷你洗衣机是不是都是智商税？
4
求助一个排查了半年没解决的 MySQL order by 子句导致索引失效的问题， 500 多万条记录的小表要查快两分钟
5
个人开发了一款 WordPress 主题： iPao，集成了 AI 总结功能
6
偶然发现奇游加速器会在系统里植入根证书
7
国内有蒲公英替代品推荐吗？
8
语音助手这个东西真的会监听谈话并且上传，从而泄漏隐私吗？
9
出一些有意思的域名-明盘
10
jetbrains 全家桶升级 2024 后，在滚动代码时候感觉有点掉帧

热门github

1
A multi-platform library for OpenGL, OpenGL ES, Vulkan, window and input
2
Dev tool that writes scalable apps from scratch while the developer oversees the implementation
3
shadcn/ui, but for Svelte. ✨
4
The Python Risk Identification Tool for generative AI (PyRIT) is an open access automation framework to empower security professionals and machine learning engineers to proactively find risks in their generative AI systems.
5
Performance-portable, length-agnostic SIMD with runtime dispatch
6
ZK Credo
7
OpenCodeInterpreter: Integrating Code Generation with Execution and Refinement
8
Joplin - the secure note taking and to-do app with synchronisation capabilities for Windows, macOS, Linux, Android and iOS.
9
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.
10
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
11
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
12
🎓 Path to a free self-taught education in Computer Science!
13
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
14
A collective list of free APIs
15
📚 Freely available programming books