Constraints.sql

-- NOT NULL WITHOUT DEFAULT VALUES
CREATE TABLE NOT_NULL_TEST(
	COL1 INTEGER,
	COL2 INTEGER NOT NULL
);

INSERT INTO NOT_NULL_TEST VALUES (1, NULL);
INSERT INTO NOT_NULL_TEST(COL1) VALUES (1);
INSERT INTO NOT_NULL_TEST VALUES (1,1);
UPDATE NOT_NULL_TEST SET COL2 = NULL;

-- NOT NULL CONSTRAINT WITH DEFAULT CONSTRAINT
CREATE TABLE NOT_NULL_DEFAULT(
	COL1 INTEGER,
	COL2 INTEGER NOT NULL DEFAULT 5
);

INSERT INTO NOT_NULL_DEFAULT (COL1) VALUES (1);

SELECT * FROM NOT_NULL_DEFAULT;

-- UNIQUE CONSTRAINT DEFINITION TYPE-1
CREATE TABLE UNIQUETEST(
	COL1 INTEGER UNIQUE
);

INSERT INTO UNIQUETEST VALUES (1);
INSERT INTO UNIQUETEST VALUES (1);
INSERT INTO UNIQUETEST VALUES (NULL);
INSERT INTO UNIQUETEST VALUES (NULL);

-- UNIQUE CONSTRAINT DEFINITION TYPE-2
CREATE TABLE UNIQUETEST2 (
	COL1 INTEGER,
	COL2 INTEGER,
	UNIQUE (COL1, COL2)
);

-- PRIMARY KEY CONSTRAINT DEFINITION TYPE-1
CREATE TABLE PRIMARYTEST (
	COL INTEGER PRIMARY KEY
);

-- PRIMARY KEY CONSTRAINT DEFINITION TYPE-2
CREATE TABLE PRIMARYTEST2 (
	COL1,
	COL2,
	PRIMARY KEY(COL1, COL2)
);

CREATE TABLE STATENAME (
	CODE CHAR(2) PRIMARY KEY,
	NAME CHAR(30)
);

INSERT INTO STATENAME VALUES ('AL','ALABAMA');

CREATE TABLE CUSTOMER (
	CUSTOMER_ID 	INTEGER,
	NAME		CHAR(30),
	TELEPHONE	CHAR(30),
	STREET		CHAR(30),
	CITY		CHAR(25),
	STATE		CHAR(2) REFERENCES STATENAME,
	ZIPCODE		CHAR(10),
	COUNTRY		CHAR(20)
);

INSERT INTO CUSTOMER (STATE) VALUES ('AL');
INSERT INTO CUSTOMER (STATE) VALUES ('XX');