SQL database won't initialize because all tables rely on primary keys/foreign keys that are in different tables. Is there a way to get this to compile and not break?
Attached is code and ERD schema
CREATE TABLE boat
(
huID int NOT NULL,
manufacturer varchar(50) NOT NULL,
Model varchar(20) NOT NULL,
Year int NOT NULL,
Price decimal(5,2) NOT NULL,
color varchar(30) NOT NULL,
condition varchar(20),
CHECK (condition = 'New' OR condition = 'Used') ,
CONSTRAINT boat_huID_fk
FOREIGN KEY (huID) REFERENCES Holding(huID),
CONSTRAINT boat_Price_pk PRIMARY KEY (Price)
);
CREATE TABLE Customer
(
customerID int,
Name varchar(50) PRIMARY KEY,
Address varchar(50) NOT NULL,
Phone int NOT NULL,
CONSTRAINT Customer_customerID_fk
FOREIGN KEY (customerID) REFERENCES invoice(customerID)
);
CREATE TABLE Holding
(
huID int NOT NULL,
CONSTRAINT boat_huID_pk PRIMARY KEY (huID)
);
CREATE TABLE Salesperson
(
salespersonID int PRIMARY KEY,
Name varchar(50) NOT NULL,
CONSTRAINT Customer_Name_fk
FOREIGN KEY (Name) REFERENCES Customer (Name)
);
CREATE TABLE invoice
(
invoiceNumber int,
customerID int NOT NULL,
salespersonID int,
Price int NOT NULL,
huID int NOT NULL
CONSTRAINT boat_huID_fk REFERENCES Holding(huID),
CONSTRAINT boat_Price_pk
FOREIGN KEY (Price) REFERENCES boat(Price),
CONSTRAINT Salesperson_salespersonID_fk
FOREIGN KEY (salespersonID) REFERENCES Salesperson (salespersonID),
CONSTRAINT Customer_customerID_pk PRIMARY KEY(customerID)
);
CodePudding user response：
When you have circular foreign key constraints, define one constraint of the circle separately using an ALTER.
The pattern is:
CREATE TABLE T1 (
ID INT PRIMARY KEY,
... -- other cols
);
CREATE TABLE T2 (
ID INT PRIMARY KEY,
..., -- other cols
CONSTRAINT T1_FK FOREIGN KEY (ID) REFERENCES T1(ID)
);
Create other tables with FKs to previously defined tables.
Then complete the circle with an FK from the first table to the last table via an ALTER:
ALTER TABLE T1 ADD CONSTRAINT T2_FK FOREIGN KEY (ID) REFERENCES T2(ID);
Alternatively, if it's too hard to keep track of what depends on what, you can just define all foreign keys using ALTER.
CodePudding user response：
You'll have to create tables in order so that tables - that reference other tables - aren't created before them.
In your case,
- reordering
CREATE TABLEstatements, - removing foreign key constraint from
salesperson(not entirely; create it withalter tableat the end of the script) and - renaming constraints in
salespersontable (you named them in a strange manner, not uniquely)
does the job.
SQL> CREATE TABLE holding (
2 huid INT NOT NULL,
3 CONSTRAINT boat_huid_pk PRIMARY KEY ( huid )
4 );
Table created.
SQL>
SQL> CREATE TABLE boat (
2 huid INT NOT NULL,
3 manufacturer VARCHAR(50) NOT NULL,
4 model VARCHAR(20) NOT NULL,
5 year INT NOT NULL,
6 price DECIMAL(5, 2) NOT NULL,
7 color VARCHAR(30) NOT NULL,
8 condition VARCHAR(20),
9 CHECK ( condition = 'New'
10 OR condition = 'Used' ),
11 CONSTRAINT boat_huid_fk FOREIGN KEY ( huid )
12 REFERENCES holding ( huid ),
13 CONSTRAINT boat_price_pk PRIMARY KEY ( price )
14 );
Table created.
SQL>
SQL> CREATE TABLE salesperson (
2 salespersonid INT PRIMARY KEY,
3 name VARCHAR(50) NOT NULL
4 -- CONSTRAINT Customer_Name_fk
5 -- FOREIGN KEY (Name) REFERENCES Customer (Name)
6 );
Table created.
SQL>
SQL> CREATE TABLE invoice (
2 invoicenumber INT,
3 customerid INT NOT NULL,
4 salespersonid INT,
5 price INT NOT NULL,
6 huid INT NOT NULL
7 CONSTRAINT inv_huid_fk
8 REFERENCES holding ( huid ),
9 CONSTRAINT inv_price_pk FOREIGN KEY ( price )
10 REFERENCES boat ( price ),
11 CONSTRAINT salesperson_salespersonid_fk FOREIGN KEY ( salespersonid )
12 REFERENCES salesperson ( salespersonid ),
13 CONSTRAINT customer_customerid_pk PRIMARY KEY ( customerid )
14 );
Table created.
SQL>
SQL> CREATE TABLE customer (
2 customerid INT,
3 name VARCHAR(50) PRIMARY KEY,
4 address VARCHAR(50) NOT NULL,
5 phone INT NOT NULL,
6 CONSTRAINT customer_customerid_fk FOREIGN KEY ( customerid )
7 REFERENCES invoice ( customerid )
8 );
Table created.
SQL>
SQL> ALTER TABLE salesperson
2 ADD CONSTRAINT customer_name_fk FOREIGN KEY ( name )
3 REFERENCES customer ( name );
Table altered.
SQL>
CodePudding user response：
In addition, to the above mentioned comments you may want to add in the cascade clause so if you delete a parent row all the related underlying rows will also be removed. Below is a test case.Use with caution.
In addition, I also provided a query that you may want to use to tell you what is related. In my example, I hardcoded the table name ''PARENT" but you may want to use a bind variable. Good Luck.
create table parent (
id NUMBER(10),
value varchar2(30),
constraint parent_pk primary key (id)
);
CREATE TABLE child
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint child_pk primary key (id,value),
CONSTRAINT parent_child_fk
FOREIGN KEY (id)
REFERENCES parent(id)
ON DELETE CASCADE
);
CREATE TABLE grandchild
( id NUMBER(10) not null,
value NUMBER(10) not null,
constraint grandchild_pk primary key (id,value),
CONSTRAINT child_grandchild_fk
FOREIGN KEY (id,value)
REFERENCES child(id,value)
ON DELETE CASCADE
);
insert into parent values (1,'a');
insert into parent values (2,'b');
insert into parent values (3,'c');
insert into child values (1,1);
insert into child values (1,2);
insert into child values (1,3);
insert into child values (2,1);
insert into child values (2,2);
insert into child values (2,3);
insert into child values (3,1);
insert into child values (3,2);
insert into child values (3,3);
insert into grandchild values (1,1);
insert into grandchild values (1,2);
insert into grandchild values (1,3);
insert into grandchild values (2,1);
insert into grandchild values (2,2);
insert into grandchild values (2,3);
insert into grandchild values (3,1);
insert into grandchild values (3,2);
insert into grandchild values (3,3);
SELECT (
SELECT COUNT(*)
FROM parent
) AS parent_cnt,
(
SELECT COUNT(*)
FROM child
) AS child_cnt,
(
SELECT COUNT(*)
FROM grandchild
) AS grandchild_cnt
FROM dual
PARENT_CNT CHILD_CNT GRANDCHILD_CNT
3 9 9
DELETE from parent where value = 'a';
SELECT (
SELECT COUNT(*)
FROM parent
) AS parent_cnt,
(
SELECT COUNT(*)
FROM child
) AS child_cnt,
(
SELECT COUNT(*)
FROM grandchild
) AS grandchild_cnt
FROM dual
PARENT_CNT CHILD_CNT GRANDCHILD_CNT
2 6 6
Here is a query you can use to show the relationships
with f as (
select constraint_name, table_name, r_constraint_name
from user_constraints
where constraint_type = 'R'
),
p as (
select constraint_name, table_name
from user_constraints
where constraint_type = 'P'
),
j (child_table, f_key, parent_table, p_key) as (
select f.table_name, f.constraint_name, p.table_name, f.r_constraint_name
from p join f on p.constraint_name = f.r_constraint_name
union all
select 'PARENT', (select constraint_name from p where table_name = 'PARENT'), null, null from dual
)
select level as lvl, j.*
from j
start with parent_table is null
connect by nocycle parent_table = prior child_table
order by lvl, parent_table, child_table;
LVL CHILD_TABLE F_KEY PARENT_TABLE P_KEY
1 PARENT PARENT_PK - -
2 CHILD PARENT_CHILD_FK PARENT PARENT_PK
3 GRANDCHILD CHILD_GRANDCHILD_FK CHILD CHILD_PK
So now when we delete from parent Oracle will check if there are tables with FKs referencing parent and will find table child. Then it will check one-by-one if to be deleted table parent row has children in table child. If not it will delete that table parent row. If there are children if will check FK action and since that FK has ON DELETE CASCADE Oracle will try to delete that table child row. At that point it will (same as with table parent) check if there are tables with FKs referencing table child and will find table grandchild. Same way it will check one-by-one if to be deleted table child row has children in table grandchild. If not it will delete that table child row. If there are children if will check FK action and since that FK has ON DELETE CASCADE Oracle will try to delete that table grandchild row. Since table grandchild has no child tables Oracle will delete corresponding rows in tables grandchild, child and then parent.