SQL to create tables with foreign keys

[sundaygirl]

Hello everyone,
I worked through my first database and have gone back to the assignment one about the court.
I seem to be having a great deal of trouble creating tables.
for example

create table(crown court),
crowncourtno (autonumber),
telno text (15),
numberofseats text(5),
constraints crowncourtno primary key_pk ));

I am using access 2003, it doesnt seem to like the brackets or the commas, and when i get rid of them it doenst like the names. I've been following advice on here, out of books and still its not working.

so please help.
sunday

[r035198x]

Hello everyone,
I worked through my first database and have gone back to the assignment one about the court.
I seem to be having a great deal of trouble creating tables.
for example

create table(crown court),
crowncourtno (autonumber),
telno text (15),
numberofseats text(5),
constraints crowncourtno primary key_pk ));

I am using access 2003, it doesnt seem to like the brackets or the commas, and when i get rid of them it doenst like the names. I've been following advice on here, out of books and still its not working.

so please help.
sunday

Do not post questions in the articles sections.
Moved to the forum section.

[sundaygirl]

so sorry I posted in the wrong place ( I was wondering why I hadnt had a reply)
I was wondering when creating tables in sql is the order you do them important and prehaps i should wait to create them all before runing them.
hope somone can put me on the right road.

sunday

[JKing]

Hi, I'll show you an example of creating a table in through SQL. The table is simple but you should be able to gather the format of things from it.

CREATE TABLE tblEmployee
(
empID COUNTER,
empFName TEXT(25) NOT NULL,
empLName TEXT(35),
deptID number,
CONSTRAINT pk_employee PRIMARY KEY(empID),
CONSTRAINT fk_employee FOREIGN KEY(deptID) REFERENCES tblDept(deptID)
)
 

The order in which you create tables is important. You must create parent tables before creating child tables. So in this example I would not be able to create the employee table with a foreign key constraint on deptID if tblDept had not yet been created.

[sundaygirl]

thanks so much for that,
I've been racking my brain on how to create them correctly, this database that i am working on has so many different tables.
I will keep trying now.
thankyou
sunday

[sundaygirl]

HI again,
I've been getting along really well creating these tables but have hit another problem.
here is what I am trying to do.

create table case
(
caseno text (4),
judgeno text (4),
barristerno text (4),
constraint pk_caseno primary key
constraint fk_caseno foreign key (booking) references booking (caseno)
constraint fk_judgeno foreign key(judge) references judge (judgeno)
constraint fk_barristerno foreign key (prosecuting barrister) references barristerno (prosecuting barrister));

I have managed to create all the other tables with the foreign keys in, but cant seem to get this one to work.
please tell me what I am doing wrong again, its so frustrating.
sunday

[JKing]

I'm assuming you're trying to create the intermediate table in a many to many relationship. The three fields make up the primary key but they are also there own seperate foreign keys. Make sure to backup what you have before trying this out.

CREATE TABLE case
(
caseNo text(4),
judgeNo text(4),
barristerNo text(4),
PRIMARY KEY (caseNo, judgeNo, barristerNo),
FOREIGN KEY (caseNo) REFERENCES tblBooking(caseNo),
FOREIGN KEY (judgeNo) REFERENCES tblJudge(judgeNo),
FOREIGN KEY (barristerNo) REFERENCES tblBarrister(barristerNo)
)
 

[sundaygirl]

thankyou again,
I have managed to create all my tables now and enforced the intergrity in the relationships table.
I was wondering if there is a way of inserting the data in groups?
At the moment I am inputing it one row at a time.
I am using the insert statement ie
insert into ( field 1, field2, field3)
values '1', '2' '3';

thanks again
sunday

[sundaygirl]

ok I take it that I must imput the data one row at a time.
I would have thought that there would have been an easier way.
sunday

[MMcCarthy]

ok I take it that I must imput the data one row at a time.
I would have thought that there would have been an easier way.
sunday

Have you got the data somewhere else? Excel, text file, etc.