MySQL Create Statements

Cork Skate

Hi there,

Trying to create tables in MySQL and i'm have a bit of trouble.

CREATE TABLE ***** (
name varchar(20) not null not default,
etc.


And it doesn't seem to take the 'not default' ... do i just leave it out ??

Ph3n0m

you have to use the following

CREATE TABLE ******  (
  username varchar(255) NOT NULL default '',
  password varchar(255) NOT NULL default '',
  folder_name varchar(255) NOT NULL default '',
  last_files varchar(255) NOT NULL default '',
  last_login varchar(255) NOT NULL default '',
  new_login varchar(255) NOT NULL default '',
  date_added varchar(255) NOT NULL default ''
) TYPE=MyISAM;

Cork Skate

So the '' (which is nothing) is the 'not' in 'not default' ..... if you know what i mean !?!!

Also .... can i say 'With Null' if i need a null ??

Its OK ..... its either 'not null' or just 'null' on its own.

Also


Can i just use TYPE-InnoDB; ..... or is there more to it ??

rsynnott

Cork Skate wrote:

Can i just use TYPE-InnoDB; ..... or is there more to it ??

You can if you want, but unless you have good reason to, don't. Do you need transactions?

Cork Skate

rsynnott wrote:

You can if you want, but unless you have good reason to, don't. Do you need transactions?

Transactions ? ... do you mean foreign keys ? .... yeah !!

I will have 12 tables and 7 of them use foreign keys.

Cork Skate

I have all my CREATE statements done now and i'm trying to build the tables now but it wont create one or two of them saying there is a problem with the syntax of the statements, even though i am using the exact same syntax for other ones that i can create.

Can some take a look at them ??

aidan_walsh

Not unless you post them? :rolleyes: C'mon, you must know how this works by now... We're not here to offer one-to-one help. You want that, pay a tutor...

Cork Skate wrote:

Transactions ? ... do you mean foreign keys ?

No, he meant Transactions.

Cork Skate

doodle_sketch wrote:

Not unless you post them? :rolleyes: C'mon, you must know how this works by now... We're not here to offer one-to-one help. You want that, pay a tutor...

Yeah ... fair play, i'd say it is a small syntax error just in them all.
I'll post them up now.

No, he meant Transactions.

Cheers for that.

Cork Skate

CREATE TABLE Member(
MemberNo		integer		not null,
FIrstName		Varchar(32)	not null default '',
Surname			Varchar(32)	not null default '',
PlaceOfBirth		Varchar(32)	null default '',
Nationality		Varchar(32)	null default '',
ClubNo			integer		null default '',
CONSTRAINT MemberNo_Required PRIMARY KEY(MemberNo),
CONSTRAINT PresentClub_check FOREIGN KEY (ClubNo) REFERENCES Club(ClubNo)
)TYPE=InnoDB;
COMMIT;					


CREATE TABLE Positions( 
ID  tinyint not null  PRIMARY KEY, 
Position varchar(11) not null 
) TYPE=InnoDB;
COMMIT;


insert into Positions values
 ( 1,'Keeper' )
,( 2,'Defender' )
,( 3,'Midfielder' )
,( 4,'Forward' )
,( 5,'Varied' );
COMMIT;


CREATE TABLE PlayerLeg( 
ID  tinyint not null  PRIMARY KEY, 
Legged varchar(8) not null 
) TYPE=InnoDB;
COMMIT;


insert into PlayerLeg values
( 1,'Left' ),
( 2,'Right' ),
( 3,'Both' );
COMMIT;


CREATE TABLE Player( 
PlayerNo  integer  not null, 
MemberNo  integer  not null, 
DatOfBirth  date  null , 
Weight    varchar(32) null , 
Height   varchar(32) null , 
Legged   tinyint not null, 
Position  tinyint not null, 
INDEX Member_index (MemberNo), 
INDEX Position_index (Position), 
INDEX Legged_index (Legged), 
CONSTRAINT Player_Required PRIMARY KEY(PlayerNo, MemberNo), 
CONSTRAINT Member_check FOREIGN KEY (MemberNo) REFERENCES Member(MemberNo)
CONSTRAINT PlayerPosition_check FOREIGN KEY (Position) REFERENCES Positions(ID)
CONSTRAINT PlayerLegged_check FOREIGN KEY (Legged) REFERENCES PlayerLeg(ID)
) TYPE=InnoDB;
COMMIT;


CREATE TABLE Manager(
ManagerNo		integer		not null,
MemberNo		integer		not null,	
Credentials		Varchar(64)	null default '',		
CONSTRAINT Manager_Required PRIMARY KEY(ManagerNo,MemberNo),
CONSTRAINT Member_check FOREIGN KEY (MemberNo) REFERENCES Member(MemberNo)
)TYPE=InnoDB;
COMMIT;	


CREATE TABLE Coach(
CoachNo			integer		not null,
MemberNo		integer		not null,	
Credentials		Varchar(64)	null default '',		
CONSTRAINT Coach_Required PRIMARY KEY(CoachNo,MemberNo),
CONSTRAINT Member_check FOREIGN KEY (MemberNo) REFERENCES Member(MemberNo)
)TYPE=InnoDB;
COMMIT;	


CREATE TABLE MemberHistory(
MemberNo		integer		not null,
ClubNo			integer		not null,
StartDate		Date		null default '',
EndDate			Date		null default '',
CONSTRAINT MemberHistory_Required PRIMARY KEY(MemberNo,ClubNo),
CONSTRAINT Member_check FOREIGN KEY (MemberNo) REFERENCES Member(MemberNo)
CONSTRAINT PreviousClub_check FOREIGN KEY (ClubNo) REFERENCES Club(ClubNo)
)TYPE=InnoDB;
COMMIT;


CREATE TABLE StatusOfClub( 
ID  tinyint not null  PRIMARY KEY, 
Status varchar(12) not null 
) TYPE=InnoDB;
COMMIT;


insert into StatusOfClub values
( 1,'Fulltime' ),
( 2,'Semi Pro' ),
( 3,'Parttime' ),
( 4,'Amateur' );
COMMIT;



CREATE TABLE Club(
ClubNo			integer		not null,
ClubName		varchar(32)	not null,
DomesticLeague		varchar(32)	null default '',
ContactAddress		varchar(48)	null default '',
TelePhoneNo		varchar(20)	null default '',
FaxNo			varchar(20)	null default '',
EmailAddress		varchar(32)	null default '',
Website			varchar(32)	null default '',
Groundno		integer		null default '',
YearEstablished		char(4)		null default '',
ClubStatus		tinyint 	not null, 
ManagerNo		integer		null default '',
CoachNo			integer		null default '',
CONSTRAINT ClubNo_Required PRIMARY KEY(CLubNo),
CONSTRAINT Unique_ClubName UNIQUE (ClubName),
CONSTRAINT Ground_check FOREIGN KEY (GroundNo) REFERENCES Ground(GroundNo)
CONSTRAINT ClubStatus_check FOREIGN KEY (Status) REFERENCES StatusOfClub(ID)
CONSTRAINT ManagerNo_check FOREIGN KEY (ManagerNo) REFERENCES Manager(ManagerNo)
CONSTRAINT Coach_check FOREIGN KEY (CoachNo) REFERENCES Coach(CoachNo)
)TYPE=InnoDB;
COMMIT;


CREATE TABLE GroundHistory(
CLubNo			integer		not null,
GroundNo		integer		not null,
StartDate		Date		null default '',
EndDate			Date		null default '',
CONSTRAINT GroundHistory_Required PRIMARY KEY(ClubNo,GroundNo),
CONSTRAINT CurrentClub_check FOREIGN KEY (ClubName) REFERENCES Club(ClubNo)
CONSTRAINT PreviousGround_check FOREIGN KEY (GroundName) REFERENCES Ground(GroundNo)
)TYPE=InnoDB;
COMMIT;


CREATE TABLE Ground(
GroundNo		integer		not null,
GroundName		varchar(32)	not null,
ContactAddress		varchar(48)	null default '',
TelephoneNo		varchar(24)	null default '',
ClubNo			integer		null default '',
Capacity 		integer		null default '',
Seated			integer		null default '',
Terraced		integer		null default '',
Floodlights		tinyint		null default '',
Parking			tinyint		null default '',
CONSTRAINT GroundNo_Required PRIMARY KEY(GroundNo),
CONSTRAINT Unique_GroundName UNIQUE (GroundName),
CONSTRAINT CurrentClub_check FOREIGN KEY (ClubNo) REFERENCES Club(ClubNo)
)TYPE=InnoDB;
COMMIT;

Ph3n0m

It would be so much easier if you created all your table using a simple interface like phpMyAdmin (www.phpmyadmin.net)

tempest

What error message do you get?

Cork Skate

These are the error messges i get

ERROR 1005: Can't create table '.\mydatabasedb\member.frm' (errno: 150)
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.09 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'CONSTRAINT P
layerPosition_check FOREIGN KEY (Position) REFERENC
Query OK, 0 rows affected (0.00 sec)

ERROR 1005: Can't create table '.\mydatabasedb\manager.frm' (errno: 150)
Query OK, 0 rows affected (0.00 sec)

ERROR 1005: Can't create table '.\mydatabasedb\coach.frm' (errno: 150)
Query OK, 0 rows affected (0.00 sec)

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'CONSTRAINT P
reviousClub_check FOREIGN KEY (ClubNo) REFERENCES C
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.09 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'CONSTRAINT C
lubStatus_check FOREIGN KEY (Status) REFERENCES Sta
Query OK, 0 rows affected (0.00 sec)

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'CONSTRAINT P
reviousGround_check FOREIGN KEY (GroundName) REFERE
Query OK, 0 rows affected (0.00 sec)

ERROR 1005: Can't create table '.\mydatabasedb\ground.frm' (errno: 150)
Query OK, 0 rows affected (0.00 sec)

They all related to the Foreign keys from what i can see.

Cork Skate

I just wanna bump this up.

Cork Skate

Just wanna let you know that i have CREATEd each one individually and if have started with the Member ..... taking OUT the foreign key contraint, i'll add that in later.

I have been able to CREATE the Ground, Member, Player, Manager, Coach tables and i am having a problem with Club table right now but looking at it, it is holding another two tables ...... but its looking alot better now ..... i'll let you know how i get on.

Cork Skate

Just to let you know .... i got this sorted in the end.
All is well ....... if anyone has a problem/question with it ... just give me a shout and i'll try and help.

Cheers for all the help people !!