CODEWITHSUNDEEP
mysqlsql-serverdatabase-designforeign-keysunique-constraint
Walter
Walter

Reputation: 369

enforce uniqueness and define foreign key constraint from multiple tables

this question is applicable on different RDBMS: MySQL and SQL Server

I've been searching for this all night and I can't find it on the net. My problem is all about uniqueness and foreign keys. Consider the following schema:

CREATE TABLE Male
(
    StudentID INT,
    FirstName VARCHAR(20) NOT NULL,
    MiddleName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    -- other columns ...
    CONSTRAINT male_PK PRIMARY KEY (StudentID),
    CONSTRAINT male_UQ UNIQUE (FirstName, MiddleName, LastName)
);

CREATE TABLE Female
(
    StudentID INT,
    FirstName VARCHAR(20) NOT NULL,
    MiddleName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    -- other columns ...
    CONSTRAINT Female_PK PRIMARY KEY (StudentID),
    CONSTRAINT Female_UQ UNIQUE (FirstName, MiddleName, LastName)
);

CREATE TABLE ClassList
(
    ClassID INT,
    Name VARCHAR(30),
    ClassYear INT,
    -- other columns ...
    CONSTRAINT ClassList_PK PRIMARY KEY (ClassID),
    CONSTRAINT ClassList_UQ UNIQUE (Name, ClassYear)
);

The reason why table Male and Female are separated is because they are maintained by different sql accounts.

The problem now is the Association Class.

CREATE Student_Class
(
    SudentID INT,
    ClassID INT,
    CONSTRAINT tb_UQ UNIQUE (StudentID, ClassID)
)

So my questions are:

Suggestions are also Accepted.

Thank You

Upvotes: 2

Views: 887

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

This is why attribute splitting is bad - it starts "infecting" the other tables in odd ways.

You can have:

CREATE Student_Class
(
    MaleStudentID INT NULL,
    FemaleStudentID INT NULL,
    ClassID INT NOT NULL,
    CONSTRAINT tb_UQ UNIQUE (MaleStudentID, FemaleStudentID, ClassID)
)

Since MySQL, so far as I'm aware, doesn't support CHECK constraints, you'll also have to create a trigger that ensures only one, and exactly one, of MaleStudentID and FemaleStudentID is NOT NULL.

You can now apply your foreign key constraints in an obvious manner.

For SQL Server, you would have a check constraint:

ALTER TABLE Student_Class ADD CONSTRAINT CK_Students_Nullability CHECK (
    (MaleStudentID is null and FemaleStudentID is not null) or
    (MaleStudentID is not null and FemaleStudentID is null)
)

Upvotes: 2

user1914530
user1914530

Reputation:

I don't think it's a great idea to let user privileges dictate your schema but if you have to then I think the idea mentioned by HerpaMoTeH is a good one. A third table with id, gender and with the id used as an FK in the gender specific tables and student_class.

Another solution is to just split your student_class table into two: male_student_class and female_student_class

Alternatively you could create 2 sets of stored procedures (male and female) for CRUD operations on a single student table. Then you would deny priviliges to the actual table for your users but grant them access to the appropriate set of stored procedures. Your schema would be simplified to:

CREATE TABLE Student
(
    StudentID INT,
    GenderId INT,
    FirstName VARCHAR(20) NOT NULL,
    MiddleName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    -- other columns ...

);


CREATE Student_Class
(
   SudentID INT,
   ClassID INT,
  CONSTRAINT tb_UQ UNIQUE (StudentID, ClassID)
)

Upvotes: 1

Raj
Raj

Reputation: 10853

You can:

Set a range for StudentID to identify Male / Female. For example 10000 to 19999 for male and 20000 to 29999 for female

Alternately, you can add a redundant column "Sex" in both tables, with M for male and F for female and make that part of the reference

Raj

Upvotes: 1

Related Questions