473,549 Members | 2,329 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

CHECK Constraint

Hi ORAPERTS (ORAcle xPERTS)

How can I define a CHECK CONSTRAINT on a table for a field like zip
code....?

Here is what I am doing:
create table test
(
state varchar2(5) check (state
in('AL','AK','A Z','AR','CA','C O','CT','DE'))

)

but it seems that it freaks out....wonder why...? I tried using DOUBLE
QUOTES also but in vain....what is a correct way?

THANKS!
Jul 19 '05 #1
3 11070
"Hari Om" <ha*****@hotmai l.com> wrote in
news:d1******** *************** ***@posting.goo gle.com...
Hi ORAPERTS (ORAcle xPERTS)

How can I define a CHECK CONSTRAINT on a table for a field like zip
code....?

Here is what I am doing:
create table test
(
state varchar2(5) check (state
in('AL','AK','A Z','AR','CA','C O','CT','DE'))

)

but it seems that it freaks out....wonder why...? I tried using DOUBLE QUOTES also but in vain....what is a correct way?

THANKS!


Hi Hari Om,

your example works for me. What do you mean
with 'seems to freak out'? By the way: I prefer
the 'alter table... add constraint...' statement.
In this I can easily define descriptive names
for them.

hth,
Guido

Jul 19 '05 #2
ha*****@hotmail .com (Hari Om) wrote in message news:<d1******* *************** ****@posting.go ogle.com>...
Hi ORAPERTS (ORAcle xPERTS)

How can I define a CHECK CONSTRAINT on a table for a field like zip
code....?

Here is what I am doing:
create table test
(
state varchar2(5) check (state
in('AL','AK','A Z','AR','CA','C O','CT','DE'))

)

but it seems that it freaks out....wonder why...? I tried using DOUBLE
QUOTES also but in vain....what is a correct way?

THANKS!


Just watch your syntax :

This will work

create table test( state varchar2(5) constraint test_chk CHECK(STATE
IN('AL','AK','A Z','AR','CA','C O','CT','DE'))) ;
Table created.

You can test it by inserting an invalid value:

SQL> INSERT INTO TEST VALUES('YY');
INSERT INTO TEST VALUES('YY')
*
ERROR at line 1:
ORA-02290: check constraint (STEVE.TEST_CHK ) violated
I hope this helps,

Steve
Jul 19 '05 #3
On 5 Sep 2003 14:19:59 -0700, ha*****@hotmail .com (Hari Om) wrote:
How can I define a CHECK CONSTRAINT on a table for a field like zip
code....?

Here is what I am doing:
create table test
(
state varchar2(5) check (state
in('AL','AK',' AZ','AR','CA',' CO','CT','DE'))

)


FWIW, those aren't ZIP codes. Consider putting all your postal codes
into a table and set a foreign key reference to it.

--
Mike Sherrill
Information Management Systems
Jul 19 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2813
by: Doug Baroter | last post by:
Hi, DDL: -- create table #task (taskID int identity(1,1) primary key, taskName varchar(25) unique, taskCompleteDate dateTime, taskComplete bit default(0)); /* Business Rules: a) if taskCompleteDate is NULL (like default) then, taskComplete may not be set to 1 (attempt to update it to 1 would fail);
2
7169
by: Doug Baroter | last post by:
Hi, DDLs and DMLs: create table #job (jobID int identity(1,1) primary key, jobName varchar(25) unique not null, jobEndDate dateTime, jobComplete bit default(0), check (( is null and = 0) OR ( is not null and = 1))); Q1 with check constraint: sample dagta
3
9049
by: RAD | last post by:
I am working with an evaluation copy of SQL Server 2000 for the first time; my DB experience lies with MS Access. I have a simple table in SQL Server (tblCompany) that has a field called "Ticker." When new company stock tickers (i.e., MSFT for Microsoft) are entered into the field, I'd like them in all caps--whether the user types msft,...
0
1822
by: Fabre Lambeau | last post by:
I've got a problem when adding a CONSTRAINT CHECK on a table by calling a function. It just seems not to work... Here is the table (simplified to only the relevant fields for this case): CREATE TABLE public.tb_contacts ( contact_id serial NOT NULL, actor_id varchar(50) NOT NULL, contacttype_id varchar(6) NOT NULL,
9
3406
by: Edmund Dengler | last post by:
Greetings! Just trying some tests out, and wanted to know about some optimizations. If I do a CHECK constraint on a table, is this used to optimize a SELECT or does Postgresql rely mostly on normal index search? For example, I want to create some tables to manage different data in a kind of <object, relationship, object2> manner, but...
3
6306
by: ferg | last post by:
I have a Customer table. The table has two different CHECK constraints. Then there is the Customer details dialog, which provides the user with an UI for changing users. I have some UPDATE sql, which is called once the user clicks the OK button on this dialog. try { int rows = cmd.ExecuteNonQuery(); } catch(SqlException se)
1
5910
by: huyuhui | last post by:
The following is a question of LOAD utility. Question: How does the DB2 enforce table check constraints for data added to table with the LOAD utility? A. With the BUILD phase of LOAD B. With the SET INTEGRITY statement C. With the DELETE phase of the LOAD D. With the UPDATE CONSTRAINTS statement Answer is A
1
6526
by: Spectre1337 | last post by:
Hello, it seems like the check constraint validation of MS SQL Server Management Studio express is horribly, horribly broken. Either that or I'm using it wrong. I hope it's the latter. I'm trying to add a check constraint to an empty table. The problem is that just about no form of expression syntax seems to get accepted by MSSQLSMS. On my a...
3
6180
by: Helen Wheels | last post by:
Can we use parentheses in a check constraint in MS-SQL-server DDL? e.g. I'm having a problem with the following statement: ALTER TABLE . ADD CONSTRAINT CHECK (( IS NULL AND IS NULL) OR ( IS NOT NULL AND IS NOT NULL AND IS NOT NULL));
2
3962
by: joerg.sailer | last post by:
Hello, is it possible to define a CHECK CONSTRAINT on a table, whereas the CHECK-OPTION will be a SELECT. With this CHECK CONSTRAINT i try to ensure, that there will allways a state only set once within several rows. If possible, does someone can provide an example ? I did not find anything in the docs. Thanks Joerg
0
7546
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7471
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7985
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6071
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5387
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3517
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3496
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1082
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
784
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.