Home » SQL & PL/SQL » SQL & PL/SQL » Tabel creation Money?
Tabel creation Money? [message #223697] Sat, 10 March 2007 09:02 Go to next message
Chris Empatge
Messages: 4
Registered: March 2007
Junior Member
Hi I am trying to create a database in oracle I want a column with a money value up to $1000, here is my code so far

CREATE TABLE items
(ItemCode Number (4) not null,
Itemdesc Varchar2 (20) not null,
cost money (1000) not null,
Primary Key (ItemCode),
FOREIGN KEY (ItemCode)REFERENCES cHARGE);

I have spent hours puzzeling over this

Thanks for your help

Re: Tabel creation Money? [message #223704 is a reply to message #223697] Sat, 10 March 2007 09:35 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
You mean you are looking in the documentation for a datatype called MONEY and can't find it? There isn't one.
Re: Tabel creation Money? [message #223713 is a reply to message #223704] Sat, 10 March 2007 11:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Besides, why make an extra table if your PK column is an FK? Better add these columns to the charge table.
(your FK reference syntax is wrong too, it requires not only a tablename, but also a columnname.)
Re: Tabel creation Money? [message #223721 is a reply to message #223713] Sat, 10 March 2007 12:50 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Frank wrote on Sat, 10 March 2007 17:38
Besides, why make an extra table if your PK column is an FK? Better add these columns to the charge table.
(your FK reference syntax is wrong too, it requires not only a tablename, but also a columnname.)

Perhaps a Charge may have either no Items, or just one? I agree it does seem a bit odd.

The syntax is OK however:

SQL> create table master (master_id int constraint master_pk primary key, othercol varchar2(30));

Table created.

SQL> create table detail (detail_id int, master_id int,
  2  FOREIGN KEY (master_id) REFERENCES master );

Table created.

SQL> @constr detail

Type     Constraint name                Definition                                                   Status
-------- ------------------------------ ------------------------------------------------------------ --------
FK       SYS_C008271                    Foreign key (MASTER_ID) to MASTER (MASTER_PK)                ENABLED

However I prefer it inline as part of the column definition:

SQL> drop table detail purge;

Table dropped.

SQL> create table detail (detail_id int, master_id references master);

Table created.


and explicitly named:

SQL> drop table detail purge;

Table dropped.

SQL> create table detail (detail_id int, master_id CONSTRAINT det_mas_fk references master);

Table created.

SQL> @constr

Type     Constraint name                Definition                                                   Status
-------- ------------------------------ ------------------------------------------------------------ --------
FK       DET_MAS_FK                     Foreign key (MASTER_ID) to MASTER (MASTER_PK)                ENABLED
Re: Tabel creation Money? [message #223722 is a reply to message #223721] Sat, 10 March 2007 12:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks for the explanation. I never saw that syntax before.

:diving into the manuals:
Re: Tabel creation Money? [message #223783 is a reply to message #223713] Sun, 11 March 2007 07:27 Go to previous messageGo to next message
Chris Empatge
Messages: 4
Registered: March 2007
Junior Member
Frank wrote on Sat, 10 March 2007 17:38
Besides, why make an extra table if your PK column is an FK? Better add these columns to the charge table.
(your FK reference syntax is wrong too, it requires not only a tablename, but also a columnname.)



Thanks for the reply but the assignment brief tells me to put in a primary key.
Re: Tabel creation Money? [message #223789 is a reply to message #223783] Sun, 11 March 2007 08:55 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Including a primary key is one thing, having it the same as a foreign key is something else. It means there can only ever be one Item for each Charge. (btw you have Charge and Items - maybe the naming should be consistent.)
Re: Tabel creation Money? [message #223790 is a reply to message #223697] Sun, 11 March 2007 09:47 Go to previous message
Chris Empatge
Messages: 4
Registered: March 2007
Junior Member
Tank you giuys you really helped I hope that one day i can repay the favour
Previous Topic: Materialized View
Next Topic: file structure
Goto Forum:
  


Current Time: Sat Dec 03 18:19:08 CST 2016

Total time taken to generate the page: 0.05323 seconds