CODEWITHSUNDEEP
sqlsql-serverdatabase-design
Ian K
Ian K

Reputation: 91

How to implement a double constraint in SQL Server?

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
);

Upvotes: 0

Views: 414

Answers (2)

marc_s
marc_s

Reputation: 754268

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
);

Upvotes: 4

Schwern
Schwern

Reputation: 164679

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

Upvotes: 2

Related Questions