How to add a primary key and a sequence in Oracle

First let’s create the table column that is going to serve as our primary key:

ALTER TABLE OUR_TABLE
ADD (
PKEY	NUMBER
)
;

Next, let’s create the sequence:

CREATE SEQUENCE CC_OUR_TABLE_PKEY_SEQ INCREMENT BY 1
MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCACHE
;

then adding this sequence to the column to serve as a primary key:

UPDATE OUR_TABLE
SET PKEY = CC_OUR_TABLE_PKEY_SEQ.NEXTVAL
;

and finally adding the primary key constraint to the column:

ALTER TABLE OUR_TABLE
ADD CONSTRAINT OUR_TABLE_PK PRIMARY KEY (PKEY)
;

And here’s the rollback process:

--UNDO PROCESS
--ALTER TABLE OUR_TABLE DROP COLUMN PKEY;
--DROP SEQUENCE CC_OUR_TABLE_PKEY_SEQ;
--COMMIT;

Note: I like to use SQLDeveloper as an Oracle DB IDE that helps me visualise all the Oracle DB constructs.

Advertisements

One way of validating XML against XSD in Java

	static boolean validateXMLAgainstXSD(String xml, String xsd) {
	    try {
	        SchemaFactory schemaFactory = SchemaFactory.newInstance(XMLConstants.W3C_XML_SCHEMA_NS_URI);
	        Schema schema = schemaFactory.newSchema(new StreamSource(xsd));
	        Validator validator = schema.newValidator();
	        validator.validate(new StreamSource(xml));
	        return true;
	    }
	    catch(Exception ex) {
	        return false;
	    }
	}