CODEWITHSUNDEEP
sqlcreate-tableerd
Di Wang
Di Wang

Reputation: 481

SQL: How to make participation constraint in a table

Let's say, bank schema is as follows:

Employee = {*ID*,salary}
Branch = {*BranchNum*, Location}

If the relation has both participation constraint and key constraint:

Employee ==> workAt -- Branch  //every employee must work at one branch

I can force a participation constraint, by setting BranchNum as NOT NULL:

CREATE TABLE employee (
    ID           INTEGER,
    BranchNum    INTEGER     NOT NULL, 
    PRIMARY KEY (ID)
    FOREIGN KEY (ID) REFERENCES Employee
    FOREIGN KEY (BranchNum) REFERENCES Branch
)

However, If there is Only participation constraint but No key constraint:

Employee === workAt -- Branch  //every employee must work at one or some branches

How do I put participation constraint when creating a table?

Upvotes: 0

Views: 4804

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

If you want:

//every employee must work at one or some branches

Then you have a 1-n relationship. You would implement this with a junction table:

CREATE TABLE EmployeeBranches (
    EmployeeBranchId INTEGER AUTO_INCREMENT PRIMARY KEY,
    EmployeeID INTEGER NOT NULL,
    BranchNum INTEGER NOT NULL,
    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeId)
    FOREIGN KEY (BranchNum) REFERENCES Branch(BranchNum)
);

Upvotes: 1

Related Questions