Friday, March 02, 2007

Create Database Table in Oracle or Sybase

Here's the syntax to create a database table with datatypes defined. It's a fairly straightforward task.

ORACLE
SQL> CREATE TABLE CUSTOMER (
CUST_ID NUMBER(6) PRIMARY KEY,
CUST_TITLE VARCHAR2(10) NOT NULL,
CUST_HOME_AREA_CODE NUMBER(3) NOT NULL,
CUST_HPHONE VARCHAR2(8) NOT NULL,
CUST_WORK_AREA_CODE NUMBER(3) NOT NULL,
CUST_WPHONE VARCHAR2(8) NOT NULL,
CUST_STREET VARCHAR2(30) NOT NULL,
DATE_ACCT_OPENED DATE NOT NULL,
CUST_SSN_ID VARCHAR2(11) NOT NULL,
CUST_ZIP_ID NUMBER(5) NOT NULL,
FOREIGN KEY (CUST_SSN_ID) REFERENCES CUSTOMER ON DELETE CASCADE,
FOREIGN KEY (CUST_ZIP_ID) REFERENCES ZIPCODE);

SYBASE
1> CREATE TABLE CUSTOMER (
CUST_ID NUMERIC (6,0) PRIMARY KEY,
CUST_TITLE VARCHAR (10) NOT NULL,
CUST_HOME_AREA_CODE NUMERIC (3,0) NOT NULL,
CUST_HPHONE VARCHAR (8) NOT NULL,
CUST_WORK_AREA_CODE NUMERIC (3,0) NOT NULL,
CUST_WPHONE VARCHAR (8) NOT NULL,
CUST_STREET VARCHAR (30) NOT NULL,
DATE_ACCT_OPENED DATE NOT NULL,
CUST_SSN_ID VARCHAR (11) NOT NULL,
CUST_ZIP_ID NUMERIC (5,0) NOT NULL,
FOREIGN KEY (CUST_SSN_ID) REFERENCES CUSTOMER ON DELETE CASCADE,
FOREIGN KEY (CUST_ZIP_ID) REFERENCES ZIPCODE)
2> go

The ";" is the execute command for Oracle and "go" is the execute command for Sybase.

No comments: