Query 4. Write one DML statement to add data of the following six projects into

Question

Query 4.

Write one DML statement to add data of the following six projects into Project table.

ProjNum Pname Budget

------- ----- --------

p1 UH28K 1890.00

p2 UB555 1500.00

p3 UB5AA 2009.00

p4 UH200 11000.00

p5 UH2XG 9000.00

p6 UB5J6 4400.00

Query 5.

When Query 4 is successfully executed, write one DDL statement to add a foreign key constraint called 'FK_ProjAss_Proj' in ProjAssignment table. It should define Pno as a foreign key which references the ProjNum of Project.

After executing this statement, updating a ProjNum value in Project should automatically update the Pno to the same in ProjAssignment. However, with the constraint enforced, a project cannot be deleted if its ProjNum value is referenced by Pno of one or more rows of ProjAssignment.

Explanation / Answer

Query 4) Assuming Table project has been already created and ProjNum is declared as the primary key. Multiple rows are added to the table Project using single insert statememt.

INSERT INTO Project (ProjNum, Pname, Budget)
VALUES
('p1','UH28K',1890.00),
('p1','UB555', 1500.00),
('p3', 'UB5AA', 2009.00),
('p4','UH200',11000.00),
('p5','UH2XG',9000.00),
('p6','UB5J6', 4400.00);

Query 5) Alter table command is used to add the foreign key constraint to the existing table ProjAssignment. Pno field of table ProjAssignment is created as a foreign key referencing ProjNum of Project table.

ALTER TABLE ProjAssignment
ADD CONSTRAINT FK_ProjAss_Proj
FOREIGN KEY (Pno) REFERENCES Project (ProjNum);

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.