Re: Is mysql a RDBMS ?
Date: 26 Aug 2003 06:25:20 -0700
Message-ID: <60ca69db.0308260525.1a48a6e4_at_posting.google.com>
"Heikki Tuuri" <Heikki.Tuuri_at_innodb.com> wrote in message news:<0d12b.203$yu.178_at_read3.inet.fi>...
> Morten,
>
>
> "Morten Gulbrandsen" <Morten.Gulbrandsen_at_rwth-aachen.de> kirjoitti viestissä
> news:60ca69db.0308210016.822e230_at_posting.google.com...
> > Hello,
> >
> > I have only read Elmasri/Navathe and I find it nearly impossible
> > to implement any non-trivial Enhanced Entity relationship diagram
> > in MySQL,
> > according to the mailing list, important foreign key constraints are
> > not supported,
>
> foreign key constraints are supported in MySQL. Where did you find on the
> mailing list the claim that they are not?
mailing.database.mysql
I do not remember seeing that
> claim for a long time. InnoDB does not support ON ... SET DEFAULT, but that
> is not really a constraint, but an 'action', similar to a trigger.
Dear Mr. Heikki Tuuri ,
Please how can I code this 'action' ? If It is not a constraint,
we could identify it as an action, I still can't get this action
coded with mysql.
Please see mailing.database.mysql for details.
Yours sincerely
Morten Gulbrandsen
USE company;
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE
(
FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9), DNO INT NOT NULL DEFAULT 1,
CONSTRAINT EMP_PK
PRIMARY KEY (SSN),
CONSTRAINT EMP_SUPER_FK
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT EMP_DEPT_FK
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT ON UPDATE CASCADE)TYPE = INNODB;
DROP TABLE IF EXISTS DEPARTMENT;
CREATE TABLE DEPARTMENT
(
DNAME VARCHAR(15) NOT NULL, DNUMBER INT NOT NULL, MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',MGRSTARTDATE DATE, CONSTRAINT DEPT_PK
PRIMARY KEY (DNUMBER),
CONSTRAINT DEPT_SK
UNIQUE (DNAME),
CONSTRAINT DEPT_MGR_FK
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET DEFAULT ON UPDATE CASCADE)TYPE = INNODB; DROP TABLE IF EXISTS DEPT_LOCATIONS; CREATE TABLE DEPT_LOCATIONS
(
DNUMBER INT NOT NULL, DLOCATION VARCHAR(15) NOT NULL, PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER) ON DELETE CASCADE ON UPDATE CASCADE)TYPE = INNODB; DROP TABLE IF EXISTS PROJECT; CREATE TABLE PROJECT
(
PNAME VARCHAR(15) NOT NULL,
PNUMBER INT NOT NULL,
PLOCATION VARCHAR(15),
DNUM INT NOT NULL,
PRIMARY KEY (PNUMBER),
UNIQUE (PNAME),
FOREIGN KEY (DNUM) REFERENCES DEPARTMENT (DNUMBER))TYPE = INNODB; DROP TABLE IF EXISTS WORKS_ON; CREATE TABLE WORKS_ON
(
ESSN CHAR(9) NOT NULL, PNO INT NOT NULL, HOURS DECIMAL(3,1) NOT NULL, PRIMARY KEY (ESSN, PNO),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (PNO) REFERENCES PROJECT (PNUMBER))TYPE = INNODB; DROP TABLE IF EXISTS DEPENDENT; CREATE TABLE DEPENDENT
(
ESSN CHAR(9) NOT NULL,
DEPENDENT_NAME VARCHAR(15) NOT NULL,
SEX CHAR, BDATE DATE,
RELATIONSHIP VARCHAR(8),
PRIMARY KEY (ESSN, DEPENDENT_NAME),
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN))TYPE = INNODB;
If I remove the Type=innodb; assignment,
then mysql parses through the code,
but no expected database behaviour is actually compiled.
I also miss a detailed warning level option and error level option.
Also creating mutually dependencies is nearly impossible,
without first creating tables without any relationship,
and then altering the tables to incorporate the relationship.
I feel that My SQL is not prepared for ANSI SQL2 code,
foreign keys and foreign table references is still
'foreign' to MySQL. With or without InnoDB;
Please help,
any workaround will be highly appreciated,
Yours Sincerely
Morten Gulbrandsen