SQL DB Creation Error

Question

I used BIT DEFAULT 0, as a Boolean dont know if its right like that. Well, the problem is that I cannot create any table because there is a problem in FK, dont get why? I'ev also added ERD Image for better view.

Maybe someone will/can be so nice and explain it to me, ty!

Maybe someone know any good program or online sandbox for turtnind ERD to the MSSQL Code or MySQL code? It would be nice if its free for use. Thanks in advance!

CREATE DataBase LO;
GO
USE LO;

CREATE TABLE Weapon
(
  ClassWeapon CHAR(50) NULL,
  Level INT NULL,
  MinDmg INT NULL,
  MaxDmg INT NULL,
  AtkSpeed NUMERIC(3,2) NULL,
  PerSecondDmg NUMERIC(6,2) NULL,
  PlayerLevel INT NULL,
  SkillLevel INT NULL,
  Image BIT DEFAULT 0 NULL,
  Name CHAR(100) NULL,
  Weapons CHAR(50) NOT NULL,
  Monsters CHAR(50) NULL,
  PRIMARY KEY (Weapons),
  FOREIGN KEY (Monsters) REFERENCES Monster(Monsters)
);

CREATE TABLE Armor
(
  ClassArmor CHAR(50) NULL,
  Name CHAR(100) NULL,
  Level INT NULL,
  Defense INT NULL,
  Image BIT DEFAULT 0 NULL,
  Armors CHAR(50) NOT NULL,
  Monsters CHAR(50) NULL,
  PRIMARY KEY (Armors),
  FOREIGN KEY (Monsters) REFERENCES Monster(Monsters)
);

CREATE TABLE Menu
(
  Weapons CHAR(50) NULL,
  Armors CHAR(50) NULL,
  Monsters CHAR(50) NULL,
  FOREIGN KEY (Weapons) REFERENCES Weapon(Weapons),
  FOREIGN KEY (Armors) REFERENCES Armor(Armors),
  FOREIGN KEY (Monsters) REFERENCES Monster(Monsters)
);

CREATE TABLE Monster
(
  Name CHAR(100) NULL,
  Level INT NULL,
  MinHealth INT NULL,
  MaxHealth INT NULL,
  MinDmg INT NULL,
  MaxDmg INT NULL,
  AtkSpeed NUMERIC(3,2) NULL,
  Location CHAR(100) NULL,
  Monsters CHAR(50) NOT NULL,
  DropItems CHAR(100) NULL,
  Image BIT DEFAULT 0 NULL,
  PRIMARY KEY (Monsters),
  FOREIGN KEY (DropItems) REFERENCES Items(DropItems)
);

CREATE TABLE Items
(
  DropItems CHAR(100) NOT NULL,
  Weapons CHAR(50) NULL,
  Armors CHAR(50) NULL,
  PRIMARY KEY (DropItems),
  FOREIGN KEY (Weapons) REFERENCES Weapon(Weapons),
  FOREIGN KEY (Armors) REFERENCES Armor(Armors)
);

1) Create tables 2) create FK using ALTER TABLE syntax. In SQL there is no forward declaration. For example weapon will reference Monster table(which does not exists). — Lukasz Szozda
– Lukasz Szozda, Commented Aug 1, 2017 at 19:10

Lukasz Szozda · Accepted Answer · 2017-08-01 19:16:35Z

2

Well, the problem is that I cannot create any table because there is a problem in FK, dont get why?

Simply, you cannot reference object that does not exist.

You should first create tables and then add FK relationships:

CREATE DataBase LO;
GO
USE LO;

CREATE TABLE Weapon
(
  ClassWeapon CHAR(50) NULL,
  Level INT NULL,
  MinDmg INT NULL,
  MaxDmg INT NULL,
  AtkSpeed NUMERIC(3,2) NULL,
  PerSecondDmg NUMERIC(6,2) NULL,
  PlayerLevel INT NULL,
  SkillLevel INT NULL,
  Image BIT DEFAULT 0 NULL,
  Name CHAR(100) NULL,
  Weapons CHAR(50) NOT NULL,
  Monsters CHAR(50) NULL,
  PRIMARY KEY (Weapons),
  --FOREIGN KEY (Monsters) REFERENCES Monster(Monsters)
);

CREATE TABLE Armor
(
  ClassArmor CHAR(50) NULL,
  Name CHAR(100) NULL,
  Level INT NULL,
  Defense INT NULL,
  Image BIT DEFAULT 0 NULL,
  Armors CHAR(50) NOT NULL,
  Monsters CHAR(50) NULL,
  PRIMARY KEY (Armors),
 ---- FOREIGN KEY (Monsters) REFERENCES Monster(Monsters)
);

CREATE TABLE Menu
(
  Weapons CHAR(50) NULL,
  Armors CHAR(50) NULL,
  Monsters CHAR(50) NULL,
  --FOREIGN KEY (Weapons) REFERENCES Weapon(Weapons),
 -- FOREIGN KEY (Armors) REFERENCES Armor(Armors),
 -- FOREIGN KEY (Monsters) REFERENCES Monster(Monsters)
);

