Home » SQL & PL/SQL » SQL & PL/SQL » Creating a table with a constraint.
Creating a table with a constraint. [message #198382] Mon, 16 October 2006 22:40 Go to next message
highvoltage
Messages: 12
Registered: October 2006
Location: Singapore
Junior Member
Guys do advice how to i insert a constraint of words into the table example below.I am new to this stuff man.

Create table Orders
(
orderID Number(8) Primary Key,
orderDate Date Not Null,
methPmt Varchar2(10),
custID Number(5),
orderSource Number(2),
Foreign Key(custID) Reference Customer(custID),
Foreign Key(ordersource) Reference OrderSource(ordersource)
);

The catch is I am required to enter a constraint of the methPmt will only take values of ‘CASH’, ‘CREDIT’ or ‘CHEQUE’ only.

How am I suppose to enter this constraint vaule into the creation of this table?Am I suppose to use the "check"

[Updated on: Mon, 16 October 2006 22:41]

Report message to a moderator

Re: Creating a table with a constraint. [message #198394 is a reply to message #198382] Tue, 17 October 2006 00:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You are absolutely right. If it is a fixed set you can use a check constraint:
CONSTRAINT methpmt_range CHECK (methPmt IN ('CASH','CREDIT','CHEQUE'))
But why didn't you test it yourself? I've quickly ran this example to verify my answer:
Create table mhe_foo
(
orderID Number(8) Primary Key,
orderDate Date Not Null,
methPmt Varchar2(10),
custID Number(5),
orderSource Number(2),
CONSTRAINT methpmt_range CHECK (methPmt IN ('CASH','CREDIT','CHEQUE'))
)
/

INSERT INTO mhe_foo VALUES (1,sysdate,'BLAH', 1, 1)
/

DROP TABLE mhe_foo
/
I bet you could do the same Wink

MHE
Re: Creating a table with a constraint. [message #198420 is a reply to message #198394] Tue, 17 October 2006 02:30 Go to previous message
highvoltage
Messages: 12
Registered: October 2006
Location: Singapore
Junior Member
Thank you man thats what I am looking for guess i need more practice.
Previous Topic: loop problem
Next Topic: Partial Primay Key
Goto Forum:
  


Current Time: Sat Dec 10 05:08:19 CST 2016

Total time taken to generate the page: 0.08420 seconds