Databases
StackOverflow2013
Postsdbo
POCreating tables with circular reference
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreating tables with circular reference
    primarykey
    data
    text
    <p>How can I create multiple tables with circular references. I have this data that I'd like to create all in one shot:</p> <pre><code>DROP TABLE employee CASCADE CONSTRAINTS; CREATE TABLE employee ( fname varchar2(15) not null, minit varchar2(1), lname varchar2(15) not null, ssn char(9), bdate date, address varchar2(30), sex char, salary number(10,2), superssn char(9), dno number(4), primary key (ssn), foreign key (superssn) references employee(ssn), foreign key (dno) references department(dnumber) ); DROP TABLE department CASCADE CONSTRAINTS; CREATE TABLE department ( dname varchar2(15) not null, dnumber number(4), mgrssn char(9) not null, mgrstartdate date, primary key (dnumber), unique (dname), foreign key (mgrssn) references employee(ssn) ); DROP TABLE dept_locations CASCADE CONSTRAINTS; CREATE TABLE dept_locations ( dnumber number(4), dlocation varchar2(15), primary key (dnumber,dlocation), foreign key (dnumber) references department(dnumber) ); DROP TABLE project CASCADE CONSTRAINTS; CREATE TABLE project ( pname varchar2(15) not null, pnumber number(4), plocation varchar2(15), dnum number(4) not null, primary key (pnumber), unique (pname), foreign key (dnum) references department(dnumber) ); DROP TABLE works_on CASCADE CONSTRAINTS; CREATE TABLE works_on ( essn char(9), pno number(4), hours number(4,1), primary key (essn,pno), foreign key (essn) references employee(ssn), foreign key (pno) references project(pnumber) ); DROP TABLE dependent CASCADE CONSTRAINTS; CREATE TABLE dependent ( essn char(9), dependent_name varchar2(15), sex char, bdate date, relationship varchar2(8), primary key (essn,dependent_name), foreign key (essn) references employee(ssn) ); </code></pre> <p>Please help!!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCharles
    1. USJuan Carlos Hernandez
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCreating tables with circular reference
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POCreating tables with circular reference
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCreating tables with circular reference
      1. This table or related slice is empty.
      1. VTDownMod
    1. COAny reason thy you can't run this? Are you getting an error code?
      singulars
      1. POCreating tables with circular reference
      1. USDavid Starkey
    2. CONot sure who voted to close the question without leaving a comment, but I think it's a good question. You might want to re-think the not null constraint on department.mgrssn. If a manager leaves, is he intstantly replaced 100% of the time? If not, your data model does not support the business requirement.
      singulars
      1. POCreating tables with circular reference
      1. USDan Bracuk
    3. CO@DavidStarkey, the code won't run because the employee and department tables reference each other.
      singulars
      1. POCreating tables with circular reference
      1. USDan Bracuk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload