DROP TABLE IF EXISTS `Person`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; /*create a new table Person;*/

create table Person (PersonCode varchar(255) not null , PersonName varchar(255) , PersonAddressID int ,emailID int, primary key (PersonCode));

-- make Person can not insert the same value

alter table Person add constraint PersonCode unique (PersonCode);

/*add foreign key into Person*/

alter table Person add foreign key (emailID) references email(emailID);

alter table Person add foreign key (PersonAddressID) references address(PersonAddressID);

-- insert index into Person

ALTER TABLE Person ADD INDEX stupid1 (PersonCode,PersonName,PersonAddressID);

/*insert Check method in Person*/

ALTER TABLE Person ADD CHECK (PersonAddressID>0);

-- insert default method in Person

ALTER TABLE Person ALTER PersonName SET DEFAULT 'Sydyne';

DROP TABLE IF EXISTS `email`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; /*create a new table email;*/

create table email(emailID int,email1 varchar(255),email2 varchar(255),email3 varchar(255),email4 varchar(255));

alter table email add primary key (emailID);

alter table email add constraint emialID unique (emailID);

Always show errors----

ERROR 1005 (HY000) at line 275: Can't create table `cse156`.`email` (errno: 150 "Foreign key constraint is incorrectly formed")