| Help Creating Table [message #7070] |
Mon, 19 May 2003 21:03  |
Joe
Messages: 138
Registered: November 1999
|
Senior Member |
|
|
I am trying to create a table with a row of mandatory length but not sure the syntax. The question is as follows.
table called CUSTOMERS. This table contains the following
cust_id (mandatory 6 digits starting at 100000),
cust_name (through 25 characters),
cust_phone (mandatory 10 characters),
cust_email (through 50 characters
can anyone show me a sample sql statement for this problem
|
|
|
|
| Re: Help Creating Table [message #7077 is a reply to message #7070] |
Tue, 20 May 2003 07:21  |
Keith
Messages: 88
Registered: March 2000
|
Member |
|
|
Example below - I've taken out the storage clauses for simplicity.
Also, not sure exactly what the form of the constraints you require are, but you should get the idea.
Good luck
keith
krjf@ora92> CREATE TABLE CUST
2 (
3 CUST_ID NUMBER(10) NOT NULL,
4 CUST_NAME VARCHAR2(25),
5 CUST_PHONE VARCHAR2(10) NOT NULL,
6 CUST_EMAIL VARCHAR2(50)
7 )
8 TABLESPACE USERS
9 ;
Table created.
krjf@ora92>
krjf@ora92>
krjf@ora92> CREATE UNIQUE INDEX PK_CUST ON CUST
2 (CUST_ID)
3 LOGGING
4 TABLESPACE USERS
5 ;
Index created.
krjf@ora92>
krjf@ora92>
krjf@ora92> ALTER TABLE CUST ADD (
2 CONSTRAINT CUST_CHK_ID CHECK (length(cust_id) = 6 and cust_id > 99999));
Table altered.
krjf@ora92>
krjf@ora92> ALTER TABLE CUST ADD (
2 CONSTRAINT CUST_CHK_PHONE CHECK (length(cust_phone) = 10));
Table altered.
krjf@ora92>
krjf@ora92> ALTER TABLE CUST ADD (
2 CONSTRAINT PK_CUST PRIMARY KEY (CUST_ID)
3 USING INDEX
4 TABLESPACE USERS
5 );
Table altered.
krjf@ora92>
krjf@ora92>
krjf@ora92> insert into cust values (90000,'Name','1234567890','a.b@x.com');
insert into cust values (90000,'Name','1234567890','a.b@x.com')
*
ERROR at line 1:
ORA-02290: check constraint (KRJF.CUST_CHK_ID) violated
krjf@ora92> insert into cust values (100000,'Name','123','a.b@x.com');
insert into cust values (100000,'Name','123','a.b@x.com')
*
ERROR at line 1:
ORA-02290: check constraint (KRJF.CUST_CHK_PHONE) violated
krjf@ora92> insert into cust values (100000,'Name','1234567890','a.b@x.com');
1 row created.
krjf@ora92>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
