Блог

Главная - Вопросы по программированию - Как создать столбец null или not-null, зависящий от значения другого столбца?

Как создать столбец null или not-null, зависящий от значения другого столбца?

#sql-server #db-first

#sql-server #db-first

Вопрос:

Я использую подход database first с EF core и пытаюсь найти чистое решение приведенной ниже проблемы —

Рассмотрим таблицу посещаемости учащихся (нерелевантные столбцы удалены) ниже, в которой хранится дата занятия и позволяет учащемуся вводить рейтинг своего класса —

 create table Student (
    Id int Identity(1, 1) not null,
    ClassDate smalldatetime not null,
    ClassRatingByStudent varchar(250) not null
)

Это веб-приложение, в котором система посещаемости школы автоматически заполняет приведенную выше таблицу при EOD, а затем учащийся (скажем, через несколько дней) должен добавить оценки класса. Когда таблица заполняется системой посещаемости школы, в столбце ClassRatingByStudent ничего нет. Затем, когда учащийся входит в систему, он должен добавить оценку.

Как вы видите, ClassRatingByStudent должно быть null, когда система посещаемости школы заполняет таблицу, и должно быть not-null, когда студент сохраняет свои изменения. Одно из очевидных решений — сделать столбец ClassRatingByStudent обнуляемым, чтобы обработать его в коде, но мне интересно, существует ли более аккуратное решение уровня базы данных (или, может быть, EF) или какие-то рекомендации по шаблону / архитектуре для сценариев такого типа?

Комментарии:

1. Я бы сделал это в коде.

2. Я бы определил ClassRatingByStudent as NULL и запрограммировал систему на интерпретацию NULL значений в этом столбце как показатель того, что учащийся еще не заполнил рейтинг. Здесь NULL значение имеет конкретное значение.

Ответ №1:

Я не знаю, но, возможно CHECK , ограничение может вам помочь:

 CREATE TABLE TestTable(
  ID int NOT NULL IDENTITY,
  RatingAllowed bit NOT NULL DEFAULT 0, -- switcher
  RatingValue varchar(250),
CONSTRAINT PK_TestTable PRIMARY KEY(ID),
CONSTRAINT CK_TestTable_RatingValue CHECK( -- constraint
                    CASE
                      WHEN RatingAllowed=0 AND RatingValue IS NULL THEN 1
                      WHEN RatingAllowed=1 AND RatingValue IS NOT NULL THEN 1
                      ELSE 0
                    END=1
                 )
)

INSERT TestTable(RatingAllowed,RatingValue)VALUES(0,NULL)
INSERT TestTable(RatingAllowed,RatingValue)VALUES(1,'AAA')

-- The INSERT statement conflicted with the CHECK constraint "CK_TestTable_RatingValue"
INSERT TestTable(RatingAllowed,RatingValue)VALUES(0,'AAA')
INSERT TestTable(RatingAllowed,RatingValue)VALUES(1,NULL)

Ответ №2:

Я нашел вариант, как проверить, используя другую таблицу в качестве переключателя

 CREATE TABLE TableA(
  ID int NOT NULL IDENTITY PRIMARY KEY,
  StudentID int NOT NULL,
  Grade int
)

CREATE TABLE TableB(
  StudentID int NOT NULL PRIMARY KEY
)
GO

-- auxiliary function
CREATE FUNCTION GradeIsAllowed(@StudentID int)
RETURNS bit
BEGIN
  DECLARE @Result bit=CASE WHEN EXISTS(SELECT * FROM TableB WHERE StudentID=@StudentID) THEN 1 ELSE 0 END
  RETURN @Result
END
GO

-- constraint to check
ALTER TABLE TableA ADD CONSTRAINT CK_TableA_Grade CHECK(
                CASE dbo.GradeIsAllowed(StudentID) -- then we can use the function here
                  WHEN 1 THEN CASE WHEN Grade IS NOT NULL THEN 1 ELSE 0 END
                  WHEN 0 THEN CASE WHEN Grade IS NULL THEN 1 ELSE 0 END
                END=1)
GO

-- Tests
INSERT TableB(StudentID)VALUES(2) -- allowed student

INSERT TableA(StudentID,Grade)VALUES(1,NULL) -- OK
INSERT TableA(StudentID,Grade)VALUES(2,5) -- OK

INSERT TableA(StudentID,Grade)VALUES(1,4) -- Error
INSERT TableA(StudentID,Grade)VALUES(2,NULL) -- Error

INSERT TableB(StudentID)VALUES(1) -- add 1

UPDATE TableA SET Grade=4 WHERE StudentID=1 -- OK
UPDATE TableA SET Grade=NULL WHERE StudentID=1 -- Error
Метки: