How to implement a double constraint in SQL Server?

Question

I am designing a database project that holds records for an alumni association. My goal was to make sure that the names in the awards table only come from those who have been nominated in the alumni table (where Award_Nominated acts as a Boolean). I have tried using two booleans, one for nomination and another for winning, but that still leaves a possibility for a logical error, i.e. one can be an award winner without getting nominated. My question is how can I make sure that the award winner names only come from the alumni name values with Award_nominated as true.

The relevant tables are declared below:

CREATE TABLE Alumni 
(
    Alumni_ID int NOT NULL IDENTITY(1,1),
    First_Name varchar(20) UNIQUE NOT NULL,
    Last_Name varchar(20) UNIQUE NOT NULL,
    Graduation_Year int NOT NULL,
    Course_taken varchar(255) NOT NULL,
    Award_Nominated bit,
    Phone_Number int NOT NULL,
    Email_Address varchar(255) NOT NULL,

    PRIMARY KEY (Alumni_ID, First_Name, Last_Name)
);

CREATE TABLE Awards 
(
    Award_year int NOT NULL,
    Chapters varchar(255),
    Award_Winner_First_Name varchar(20) NOT NULL,
    Award_Winner_Last_Name varchar(20) NOT NULL,

    FOREIGN KEY (Award_Winner_First_Name) REFERENCES Alumni(First_Name),
    FOREIGN KEY (Award_Winner_Last_Name) REFERENCES Alumni(Last_Name),

    Award_purpose varchar(255) NOT NULL
);

Your Alumni table has a design problem, because the first and last name columns should not be unique. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 6, 2021 at 9:34

marc_s · Accepted Answer · 2021-03-06 09:37:05Z

4

Couple points:

simplify the Alumni table's primary key to be just the Alumni_ID
Don't make first and last name unique - really bad idea ....
reference the Awards table to the Alumni table solely based on the Alumni_ID - not on first and last name ....
don't duplicate the first and last name of the alumni into the Awards table - if need be, you can get it be a JOIN to the Alumni table

I'd go with this:

CREATE TABLE dbo.Alumni 
(
    Alumni_ID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Alumni PRIMARY KEY CLUSTERED,
    First_Name varchar(20) NOT NULL,
    Last_Name varchar(20) NOT NULL,
    Graduation_Year int NOT NULL,
    Course_taken varchar(255) NOT NULL,
    Award_Nominated bit,
    Phone_Number int NOT NULL,
    Email_Address varchar(255) NOT NULL
);

CREATE TABLE dbo.Awards 
(
    Award_year int NOT NULL,
    Alumni_ID int NOT NULL 
        CONSTRAINT FK_Award_Alumni REFERENCES dbo.Alumni(Alumni_ID),
    Chapters varchar(255),
    Award_purpose varchar(255) NOT NULL
);

answered Mar 6, 2021 at 9:37

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sa-es-ir Over a year ago

It requires a boolean column Winner for specify who win the award.

marc_s Over a year ago

@SaeedEsmaeelinejad: sorry?? No - if an alumni is referenced from the Awards table - he (or she) got this award. It's not a "nominees" table - it's the Awards table - awards that have been given to certain alumni ....

Ian K Over a year ago

Thanks, but I still don't know how to implement the constraint for the award winners.

marc_s Over a year ago

You should ensure (in your application) that only alumni who have been nominated can be stored in the awards table - that's not really easily doable in just database constraints...

Schwern · Accepted Answer · 2021-03-06 09:36:19Z

Don't duplicate the names in the Awards table. Reference the Alumni ID and join.

CREATE TABLE Alumni (
Alumni_ID int  not null identity(1,1),

-- It's possible to have two alumni with the same name.
-- And also to have names longer than 20 characters.
First_Name varchar(255) not null,
Last_Name varchar(255) not null,
Graduation_Year int not null,
Course_taken varchar(255) not null,
Phone_Number int not null,
Email_Address varchar(255) not null,

-- Just the Alumni_ID is sufficient
PRIMARY KEY (Alumni_ID)
);

CREATE TABLE Awards (
Award_year int not null,
Chapters varchar(255),
-- Only reference the ID
Alumni_ID int not null,
FOREIGN key (Alumni_ID) REFERENCES Alumni(Alumni_ID),
Award_purpose varchar(255) not null
);

Then join with Alumni to get their names.

select First_Name, Last_Name, Awards.*
from Awards
join Alumni on Awards.Alumni_ID = Alumni.Alumni_ID

Collectives™ on Stack Overflow

How to implement a double constraint in SQL Server?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related