SQL Server adding constraints

Question

I have the following two tables

Create table Exam
(
    coursecode nvarchar(6) Not Null Constraint Pk_courseCode Primary Key (coursecode),
    [examiner-name] [varchar](25) ,
    [date] date not null,
    slot varchar(2),

)

Alter table Exam with check 
  add constraint [coursecode_ck_validity] check ((coursecode like '[A-Z][A-Z][A-Z][0-9][0-9][0-9]'))

Alter table Exam with check 
  add constraint [date_ck_validity] check  (Month([date]) = 5 or Month([date]) = 11)

Alter table Exam with check 
  add constraint [slot_ck_validity] check (slot in ('AM', 'PM'))

And this table Student

Create table Student
(
    [script-id] int identity(1,1) not null Constraint [PK_Student] Primary key ([script-id]),
    [student-id] char(8) not null,
    [course-code] nvarchar(6) Not Null constraint [FK_Student] foreign key([course-code]) references Exam ([course-code]),
    [seat-no] int not null constraint [seatno_ck_validity] check ([seat-no] between 1 and 3000 )

)

And to add on to the above statements I have additional constraints that I have to apply, but not sure how to to apply these constraints

Constraints

• Delete to course-code is disallowed and update to course-code is cascaded.

• Minimum cardinality between Exam and Student is M:O.

Thanks

Answer 1

Delete to course-code is disallowed and update to course-code is cascaded

Do you mean Delete to Exam is disallowed? If so, do not grant (or actively deny) Delete on the Exam table.

update to the course-code is cascaded

Use Cascading Referential Integrity Constraints:

Create table Student
(
    -- ...
    [course-code] nvarchar(6) Not Null 
        Constraint [FK_Student] foreign key([course-code]) 
        References Exam ([course-code]) /***/ On Update Cascade /***/
    -- ...
)

... and your second condition...

Minimum cardinality between Exam and Student is M:O.

I am not sure what issue you are attempting to solve with this condition. Your Not Null and Foreign Key constraint on Student.course-code should be all that is needed.

As an aside, be sure to have a consistent naming convention; specifically, compare Exam.coursecode and Student.course-code. Also, underscores are much more prevalent than hyphens in object names in the professional SQL world.