sql - Data Type different from reference table
问题描述
I am creating a sample IRTC database that includes tables ticket
and train_ticket_fare
. I created a table ticket
with a column as
ticket_class varchar(6)
.
In the table train_ticket_fare
, I referenced it using this
ticket_class references ticket
.
I re-created the tables to check but couldn't find the error.
SQL> desc ticket
Name Null? Type
----------------------- -------- ----------------
PNR_NO NOT NULL NUMBER(10)
TRANSACTIONID NUMBER(10)
FROM_STATION VARCHAR2(20)
TO_STATION VARCHAR2(20)
DATE_OF_JOURNEY DATE
TICKET_CLASS VARCHAR2(6) //this
DATE_OF_BOOKING DATE
TICKET_FARE NUMBER(8,2)
TRAIN_NUMBER NUMBER(5)
SQL> create table Train_Ticket_fare
2 (
3 train_number references train,
4 ticket_class references ticket, //this
5 base_fare number(7,2),
6 reservation_charge number(6,2),
7 superfast_charge number(6,2),
8 other_charge number(6,2),
9 tatkal_charge number(6,2),
10 service_tax number(6,2),
11 primary key(train_number, ticket_class)
12 );
Table created.
SQL> desc Train_Ticket_fare
Name Null? Type
----------------------- -------- ----------------
TRAIN_NUMBER NOT NULL NUMBER(5)
TICKET_CLASS NOT NULL NUMBER(10) //this
BASE_FARE NUMBER(7,2)
RESERVATION_CHARGE NUMBER(6,2)
SUPERFAST_CHARGE NUMBER(6,2)
OTHER_CHARGE NUMBER(6,2)
TATKAL_CHARGE NUMBER(6,2)
SERVICE_TAX NUMBER(6,2)
Resolved! Thanks @stickybit. The table Train_ticket_fare
must have TICKET_CLASS
as primary key which is to be referenced to the table Ticket
. I did the opposite and referenced it wrong.
解决方案
也许你想要这样的东西:
create table train
( train_number number(5) constraint train_pk primary key
);
create table ticket_class
( ticket_class varchar2(6) constraint ticket_class_pk primary key
);
create table ticket
( pnr_no number(10) constraint ticket_pk primary key
, transactionid number(10)
, from_station varchar2(20)
, to_station varchar2(20)
, date_of_journey date
, ticket_class references ticket_class
, date_of_booking date
, ticket_fare number(8,2)
, train_number number(5)
);
create table train_ticket_fare
( train_number references train
, ticket_class references ticket_class
, base_fare number(7,2)
, reservation_charge number(6,2)
, superfast_charge number(6,2)
, other_charge number(6,2)
, tatkal_charge number(6,2)
, service_tax number(6,2)
, constraint train_ticket_fare primary key (train_number, ticket_class)
);
SQL> @desc train_ticket_fare
Name Null? Type
----------------------------------------- -------- ----------------------------
TRAIN_NUMBER NOT NULL NUMBER(5)
TICKET_CLASS NOT NULL VARCHAR2(6)
BASE_FARE NUMBER(7,2)
RESERVATION_CHARGE NUMBER(6,2)
SUPERFAST_CHARGE NUMBER(6,2)
OTHER_CHARGE NUMBER(6,2)
TATKAL_CHARGE NUMBER(6,2)
SERVICE_TAX NUMBER(6,2)
具有相同名称的表和列 (TICKET_CLASS) 并不好。它有效,但可能会导致模棱两可和混乱。避免这种情况的一种方法是使用复数形式命名表,例如 TRAINS、TICKET_CLASSES、TICKETS。
推荐阅读
- javascript - SocketCluster 客户端——TypeError:WebSocket 不是构造函数
- r - 如何在 R 中实现 SAS 百分位语句?
- xcode - 如何解决 Flutter iOS Build 缺少所需的文件
- android - 嵌入 Android 应用程序的实时视频播放器
- javascript - 从 Gatsby JS 制作 Axios 发布 Req
- java - 如何在运行时更改值颜色?
- javascript - 为什么我们要在没有依赖数组的情况下使用 useEffect?
- random - 从时间戳生成确定性随机数
- javascript - 如果页面嵌入在 iframe 中,如何显示特定的 HTML 元素?
- sql - 根据另一个表检查列条件