using "sequence", how to drop constraints?

From: matthew taylor <s176226_at_student.uq.edu.au>
Date: Sat, 11 Mar 2000 12:44:21 +1000
Message-ID: <38C9B305.8B654535_at_student.uq.edu.au>

Thought a few might find this interesting ...

--	Sequence Number		Table
--	------------------------------------------
-- 	s_customer_id		s_customer

CREATE SEQUENCE s_customer_id
  MINVALUE 1
  MAXVALUE 9999999
  INCREMENT BY 1
  START WITH 216
  NOCACHE
  NOORDER
  NOCYCLE;
// create table, note constraint CHECK format
// CONSTRAINT constraint_name CHECK (attribute_name IN ('list_item1',
'list_item2', 'etc'))

CREATE TABLE s_customer

(customer_id        NUMBER(7)    CONSTRAINT s_customer_id_nn NOT NULL,
 customer_name      VARCHAR2(50) CONSTRAINT s_customer_name_nn NOT NULL,
 phone              VARCHAR2(15),
 address            VARCHAR2(400),
 city               VARCHAR2(35),
 state              VARCHAR2(30),
 country            VARCHAR2(30),
 zip_code           VARCHAR2(10),
 credit_rating      VARCHAR2(9),
 sales_rep_id       NUMBER(7),
 region_id          NUMBER(7),
 comments           VARCHAR2(255),

 preferred_customer VARCHAR2(1) DEFAULT 'N' NOT NULL,  shipping_method VARCHAR2(1) DEFAULT 'M' NOT NULL,  CONSTRAINT s_customer_pref_cust CHECK (preferred_customer IN ('Y', 'N')),
 CONSTRAINT s_customer_ship_method CHECK (shipping_method IN ('M', 'F', 'U')),
 CONSTRAINT s_customer_id_pk PRIMARY KEY (customer_id),  CONSTRAINT s_customer_credit_rating_ck CHECK (credit_rating IN ('EXCELLENT', 'GOOD', 'POOR'))
);
// insert data, note - || for breaking long command strings into
multiple lines.
INSERT INTO s_customer VALUES

	(201, 'UNISPORTS', '55-2066101', '72 VIA BAHIA',
	 'SAO PAOLO', NULL, 'BRAZIL', NULL, 'EXCELLENT',
	 12, 2, 'Customer usually orders large amounts ' ||
	 'and has a high order total.  This is okay as long ' ||
         'as the credit rating remains excellent.', 'N', 'M');

//

SELECT * FROM s_customer;

INSERT INTO s_customer VALUES

	(s_customer_id.NEXTVAL, 'UNISPORTS', '55-2066101', '72 VIA BAHIA',
	 'SAO PAOLO', NULL, 'BRAZIL', NULL, 'EXCELLENT',
	 12, 2, 'Customer usually orders large amounts ' ||
	 'and has a high order total.  This is okay as long ' ||
         'as the credit rating remains excellent.', 'N', 'M');

SELECT customer_id FROM s_customer;
// note : since the sequence is defined as starting from 216, the list
is 201, 216.

// the following line should work but doesn't (for oracle 7.3), any
ideas, please email
// s176226_at_student.uq.edu.au

//SELECT * FROM s_customer where customer_id = s_customer_id.CURRVAL;

[Quoted] // now cleanup ie. drop sequence and table.
drop SEQUENCE s_customer_id;
DROP TABLE s_customer;
// note - do not need to drop constraints in addition to dropping table.
// but how to drop constraint without dropping table (and data)??
Received on Sat Mar 11 2000 - 03:44:21 CET