KEY and UNIQUE. – Example: some DBMS might automatically create an index (data structure to speed search) in response to PRIMARY KEY, but not UNIQUE. • MySQL creates a B+-tree index for primary key – E.g., show index from Sells; – Or: show index in Sells;
137 Required Distinctions • However, standard SQL requires these distinctions: 1. There can be only one PRIMARY KEY for a relation, but several UNIQUE attributes. 2. No attribute of a PRIMARY KEY can ever be NULL in any tuple. But attributes declared UNIQUE may have NULL's, and there may be several tuples with NULL.
138 Other Declarations for Attributes • Two other declarations we can make for an attribute are: 1. NOT NULL means that the value for this attribute may never be NULL. 2. DEFAULT <value> says that if there is no specific value known for this attribute's component in some tuple, use the stated <value>.
139 Example: Default Values CREATE TABLE Drinkers ( name CHAR(30) PRIMARY KEY, addr CHAR(50) DEFAULT '123 Sesame St.', phone CHAR(16) );
140 Effect of Defaults • Suppose we insert the fact that Sally is a drinker, but we know neither her address nor her phone. • An INSERT with a partial list of attributes makes the insertion possible: INSERT INTO Drinkers(name) VALUES('Sally');
141 Effect of Defaults • But what tuple appears in Drinkers? name addr phone 'Sally' '123 Sesame St' NULL • If we had declared phone NOT NULL, this insertion would have been rejected.
142 Adding Attributes • We may change a relation schema by adding a new attribute (“column”) by: ALTER TABLE <name> ADD <attribute declaration>; • Example: ALTER TABLE Bars ADD phone CHAR(16) DEFAULT 'unlisted';
143 Deleting Attributes • Remove an attribute from a relation schema by: ALTER TABLE <name> DROP <attribute>; • Example: we don't really need the license attribute for bars: ALTER TABLE Bars DROP license;
Creating/dropping an index • create index sells_price_idx on Sells(price); – To drop: drop index price_idx on Sells; 144
Query execution plan • explain select * from Sells where price > 3; • Type range: only rows in a given range are retrieved • Possible keys: possible indexes to choose • Key: the index actually chosen • Using index: index used to find qualified rows in the table 145
Resources • EXPLAIN command output format – - output.html 146
- Fall '14
- manf, From Sells