CODEWITHSUNDEEP
sqldatabasedb2
TheShield
TheShield

Reputation: 307

Enforcing Total Participation in a One-To-Many Relationship on the one sided entity? | SQL

I am in the process of translating the following ER diagram into SQL code:

enter image description here

To model this, I created a table for each respective entity as well as a table for the relationship. My problem is modelling the one-to-many relationship as well as enforcing the total participation constraint on the Medical Test entity.

I begin with the Person entity:

-- Person Entity
CREATE TABLE Person (
uniquePersonID CHAR(10) NOT NULL,
gender CHAR(1),         -- "M" = Male | "F" = Female
firstName VARCHAR(30),
lastName VARCHAR(30),
dateOfBirth DATE,
PRIMARY KEY (uniquePersonID));

I assign the ID as NOT NULL because it is a primary key.

Followed by the Medical Test entity:

CREATE TABLE MedicalTest (
testID CHAR(10) NOT NULL,
testFee REAL,
testName VARCHAR(30),
PRIMARY KEY (testID));

Now comes the part where I am struggling with, the Does Test relationship:

-- Does Test Relationship
CREATE TABLE DoesTest (
uniquePersonID CHAR(10) NOT NULL,
testID CHAR(10),
testDate DATE,
result VARCHAR(100),
PRIMARY KEY (testID),
FOREIGN KEY (uniquePersonID) REFERENCES Person,
FOREIGN KEY (testID) REFERENCES MedicalTest);

From what I have learned, if for instance, I want to make Medical Test have total participation, I make the Patient Primary Key be NOT NULL. To model the one-to-many, I assign the primary key to the one side entity. However, when I run the file, it tells me testID in the relationship table can not allow NULL values. However, from what I have been taught, if I assign it NOT NULL, that would force it to have total participation, something I am trying to avoid.

The error:

CREATE TABLE DoesTest ( testDate DATE, result VARCHAR(100), testID CHAR(10), 
uniquePersonID CHAR(10) NOT NULL, PRIMARY KEY (testID), FOREIGN KEY 
(uniquePersonID) REFERENCES Person, FOREIGN KEY (testID) REFERENCES 
MedicalTest)
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0542N  The column named "TESTID" cannot be a column of a primary key or 
unique key constraint because it can contain null values.  SQLSTATE=42831

Upvotes: 1

Views: 4441

Answers (1)

The Impaler
The Impaler

Reputation: 48850

The primary key of a table NEVER accepts null values, and should always include the NOT NULL constraint.

It should work if you change your primary key to:

CREATE TABLE DoesTest (
  testDate DATE,
  result VARCHAR(100),
  testID CHAR(10) NOT NULL, -- added NOT NULL here.
  uniquePersonID CHAR(10) NOT NULL,
  PRIMARY KEY (testID),
  FOREIGN KEY (uniquePersonID) REFERENCES Person,
  FOREIGN KEY (testID) REFERENCES MedicalTest
);

I don't get exactly what you mean by "total participation" but the NOT NULL constraint is validated only for existing rows. Not all patients must have a corresponding row in DoesTest. Those would model patients who haven't yet taken the test, and that's perfectly normal.

Upvotes: 1

Related Questions