1 / 40

Creating Tables, Defining Constraints

Creating Tables, Defining Constraints. Rose-Hulman Institute of Technology Curt Clifton. Outline. Data Types Creating and Altering Tables Constraints Primary and Foreign Key Constraints Row and Tuple Checks Generating Column Values Generating Scripts. Data Types.

ibarraa
Download Presentation

Creating Tables, Defining Constraints

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Creating Tables, Defining Constraints Rose-Hulman Institute of Technology Curt Clifton

  2. Outline • Data Types • Creating and Altering Tables • Constraints • Primary and Foreign Key Constraints • Row and Tuple Checks • Generating Column Values • Generating Scripts

  3. Data Types

  4. System-supplied Data Types • Numeric • Integer • Exact numeric • Approximate numeric • Monetary • Date and Time • Character and Unicode Character • Binary • Other Slide based on MS-CreatingTables.ppt

  5. User-defined Data Types • Simple, self-documenting short-hand • Creating: • CREATE TYPE ssnFROM varchar(11) NOT NULL • Dropping: • DROP TYPE ssn • Advanced use: C# objects

  6. Guidelines for Data Types • If Column Length Varies, Use a Variable Data Type • Use tinyint Appropriately • For Numeric Data Types, Commonly Use decimal • Use money for Currency • Do Not Use float or real as Primary Keys Slide based on MS-CreatingTables.ppt

  7. Creating and Altering Tables

  8. Creating Tables • Need: • Table name • Column names and types • Basic Example: • CREATE TABLE Soda( name CHAR(20), manf CHAR(20));

  9. Data Header Fixed Data NB VB Variable Data 4 bytes Null Block Variable Block How SQL Server Organizes Data A Single Data Row Slide based on MS-CreatingTables.ppt

  10. Big @$$ Data Data row Text Pointer Root Structure Intermediate Node Intermediate Node block 1 block 2 block 1 block 2 Slide based on MS-CreatingTables.ppt

  11. Altering Tables • Adding columns: • ALTER TABLE Soda ADD msrp float; • Changing columns: • ALTER TABLE Soda ALTER COLUMN msrp money; • Dropping columns: • ALTER TABLE Soda DROP COLUMN manf;

  12. Dropping Tables • DROP TABLE Soda;

  13. Constraints • A requirement on data elements or the relationship between data elements that the DBMS is required to enforce

  14. Kinds of Constraints • Primary keys (entity integrity) • Foreign keys (referential integrity) • Attribute-based • Restrictions on the value of a single attribute • Row-based • Restrictions on the value of one attribute in row based on value of other attributes • Assertions • Later…

  15. Specifying Primary Key Constraint • Examples: • CREATE TABLE Soda ( name CHAR(20) PRIMARY KEY, manf CHAR(20)); • CREATE TABLE Likes( customer CHAR(30), soda CHAR(20), PRIMARY KEY(customer, soda));

  16. Foreign Key Constraints • Consider foreign keys in Sells relation…

  17. Specifying Foreign Key Constraints • CREATE TABLE Sells( rest CHAR(20) REFERENCES Rest(name), soda CHAR(20) REFERENCES Soda(name), price money ); or • CREATE TABLE Sells( rest CHAR(20), soda CHAR(20), price money, FOREIGN KEY(rest) REFERENCES Rest(name), FOREIGN KEY(soda) REFERENCES Soda(name) );

  18. Foreign Key Restriction • Referenced attributes must be either: • PRIMARY KEY or else • UNIQUE (another element constraint)

  19. Enforcing Foreign-Key Constraints • What changes to the SodaBase data might break referential integrity?

  20. Change to Table with Foreign Key • How should we handle an insert or update to the table with the foreign key that would break referential integrity?

  21. Change to Table with Primary Key • How should we handle an update or delete to the table with the primary key that would break referential integrity?

  22. 3 Solutions to Primary Key Change • Reject! • This is the default • Cascade • Make same change to foreign key • Set null • Set foreign key to null

  23. Example: Default Policy • Suppose ‘Coke’ is referenced by Sells… • We attempt to delete ‘Coke’ from Soda table • Rejected! • We attempt to update ‘Coke’ row, changing ‘Coke’ to ‘Coca-Cola’ • Rejected! • Forces Sells table to be changed first

  24. Example: Cascade Policy • Suppose we delete Coke row from Soda • Then automatically delete all rows for Coke from Sells • Suppose we update the Coke row, changing ‘Coke’ to ‘Coca-Cola’ • Then automatically change all rows in Sells referencing Coke to reference Coca-Cola instead

  25. Example: “Set Null” Policy • Suppose we delete Coke row from Soda • Then automatically change all rows referencing Coke in Sells to have nulls • Suppose we update the Coke row, changing ‘Coke’ to ‘Coca-Cola’ • Then automatically change all rows in Sells referencing Coke to have nulls

  26. Choosing a Policy • Can independently choose policy… • For update • For delete • What policy should we use for… • Deleting soda? Why? • Updating soda name? Why?

  27. Specifying a Policy • Follow foreign-key declaration with: • [ON UPDATE {SET NULL | CASCADE}][ON DELETE {SET NULL | CASCADE}] • Omitted clause means default policy

  28. Example • CREATE TABLE Sells( rest CHAR(20) REFERENCES Rest(name) ON DELETE CASCADE ON UPDATE CASCADE, soda CHAR(20) REFERENCES Soda(name) ON DELETE SET NULL ON UPDATE CASCADE, price money);

  29. Attribute-based Checks • Can constrain single attribute values • Syntax: • CHECK( condition ) • Condition can use: • Name of checked attribute • Subqueries • Checked only upon insertion, update

  30. Example • CREATE TABLE Customer( name CHAR(20) PRIMARY KEY, addr CHAR(50), phone CHAR(8) CHECK (phone LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'));

  31. CREATE TABLE Sells ( rest CHAR(20), soda CHAR(20) REFERENCES Soda(name), price money); CREATE TABLE Sells ( rest CHAR(20), soda CHAR(20) CHECK ( soda IS NULL OR soda IN (SELECT name FROM Soda)), price money); Same or Different?

  32. Row-Based Checks • Can also put CHECK at end of table declaration • Can reference any attribute in table • CHECK for each tuple… • Inserted or • Updated

  33. Example • Only Joe’s can sell Coke for more than $2 • CREATE TABLE Sells ( rest CHAR(20), soda CHAR(20), price money, CHECK( condition )); • What should condition be?

  34. Generating Column Values • Table identity columns • Globally unique identifiers

  35. Table Identity Column • Constraint on single column of table • Column must be integer or decimal data type • Syntax: • IDENTITY [ (seed, increment) ] • Example: • CREATE TABLE Users( name CHAR(20), id int IDENTITY (0, 5) );

  36. Getting Last Identity Value • Use @@identity in scripts • INSERT INTO Users(name)VALUE ('Molly');SELECT 'Last identity used: ' + CONVERT(char, @@identity) AS Answer;

  37. GUIDs • Globally unique identifiers • Generated with newid() function • Used with DEFAULT constraint

  38. Example • CREATE TABLE Household( HouseholdID uniqueidentifier NOT NULL DEFAULT newid(), …);

  39. Generating Scripts • Can generate scripts from objects • Right click database • Tasks  Generate Scripts… • Useful for: • Storing schemas in version control system • Creating test environment • Training

  40. Recommended Practices • Specify Appropriate Data Types and Data Type Sizes (duh!) • Always Specify Column Characteristics in CREATE TABLE • Generate Scripts to Recreate Database Objects Slide based on MS-CreatingTables.ppt

More Related