Hi all,

Pretty new to MYSQL I have created some tables with no problem but having problems with this bit of code if anyone can help please.

create table Booking
(DelNo int not null,
EventNo int not null,
EventB&B bool,
BookingFee Decimal(5,3) not null,
primary key (DelNo))
foreign key (DelNo) references Delegate (DellNo))
primary key (EventNo)
foreign key (EventNo) references Event (EventNo))
engine = innodb;

insert into booking values
(true,20);

create table Event
(EventNo int not null,
EventDate date(yyyy-mm-dd) not null,
primary key (EventNo))
foreign key (HotelNo) references Hotel (HotelNo))
engine = innodb;

insert into booking values
(1,2008-10-19);

I probabley made some silly Mistakes but being new cant seem to see them.

It looks like you're missing commas at the end of a few statements and you aren't closing the opening parenthesis. I think you also have a few parenthesis where you don't need them too.

Tip: PhpMyAdmin works great especially when you are just starting out, usually in your hosting package.

Originally Posted by millwalll

EventB&B bool,

SQL Server would never allow that without [brackets] around the column name, telling the server this is an identifier. In MySQL you use rabbit ears instead of brackets to do that.

Also, you're missing some commas on the ends of a few lines.

Hi ive sorted the code out bit as saw few mistake here the new code with the problems I am geting any help be cool thanks

create table Booking
DelNo int not null,
EventNo int not null,
EventBB bool,
BookingFee Decimal(5,3) not null,
primary key (DelNo)
foreign key (DelNo) references delegate (DelNo)
primary key (EventNo)
foreign key (EventNo) references Event (EventNo))
engine = innodb;

Error I get
Command failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'foreign key (DelNo) references delegate (DelNo) primary key (Ev

the second problem I have is I have used this code

create table event
(EventNo int not null,
EventDate varchar(10) not null,
HotelNo int not null,
primary key (EventNo),
foreign key (HotelNo) references hotel (HotelNo))
engine = innodb;

When I try use this code to enter data I get this error

insert into event values
(1,19-10-2008);

Error

Command failed: Column count doesn't match value count at row 1

any help be amazing and again sorry if these are silly question I am very new

With the first table you're missing the opening parenthesis

create table Booking
DelNo int not null,

should be

create table Booking
(DelNo int not null,

followed by the rest.

With the second table the way you're inserting won't work. You're trying to insert 2 values into the table, but MySQL doesn't know which fields. Since you didn't specify it assumes you want to add values to all the columns. You have 3 columns in the table, but you're only inserting 2 values.

You either need to insert the 3rd value for HotelNo or specify which columns you want to insert values into like this:

insert EventNo, Event Date into event values (1,19-10-2008);

create table Booking
(DelNo int not null,
EventNo int not null,
EventB&B bool,
BookingFee Decimal(5,3) not null,
primary key (DelNo)
foreign key (DelNo) references delegate (DelNo)
primary key (EventNo)
foreign key (EventNo) references Event (EventNo))
engine = innodb;

that code I have tried and still get same problem

as for the second bit the reason I am only inserting 2 bits of data is becasue

create table event
(EventNo int not null,
EventDate varchar(10) not null,
HotelNo int not null,
primary key (EventNo),
foreign key (HotelNo) references hotel (HotelNo))
engine = innodb;

the first bit is the id number what is 1 and the second bit is the data as for the HotelNo I thought that by using that as foreign key it would get the vales u from table and display them so would not need to enter data for it if that makes sense.

problem 2 first:

UNLESS you are filling ALL fields with values, you MUST specify a field list. In your example you have three columns and only two values.
And if you omit the field list the values MUST be the same order as the columns.

and

 
create table Booking(
DelNo int not null,
EventNo int not null,
EventBB bool,
BookingFee Decimal(5,3) not null,
foreign key (DelNo) references delegate(DelNo),
foreign key (EventNo) references Event (EventNo),
primary key (DelNo, EventNo)
)
engine = innodb;

note the addition of commas

ok sorted one problem out bit confussed about the other one this is the code

create table event
(EventNo int not null,
EventDate varchar(10) not null,
HotelNo int not null,
primary key (EventNo),
foreign key (HotelNo) references hotel (HotelNo))
engine = innodb;

Now the first line is the EventNo and next is EventDate so this is the code I am using

insert into event values
(1,19-10-2008);

The reason I have only 2 bits of information in this is I thought that HotelNo is a foreign key so it would go to my hotel table and get the information out of the HotelNo or is this incorrect?

Many thanks

it's incorrect

how would the query know which hotel number to get if you had 50,000 in the table?

It seems like you misunderstand the purpose of foreign keys, they are there to maintain the referential integrity of the data. So if you deleted HotelNo xx and records still existed in "event" that referenced that particular record it would raise an error.

Thanks one more question when I remove the HotelNo from the code like this

create table event
(EventNo int not null,
EventDate varchar(10) not null,
primary key (EventNo),
foreign key (HotelNo) references hotel (HotelNo))
engine = innodb;

I get this error Command failed: Key column 'HotelNo' doesn't exist in table
I know how to fix this error but dont really want column called HotelNo in this table as it will only confuss things would this work

foreign key references hotel (HotelNo))

many thank

it's a little difficult to have a foreign key on a column that isn't in the table.

Nope. A correction.

It's impossible.

ok thanks