Home » SQL & PL/SQL » SQL & PL/SQL » Curious if this code is correct.
Curious if this code is correct. [message #256290] Fri, 03 August 2007 13:53 Go to next message
phloyd
Messages: 5
Registered: August 2007
Junior Member
EDIT: Restored the original post.

Would this table be created properly assuming the referenced tables were already in existence:

Create table Equipment_tbl
(
stateIDNo varchar2(9) constraint stateIDNo_pk primary key,
serialNo varchar2(20) not null,
description varchar2(20),
modelNo varchar2(12),
buildingID char(2) constraint buildingID_fk foreign key (buildingID) references Building_tbl (buildingID),
roomID varchar2(5) constraint roomID_fk foreign key (roomID) references Room_tbl (roomID),
value integer,
manuName varchar(10) constraint nanuName_fk foreign key (manuName) references Manufacturer_tbl (manuName),
status char(1)
);


Thanks,
phloyd

[Updated on: Fri, 03 August 2007 14:36]

Report message to a moderator

Re: Curious if this code is correct. [message #256294 is a reply to message #256290] Fri, 03 August 2007 14:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Curious to know if you can Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Curious to know if you can make sure that lines of code do not exceed 80 or 100 characters when you format.
Curious to know if you can always post your Oracle version (4 decimals).

Curious to see what hapenned when you tried.

Regards
Michel

Re: Curious if this code is correct. [message #256301 is a reply to message #256290] Fri, 03 August 2007 14:32 Go to previous messageGo to next message
phloyd
Messages: 5
Registered: August 2007
Junior Member
I apologize Michel, I should have known better. This time I used the formatter, and cut down my characters per line. I do have a problem in that I'm not sure of my version as this is for a class wherein I only have access to the software at school. So, I'm writing this without the possibility of seeing an error message which is why I came here. If no one can help me I understand.

Create table Equipment_tbl
(
	stateIDNo 	varchar2(8) constraint stateIDNo_pk primary key,
	serialNo 	varchar2(20) not null,
	description	varchar2(20),
	modelNo	        varchar2(12),
	buildingID	char(2) constraint buildingID_fk  foreignkey (buildingID) 
                        references Building_tbl (buildingID),
	roomID	        varchar2(5) constraint roomID_fk foreign key (roomID) 
                        references Room_tbl (roomID),
	value		integer,
	manuName	varchar(10) constraint nanuName_fk foreign key (manuName) 
                        references Manufacturer_tbl (manuName),
	status		char(1)
);



Phloyd
Re: Curious if this code is correct. [message #256302 is a reply to message #256290] Fri, 03 August 2007 14:32 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Please do not remove an entry just because it is answered or is wrong. The whole idea behind this forum is knowledge. How can any user learn techniques or even mistakes if you wipe out the entry. If you made a typo, then modify it. But "Sorry" should have been put in as a new entry to the thread.
Re: Curious if this code is correct. [message #256305 is a reply to message #256290] Fri, 03 August 2007 14:37 Go to previous messageGo to next message
phloyd
Messages: 5
Registered: August 2007
Junior Member
Man, I can't win. Smile I restored the original post.
Re: Curious if this code is correct. [message #256306 is a reply to message #256301] Fri, 03 August 2007 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems correct but I'm not a SQL syntax analyzer.
If you also post the code for the referenced tables then we can test.

Btw, as you seem to have access to Internet maybe you can download Oracle 10g XE and install it on your PC.

Regards
Michel
Re: Curious if this code is correct. [message #256313 is a reply to message #256306] Fri, 03 August 2007 15:03 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, it wouldn't work. Inline constraint clause can not contain "FOREIGN KEY (column_name)".

This *might* compile:
CREATE TABLE equipment_tbl
(
   stateidno   VARCHAR2(8)    CONSTRAINT stateidno_pk PRIMARY KEY,
   serialno    VARCHAR2(20)   NOT NULL,
   description VARCHAR2(20),
   modelno     VARCHAR2(12),
   buildingid  CHAR(2)        CONSTRAINT buildingid_fk
                              REFERENCES building_tbl (buildingid),
   roomid      VARCHAR2(5)    CONSTRAINT roomid_fk
                              REFERENCES room_tbl (roomid),
   VALUE       INTEGER,
   manuname    VARCHAR(10)    CONSTRAINT nanuname_fk
                              REFERENCES manufacturer_tbl (manuname),
   status      CHAR(1)
);
Re: Curious if this code is correct. [message #256314 is a reply to message #256290] Fri, 03 August 2007 15:07 Go to previous messageGo to next message
phloyd
Messages: 5
Registered: August 2007
Junior Member
EDIT: The second two tables here compile. I changed the first (Room_tbl) to mirror the way you changed my equipment table Littlefoot and got this error:

ORA-00904: : invalid identifier


Here's the other code. I did download Oracle 10g XE and the when I tried to create the first table, I got this error:

ORA-02253: constraint specification not allowed here

So, I pulled out all my FK constraints and got this error:

ORA-00904: : invalid identifier

Also, the version I'm using looks similar to the one found in this link:

http://www.uwm.edu/~derek/course/Tools/CS/orcreate.html




Create table Room_tbl
(
	roomID 	        VARCHAR2(8) CONSTRAINT roomID_pk primary key,
	buildingid      CHAR(2)     CONSTRAINT buildingid_fk
                                    REFERENCES building_tbl (buildingid),

	size		number
);


Create table Manufacturer_tbl
(
	manuName varchar2(10) constraint 
 		 manuName_pk primary key,
	street 	 varchar2(20),
	city	 varchar2(15),
	zip	 integer, 
	phone	 varchar2(12)
);


Create table Building_tbl
(
	buildingID   char(2) constraint 	
 		     buildingID_pk primary key,
	street 	     varchar2(20),
	city	     varchar2(15),
	zip	     integer, 
	buildingName varchar2(30) constraint unique
);

[Updated on: Fri, 03 August 2007 15:21]

Report message to a moderator

Re: Curious if this code is correct. [message #256316 is a reply to message #256314] Fri, 03 August 2007 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have documentation, you have a database to test, you have the code for other tables.
Why did you post a question on syntax?
Do it yourself!

Regards
Michel
Re: Curious if this code is correct. [message #256317 is a reply to message #256316] Fri, 03 August 2007 15:23 Go to previous messageGo to next message
phloyd
Messages: 5
Registered: August 2007
Junior Member
Michel Cadot wrote on Fri, 03 August 2007 15:14
So you have documentation, you have a database to test, you have the code for other tables.
Why did you post a question on syntax?
Do it yourself!

Regards
Michel



The problem is that I don't know what I'm doing, I've been testing for a while now with scant results. Oh well, nevermind.

Phloyd.
Re: Curious if this code is correct. [message #256361 is a reply to message #256317] Sat, 04 August 2007 01:03 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle complains about one of the column names in the 'room_tbl' table: SIZE - rename it (it is reserved keyword and can not be used as column name).

Furthermore, you forgot to name the UNIQUE constraint.

Moreover, CREATE TABLE statement may not have blank lines - there is one in the 'room_tbl' create table statement.

Finally, you'll have to rename foreign key constraint in the 'equipment_tbl' create table statement as you've already named one constraint with the same name (the one in 'room_tbl' table).

All-in-one:
c_size         NUMBER
buildingName   VARCHAR2(30)   CONSTRAINT uk_build_name UNIQUE
buildingid     CHAR(2)        CONSTRAINT buildingid_fk_1
Previous Topic: list partition table
Next Topic: drawing w/o replacement function
Goto Forum:
  


Current Time: Tue Dec 06 10:46:24 CST 2016

Total time taken to generate the page: 0.07455 seconds