Adding a CONSTRAINT to a Partial Primary Key

Question

I am relatively new to using SQL, and MySQL in specific.

I have the following table definition, and need to add a general constraint to it which I will explain shortly.

    CREATE TABLE `ProjInvestigat` (
    `projectID` INT,
    `principalInvestigator` NUMERIC(8,0) ZEROFILL,
    `coInvestigator` NUMERIC(8,0) ZEROFILL,
    PRIMARY KEY (projectID, principalInvestigator, coInvestigator)
);

I can have multiple instances of a projectID and principalInvesigator in the table, as long as the coInvesigator is different. i.e.

     projID principID   coInvestID  
     15     17          27      OKAY
     15     17          37      OKAY
     15     27          47      NOT OKAY, I need to prevent having two principal investigators on the same project.

I know to add a CONSTRAINT on the table definition, or in an ALTER TABLE statement, but what do I state to enforce this policy? How do I approach this?

Thanks for your help.

Each project is managed by one professor, aka the projects principal investigator, AND Professors can manage or work on multiple projects — mike, Apr 14 '16 at 20:34
You don't need to do anything. A `PRIMARY KEY` is also a `UNIQUE` constraint. FYI, the only kinds of constraints that MySQL supports are `UNIQUE KEY` and `FOREIGN KEY`. It will allow you to write other constraints, but ignores them. — Barmar, Apr 14 '16 at 21:03
@GordonLinoff I don't even think of that as a constraint, I consider it part of the datatype. — Barmar, Apr 14 '16 at 21:52
@Barmar . . . In fact, I do rather agree with you, but it turns out to technically be a constraint. — Gordon Linoff, Apr 15 '16 at 01:21

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

Before filling your base, you have to write a script which allows you to verify if a project has two main investigators or not. You can also look at this link , maybe it's helpful.

edited May 23 '17 at 11:59

Community

1
1

answered Apr 14 '16 at 20:53

Houssem ZITOUN

644
1
8
23

I should have also mentioned the definitions of the two base relations, here they are. BTW foreign key constraints were added after in an alter table statement. – mike Apr 14 '16 at 21:08
CREATE TABLE `Professors` ( `SNN` NUMERIC(8,0) ZEROFILL NOT NULL CHECK (VALUE BETWEEN 0 AND 99999999), `fName` VARCHAR(20), `lName` VARCHAR(20), `age` SMALLINT CHECK (VALUE BETWEEN 0 AND 99), `rank` VARCHAR(15), `researchSpeciality` VARCHAR(30), PRIMARY KEY (SNN) ); – mike Apr 14 '16 at 21:08
CREATE TABLE `Projects` ( `projectNumber` INT NOT NULL AUTO_INCREMENT, `sponsorName` VARCHAR(20), `projectName` VARCHAR(30), `startingDate` DATE CHECK (VALUE > 2005-JAN-01), `endingDate` DATE CHECK (VALUE > 2005-JAN-01 AND VALUE > startingDate), `budget` DECIMAL(10,2), PRIMARY KEY (projectNumber) ); – mike Apr 14 '16 at 21:09
@mike If you have more details, put them in the question, not comments. – Barmar Apr 14 '16 at 21:53

Adding a CONSTRAINT to a Partial Primary Key

1 Answers1