CREATE TABLE Monster
(
  Name CHAR(100) NULL,
  Level INT NULL,
  MinHealth INT NULL,
  MaxHealth INT NULL,
  MinDmg INT NULL,
  MaxDmg INT NULL,
  AtkSpeed NUMERIC(3,2) NULL,
  Location CHAR(100) NULL,
  Monsters CHAR(50) NOT NULL,
  DropItems CHAR(100) NULL,
  Image BIT DEFAULT 0 NULL,
  PRIMARY KEY (Monsters),
  --FOREIGN KEY (DropItems) REFERENCES Items(DropItems)
);

CREATE TABLE Items
(
  DropItems CHAR(100) NOT NULL,
  Weapons CHAR(50) NULL,
  Armors CHAR(50) NULL,
  PRIMARY KEY (DropItems),
  --FOREIGN KEY (Weapons) REFERENCES Weapon(Weapons),
 -- FOREIGN KEY (Armors) REFERENCES Armor(Armors)
);

ALTER TABLE Weapon
ADD FOREIGN KEY (Monsters) REFERENCES Monster(Monsters);

ALTER TABLE Armor
ADD FOREIGN KEY (Monsters) REFERENCES Monster(Monsters);

ALTER TABLE Menu
ADD 
  FOREIGN KEY (Weapons) REFERENCES Weapon(Weapons),
  FOREIGN KEY (Armors) REFERENCES Armor(Armors),
  FOREIGN KEY (Monsters) REFERENCES Monster(Monsters);

ALTER TABLE Monster
ADD FOREIGN KEY (DropItems) REFERENCES Items(DropItems);

ALTER TABLE Items
ADD FOREIGN KEY (Weapons) REFERENCES Weapon(Weapons),
 FOREIGN KEY (Armors) REFERENCES Armor(Armors);

answered Aug 1, 2017 at 19:16

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user7772660 Over a year ago

Thanks made same!

Sean Lange Over a year ago

Or an even better approach would be to change the order of the tables being created so the tables exist when you specify the foreign keys.

user7772660 Over a year ago

I gues the problem is somewhere else, my ERD is somekind wrong I guess... Thanks anyway..

user7772660 · Accepted Answer · 2017-08-01 19:19:49Z

Thanks @lad2025

Here how it worked by adding Tables and then alter the FK.

CREATE DataBase LO;
GO
USE LO;

CREATE TABLE Weapon
(
  ClassWeapon CHAR(50) NULL,
  Level INT NULL,
  MinDmg INT NULL,
  MaxDmg INT NULL,
  AtkSpeed NUMERIC(3,2) NULL,
  PerSecondDmg NUMERIC(6,2) NULL,
  PlayerLevel INT NULL,
  SkillLevel INT NULL,
  Image BIT DEFAULT 0 NULL,
  Name CHAR(100) NULL,
  Weapons CHAR(50) NOT NULL,
  Monsters CHAR(50) NULL,
  PRIMARY KEY (Weapons)  
);
ALTER TABLE Weapon ADD FOREIGN KEY (Monsters) REFERENCES Monster(Monsters);

CREATE TABLE Armor
(
  ClassArmor CHAR(50) NULL,
  Name CHAR(100) NULL,
  Level INT NULL,
  Defense INT NULL,
  Image BIT DEFAULT 0 NULL,
  Armors CHAR(50) NOT NULL,
  Monsters CHAR(50) NULL,
  PRIMARY KEY (Armors)  
);
ALTER TABLE Armor ADD FOREIGN KEY (Monsters) REFERENCES Monster(Monsters);

CREATE TABLE Menu
(
  Weapons CHAR(50) NULL,
  Armors CHAR(50) NULL,
  Monsters CHAR(50) NULL 
);
ALTER TABLE Menu ADD FOREIGN KEY (Weapons) REFERENCES Weapon(Weapons);
ALTER TABLE Menu ADD FOREIGN KEY (Armors) REFERENCES Armor(Armors);
ALTER TABLE Menu ADD FOREIGN KEY (Monsters) REFERENCES Monster(Monsters);

CREATE TABLE Monster
(
  Name CHAR(100) NULL,
  Level INT NULL,
  MinHealth INT NULL,
  MaxHealth INT NULL,
  MinDmg INT NULL,
  MaxDmg INT NULL,
  AtkSpeed NUMERIC(3,2) NULL,
  Location CHAR(100) NULL,
  Monsters CHAR(50) NOT NULL,
  DropItems CHAR(100) NULL,
  Image BIT DEFAULT 0 NULL,
  PRIMARY KEY (Monsters)  
);
ALTER TABLE Monster ADD FOREIGN KEY (DropItems) REFERENCES Items(DropItems);

CREATE TABLE Items 
(
  DropItems CHAR(100) NOT NULL,
  Weapons CHAR(50) NULL,
  Armors CHAR(50) NULL,
  PRIMARY KEY (DropItems)
);
ALTER TABLE Items ADD FOREIGN KEY (Weapons) REFERENCES Weapon(Weapons);
ALTER TABLE Items ADD  FOREIGN KEY (Armors) REFERENCES Armor(Armors);

Collectives™ on Stack Overflow

SQL DB Creation Error

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related