(Oracle DB) - 6.3 CHECK & DEFAULT

/* CHECK */
	/*
	#	Inspect the data which will be inserted into the column
		whether or not the data satisfy the specified condition.
	*/

	/* Creation of CHECK */
CREATE TABLE ex2_9
(
	num1 NUMBER
	CONSTRAINT check1 CHECK
		(num1 BETWEEN 1 AND 9),
	gender VARCHAR2(10)
	CONSTRAINT check2 CHECK
		(gender IN ('MALE', 'FEMALE'))
);

	/* Insepection the Constraint Type of CHECK */
SELECT
	constraint_name,
	constraint_type,
	table_name,
	search_condition
	FROM user_constraints
WHERE table_name = 'EX2_9'
;

	/* Inspections the Work of CHECK */
INSERT INTO ex2_9
	VALUES
	(10, 'MAN')	-- ERROR : 'MALE' or 'FEMALE' can be inserted into 'gender' column
;

INSERT INTO ex2_9
	VALUES
	(5, 'FEMALE')	-- SUCCESS 
;


/* DEFAULT : Not constraint, but a property of column
	#	Used for specifying default value of a column

	#	Example
		table 'CREATE_DATE' stores date and time data.
		inserting the data manually is not a good way.
		When your create the table with the column, you can specify
		a default value for the 'CREATE_DATE' column.
		Then, the date and time data can be inserted automatically.
*/
CREATE TABLE ex2_10
(
	col1 VARCHAR2(10) NOT NULL,
	col2 VARCHAR2(10) NULL,
	create_date DATE DEFAULT SYSDATE	-- DEFAULT is specified
);

INSERT INTO ex2_10
	(col1, col2)	-- 'create_date' column doesn't get any data
	VALUES
	('AA', 'BB')
;

SELECT * FROM ex2_10;	-- Default value works as a table property