0

I'm currently implementing a database structure and I want to translate the following relation into sql:

BarberSchedule(SID, BUID)

SID references Schedule.SID

BUID references User.UID where User.Type=='Barber'

Is there a proper way to put that condition in this form? :

create table `BarberSchedule` (
    `SID` int not null,
    `BUID` varchar (16),
    primary key (`SID`, `BUID`) 
    foreign key (`SID`) references Schedule(SID)
    foreign key (`BUID`) references Users(UID) where Users.UType=='Barber',
)engine=innodb;

Thanks in advance,

Community
  • 1
  • 1
Dixel
  • 516
  • 1
  • 4
  • 9
  • 1
    Most likely you are looking for [CHECK Constraint](https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html) which only MySQL 8 supports.. In older MySQL version you can simulate/emulate that with [view with check option](https://dev.mysql.com/doc/refman/5.6/en/view-check-option.html) or by using [triggers](https://stackoverflow.com/questions/9734920/can-a-mysql-trigger-simulate-a-check-constraint).. – Raymond Nijland May 29 '19 at 12:17
  • If `CHECK` is not what you are looking for i would suggesting to edit you question and explain it better with a usercase and data example see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Raymond Nijland May 29 '19 at 12:20
  • Hopefully, it's what I'm looking for. Thanks a lot! – Dixel May 29 '19 at 12:27
  • What exactly do you want to achieve with `where User.Type=='Barber'`? Can you elaborate? – The Impaler May 29 '19 at 15:37
  • @TheImpaler I would like BUID to reference Users.UID only if ´´Users.Utypte == 'Barber'´´ – Dixel Jun 04 '19 at 16:41
  • 1
    @Dixel Foreign Key constraints cannot do that. SInce this column is part of the primary key, by definition it cannot be null. Then, also by definition, a non-null foreign key always must have a matching key in the receiving end. The only workaround I see is to create the foreign key as another **nullable** column that is not part of the PK, and then add a `CHECK` constraint that checks your rule. – The Impaler Jun 04 '19 at 19:21

0 Answers0