Create Table (SQL)

We can specify a new base relation by giving it a name, and specifying each of its attributes.

CREATE TABLE DEPARTMENT (
	DNAME        VARCHAR(10) NOT NULL,
	DNUMBER      INTEGER     NOT NULL,
	MGRSSN       CHAR(9),
	MGRSTARTDATE DATE
);

In SQL, attributes are ordered based on the order they are specified. Attributes may have initial constraints defined, such as NOT NULL.

Integrity constraints are specified after the attributes:

CREATE TABLE DEPARTMENT {
	...,
	PRIMARY KEY(DNUMBER),
	UNIQUE(DNAME),
	FOREIGN KEY(MGRSSN) REFERENCE EMPLOYEE(SSN),
 
	-- composite primary keys
	PRIMARY_KEY(DNUMBER, DNAME),
 
	-- composite foreign keys
	FOREIGN KEY(MGR_FNAME, MGR_LNAME) REFERENCES EMPLOYEE (FNAME, LNAME)
}