i used the program SQL Fiddle and it keeps telling me that the table doesn't exist,what can i do to fix the two tables referencing each other?

the Staff table references the branch table

CREATE TABLE Staff(
StaffNo VARCHAR(5) NOT NULL,
firstName VARCHAR(15) NOT NULL UNIQUE,
lastName VARCHAR(15) NOT NULL,
position VARCHAR(10) NOT NULL,
salary INTEGER
DEFAULT 3000,
CHECK (salary BETWEEN 3000 AND 25000),
email VARCHAR(25),
branchNo CHAR(6) NOT NULL,
PRIMARY KEY (StaffNo),
FOREIGN KEY (branchNo) REFERENCES Branch (branchNo));

and at the same time the branch table references the Staff table

create table Branch(
branchNo char(6) not null primary key,
street varchar(30) not null,
city varchar(20),
postCode char(5) not null,
ManagerNo varchar(5) not null,
foreign key (ManagerNo) references Staff(StaffNo)); 

Answers


Since your tables reference each other in the Foreign Keys you will get an error on either table creation if the other table has not been created yet. I would suggest that you remove the creation of the FOREIGN KEYs to separate ALTER TABLE statements:

CREATE TABLE Staff(
  StaffNo VARCHAR(5) NOT NULL,
  firstName VARCHAR(15) NOT NULL UNIQUE,
  lastName VARCHAR(15) NOT NULL,
  position VARCHAR(10) NOT NULL,
  salary INTEGER
  DEFAULT 3000,
  CHECK (salary BETWEEN 3000 AND 25000),
  email VARCHAR(25),
  branchNo CHAR(6) NOT NULL,
  PRIMARY KEY (StaffNo)
);


create table Branch(
  branchNo char(6) not null primary key,
  street varchar(30) not null,
  city varchar(20),
  postCode char(5) not null,
  ManagerNo varchar(5) not null
); 

alter table staff
  add constraint fk1_branchNo foreign key (branchNo) references Branch (branchNo);

alter table branch
  add constraint fk1_ManagerNo foreign key (ManagerNo) references Staff (StaffNo);

See SQL Fiddle with Demo


Need Your Help

groovy.lang.MissingMethodException: No signature of method resolution

groovy soapui

I'm having some problems with error messages in Groovy.

JQueryUI Drag & Drop - Nested Lists That Can't Switch 'Levels'

jquery jquery-ui drag-and-drop nested sortable

I'm trying to use JQuery UI to set up a system of nested, sortable lists but I don't want children becoming parents and vice versa. My basic code is right from the demo: http://jsfiddle.net/HT8QB/1...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.