SQL server altering/adding foreign key?

Question

CREATE TABLE Department
( DeptNo NUMBER(5),
DName VARCHAR2(30),
Location VARCHAR2(30),
CONSTRAINTS Department_pk PRIMARY KEY (DeptNo));

CREATE TABLE Lecturer
( LectID NUMBER(5),
LName VARCHAR2(30),
Research VARCHAR2(30),
DeptNo NUMBER(5),
CONSTRAINTS Lecturer_pk PRIMARY KEY(LectID),
CONSTRAINTS Lecturer_fk FOREIGN KEY(DeptNo) REFERENCES Department(DeptNo));

•Assume a new relationship is created between LECTURER and DEPARTMENT to illustrate that a department is managed by a lecturer and a lecturer can only manage one department.


Now, I have to add LectID as a new column in DEPARTMENT’s table which is:
ALTER TABLE Department
ADD (LectID NUMBER(5));

how do I add a FOREIGN KEY constraint for LectID to be referenced to LECTURER in DEPARTMENT’s table? I tried this but it didn't work....

ALTER TABLE Department
ADD CONSTRAINTS LectID_fk FOREIGN KEY (LectID) REFERENCES Department (LectID));

Answer 1

You are already having DeptNo as your primary key in Department table you first have to remove that

as for table only one primary can be held or composite primary key





ALTER TABLE tablename

DROP CONSTRAINT constraintname



now assign your deptid as primary key for table department by adding constraint



at last run the last sql of your questions ...



hope it helps

Answer 2

in case you utilize a distant places key with a special constraint, you've only a million get entry to with a null value.. as an instance CREATE table type ( identity INT NULL regularly happening KEY ) CREATE table SubCategory ( CategoryID INT not NULL distant places KEY REFERENCES (type.identity) ) you are able to insert information on SubCategory with a null value for CategoryID, yet a million get entry to on type could have the identity null

Answer 3

ALTER TABLE Department

ADD CONSTRAINTS LectID_fk FOREIGN KEY (LectID) REFERENCES Lecturer(LectID));

Answer 4

in case you employ a distant places key with a distinctive constraint, you could have purely a million get right of entry to with a null fee.. as an occasion CREATE table class ( identity INT NULL customary KEY ) CREATE table SubCategory ( CategoryID INT no longer NULL distant places KEY REFERENCES (class.identity) ) you could insert information on SubCategory with a null fee for CategoryID, yet a million get right of entry to on class could have the identity null