MySQL Forums
Forum List  »  MySQL Workbench

Table Inheritance and One-to-Either Constraints
Posted by: Michael Reed
Date: April 22, 2009 08:48AM

I am trying to use WB and MySQL to implement one-to-either table inheritance as described by http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server. The article relates to MS SQL, but it should be able to implemented with MySQL.

In addition to other tables discussed by the article, there is a Peoples table and a Teachers table. Below is first the article's create statement, and then mine as outputted by WB. Can anyone confirm that they are effectively the same (other than the not null and auto increment stuff and the indexes which I added), and if not, what I need to change in WB?

Note that the article describes using a "persisted computed column", however, I believe this does not exist in MySQL, and I used the check constraint approach. I don't know if there is a better way to configure WB to add the constraint, and I just added it under the default value of the column.

Thanks!

create table People
(
PersonID int primary key,
PersonTypeID int references PersonType(PersonTypeID),
Name varchar(10)
constraint People_AltPK unique (PersonID,PersonTypeID)
)

CREATE TABLE IF NOT EXISTS `mydb`.`People` (
`PersonId` INT NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR(10) NOT NULL ,
`PersonTypeID` INT NULL ,
PRIMARY KEY (`PersonId`) ,
INDEX `fk_People_PersonType` (`PersonTypeID` ASC, `PersonId` ASC) ,
CONSTRAINT `fk_People_PersonType`
FOREIGN KEY (`PersonTypeID` , `PersonId` )
REFERENCES `mydb`.`PersonType` (`PersonTypeID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

create table Teachers -- SQL 2000 Version
(
PersonID int primary key,
PersonTypeID int not null default 2 check (PersonTypeID = 2), -- teachers type
HireDate datetime,
foreign key (PersonID, PersonTypeID) references People(PersonID, PersonTypeID)
)

CREATE TABLE IF NOT EXISTS `mydb`.`Teachers` (
`PersonID` INT NOT NULL ,
`PersonTypeID` INT NOT NULL DEFAULT 2 check (PersonTypeID=2) ,
`HireDate` DATETIME NOT NULL ,
INDEX `fk_Teachers_PersonType` (`PersonTypeID` ASC, `PersonID` ASC) ,
PRIMARY KEY (`PersonID`) ,
CONSTRAINT `fk_Teachers_PersonType`
FOREIGN KEY (`PersonTypeID` , `PersonID` )
REFERENCES `mydb`.`PersonType` (`PersonTypeID` , `PersonType` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

Options: ReplyQuote


Subject
Views
Written By
Posted
Table Inheritance and One-to-Either Constraints
11178
April 22, 2009 08:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.