DROP DATABASE IF EXISTS kadai04;
CREATE DATABASE kadai04;
use kadai04
-- テーブル順番1
-- 店側商品テーブル(T_VendorShohin)作成
-- F_ShohinName(商品名)
-- F_ShohinPrice(単価)
-- F_JANCode(JANコード)
-- F_ConTax(消費税)
-- F_TaxRate(税率)
-- F_TaxAmount(税額)
CREATE TABLE T_VendorShohin(
F_ShohinName nvarchar(30) not null primary key,
F_ShohinPrice int(7) unsigned not null,
F_JANCode int(13) unsigned unique not null,
F_ConTax double unsigned not null default 1.08,
F_TaxRate double unsigned not null,
F_TaxAmount int(3) unsigned not null
);
-- テーブル順番2
-- 企業テーブル作成(T_Company)作成
-- F_ShopNum(店舗No)
-- F_CompanyName(企業名)
-- F_ShopName(店舗名)
-- F_ShopTel(店舗電話番号)
CREATE TABLE T_Company(
F_ShopNum int(3) unsigned zerofill not null auto_increment primary key,
F_CompanyName nvarchar(20) not null,
F_ShopName nvarchar(10) unique not null,
F_ShopTel char(13) unique not null
);
-- テーブル順番3
-- nanaco基本情報テーブル(T_NanacoProfile)作成
-- F_NanacoNum(nanacoナンバー)
-- F_NanacoBalance(nanaco残高ポイント)
CREATE TABLE T_NanacoProfile(
F_NanacoNum int(16) unsigned not null auto_increment primary key,
F_NanacoBalance int(7) unsigned not null default 0
);
-- テーブル順番4
-- カレンダーテーブル(T_Calendar)
-- F_Date(日時、日付)
-- F_Weather(その日の天気)
-- F_CampaignInfo(キャンペーン情報)
CREATE TABLE T_Calendar(
F_Date timestamp not null primary key default 0,
F_Weather nvarchar(10) not null,
F_CampaignInfo nvarchar(30)
);
-- テーブル順番5
-- nanacoGetポイントテーブル(T_NanacoGetPoint)作成
-- F_nanacoNum(nanacoナンバー)
-- F_Date(日付、日時)
-- F_ShopNum(店舗No)
-- F_TradeNum(取引No)
-- F_NanacoGetPoint(nanacoGetポイント)
CREATE TABLE T_NanacoGetPoint(
F_NanacoNum int(16) unsigned not null auto_increment,
F_Date timestamp not null default 0,
F_ShopNum int(3) unsigned zerofill not null,
F_TradeNum int(4) unsigned zerofill not null,
F_NanacoGetPoint int(4) unsigned not null,
primary key(F_NanacoNum,F_Date),
foreign key(F_NanacoNum) references T_NanacoProfile(F_NanacoNum)
);
-- テーブル順番6
-- 担当者テーブル(T_Staff)作成
-- F_StaffName(担当者名)
-- F_ShopNum(店舗no)
CREATE TABLE T_Staff(
F_ShopNum int(3) unsigned zerofill not null,
F_StaffName nvarchar(12) not null,
primary key(F_ShopNum,F_StaffName),
foreign key(F_ShopNum) references T_Company(F_ShopNum)
);
-- テーブル順番7
-- 取引テーブル(F_TradeList)作成
-- F_TradeNum(取引No)
-- F_Date(日付、日時)
-- F_ReceiptNum(レシートナンバー)
-- F_RegiNum(レジナンバー)
-- F_StaffName(担当者名)
-- F_ShohinName(商品名)
-- F_Discount(割引額)
-- F_PayMethod(支払方法)
-- F_NanacoNum(nanacoナンバー)
-- F_Deposit(預り金)
-- F_Change(おつり)
-- F_ShopNum(店舗No)
-- F_Acknowledge(領収書F)
CREATE TABLE T_TradeList(
F_TradeNum int(4) unsigned zerofill not null auto_increment,
F_Date timestamp not null default 0,
F_ReceiptNum int(4) zerofill unique not null,
F_RegiNum int(3) zerofill not null,
F_StaffName nvarchar(12) not null,
F_ShohinName nvarchar(30) not null,
F_Discount int(5),
F_PayMethod nvarchar(20) not null,
F_NanacoNum int(16) unsigned,
F_Deposit int(7) unsigned not null,
F_Change int(7) unsigned not null default 0,
F_ShopNum int(3) unsigned zerofill not null,
F_Acknowledge int(4) zerofill,
primary key(F_TradeNum,F_Date),
foreign key(F_Date) references T_Calendar(F_Date),
foreign key(F_StaffName) references T_Staff(F_StaffName),
foreign key(F_ShohinName) references T_VendorShohin(F_ShohinName),
foreign key(F_NanacoNum) references T_NanacoProfile(F_NanacoNum),
foreign key(F_ShopNum) references T_Staff(F_ShopNum)
);
-- テーブル順番8
-- お客さん商品テーブル(T_CustomerShohin)作成
-- F_TradeNum(取引No)
-- F_ShohinName(商品名)
-- F_ShohinNum(商品数)
CREATE TABLE T_CustomerShohin(
F_TradeNum int(4) unsigned zerofill not null,
F_ShohinName nvarchar(30) not null,
F_ShohinNum int(3) unsigned not null,
primary key(F_TradeNum,F_ShohinName),
foreign key(F_TradeNum) references T_TradeList(F_TradeNum)
);
以上の条件でテーブルを作成すると、以下のエラーメッセージが出てきます
ERROR 1005 (HY000) at line 91 in file: '07.sql': Can't create table kadai04
.t_tradelist
(errno: 150 "Foreign key constraint is incorrectly
formed")
ERROR 1005 (HY000) at line 117 in file: '07.sql': Can't create table kadai04
.t_customershohin
(errno: 150 "Foreign key constraint is incorr
ectly formed")
どういう理由かわかりますでしょうか?
回答5件
あなたの回答
tips
プレビュー