1 SQL DATA DEFINITION Managing Tables Data Integrity

1 SQL DATA DEFINITION Managing Tables, Data Integrity, Constraints by Adrienne Watt

Entering commands 2 The first step in creating queries (DML or DDL) in SQL Server is to open a new query window.

Choosing the Database 3 Once you are ready to enter your command (create table), make sure you are in the correct database. Use the dropdown to select the database.

Entering the Command 4 Enter your create table statement. This table will be created in the Adrienne database.

Datatypes 5 Before you can continue with creating tables, and constraints, you need to know about datatypes. Describes type of data allowed System-supplied datatype Integers Bit - Integer data with either a 1 or 0 value. Int - Integer (whole number) data from -2^31 (-2, 147, 483, 648) through 2^31 - 1 (2, 147, 483, 647). Smallint - Integer data from 2^15 (-32, 768) through 2^15 - 1 (32, 767). Tinyint - Integer data from 0 through 255.

Numeric Datatypes 6 Decimal - Fixed precision and scale numeric data from -10^38 -1 through 10^38 -1. Numeric - A synonym for decimal. Timestamp - A database-wide unique number. Uniqueidentifier - A globally unique identifier (GUID). Money Monetary data values from -2^63 (-922, 337, 203, 685, 477. 5808) through 2^63 - 1 (+922, 337, 203, 685, 477. 5807), with accuracy to a ten-thousandth of a monetary unit. smallmoney Monetary data values from -214, 748. 3648 through +214, 748. 3647, with accuracy to a ten-thousandth of a monetary unit.

More Datatypes 7 Approximate Numbers Float - Floating precision number data from -1. 79 E + 308 through 1. 79 E + 308. Real - Floating precision number data from -3. 40 E + 38 through 3. 40 E + 38. Date and Times datetime Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of a second, or 3. 33 milliseconds. smalldatetime Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.

More Datatypes 8 Character Strings Char - Fixed-length non-Unicode character data with a maximum length of 8, 000 characters. Varchar - Variable-length non-Unicode data with a maximum of 8, 000 characters. Text - Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2, 147, 483, 647) characters. Binary - Fixed-length binary data with a maximum length of 8, 000 bytes. Varbinary - Variable-length binary data with a maximum length of 8, 000 bytes. Image - Variable-length binary data with a maximum length of 2^31 1 (2, 147, 483, 647) bytes.

Managing Tables 9 used for storage and manipulation of data contain column, which describe data, rows table names must follow the rules for naming identifiers must be unique

Create and Modify tables 10 Column Name Unique within the table Datatype must be a system datatype or user-defined datatype Size Null Default value appears in new record only

CREATE TABLE Statement 11 CREATE TABLE [database. [owner]. ]table_name ( col_name, column_properties ) table_name column_properties 1. 2. Click on New Query Enter the following statement into the query screen

CREATE TABLE Statement 12 USE HOTEL GO i. e. CREATE TABLE tbl. Hotel (Hotel. No Int NOT NULL, Name Char(50) NOT NULL, Address Char(50) NULL, City Char(25) NULL) GO Press CTRL-E

Data Integrity 13 preserves data consistency in a database All integrity rules should be stored in the database. SQL Server supports declarative referential integrity that enforces data integrity automatically as data is inserted, updated, or deleted from a database. Data integrity falls into four categories: entity integrity, domain integrity, referential integrity, and user-defined integrity.

Identity Property 14 When a new row is added to the table, SQL Server provides a unique, incremental value for that column. Identity columns are often used with the PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to a column with a tinyint, smallint, decimal, or numeric data type. generates sequential numbers does not enforce entity integrity only one column can have the IDENTITY property must be defined as an integer, numeric or decimal datatype cannot update a column with IDENTITY property cannot contain Null values cannot bind defaults and default constraints to the column

Creating an Identity Column 15 used with the CREATE TABLE statement IDENTITY[(seed, increment)] seed – is the initial value of the identity column increment – is the value to add to the last increment column i. e. CREATE TABLE tbl. Hotel (Hotel. No Int Name Char(50) Address Char(50) City Char(25) ) GO IDENTITY (1, 1), NOT NULL,

Constraints 16 Constraints are defined to provide data integrity on a table and individual columns in a table. Constraints limit the possible values a user can enter into a table or a column Constraints are added to a table with Enterprise Manager or typed in query analyzer.

Primary Key constraint 17 A table can have only one Primary key. The columns that participate in a Primary key may not accept nulls. Primary keys may consist of as many as 16 columns. Primary key constraints cannot be disabled. [CONSTRAINT constraint_name] PRIMARY KEY [CLUSTERED | NONCLUSTERED] (col_name [, col_name 2 […, col_name 16]])

Primary Key Example 18 USE HOTEL 97 GO CREATE TABLE tbl. Hotel ( Hotel. No Int IDENTITY PRIMARY KEY, Name Char(50) NOT NULL UNIQUE, Address Char(50) NULL, City Char(25) NULL, ) GO Composite Primary Key CONSTRAINT claim_key PRIMARY KEY (policy_no, date_reported))

UNIQUE Constraint 19 Prevents duplicate values from being entered into a column. Both PK and UNIQUE constraints are used to enforce entity integrity. Multiple UNIQUE constraints can be defined for a table. When a UNIQUE constraint is added to an existing table, the existing data is always validated. Can be placed on columns that accept nulls. Only one row can be NULL. Automatically creates a unique index on the selected column. UNIQUE Constraint Syntax: [CONSTRAINT constraint_name] UNIQUE [CLUSTERED | NONCLUSTERED] (col_name [, col_name 2 […, col_name 16]]) [ON segment_name]

ALTER TABLE Constraint 20 You can use CREATE TABLE and ALTER TABLE statements to add and drop constraints. ALTER TABLE statement used to add or drop constraints does allow columns to be removed when constraint is added, all existing data is verified for violations

UNIQUE constraint example 21 USE HOTEL 97 GO ALTER TABLE tbl. Hotel ADD CONSTRAINT unq. Name UNIQUE (Name)

22 ALTER TABLE IDENTITY Property Use ALTER TABLE statement to add a column with the IDENTITY property. i. e. ALTER TABLE Table. Name ADD Column. Name int IDENTITY(seed, increment)

FOREIGN KEY Constraint 23 defines a column, or combination of columns, whose values match the primary key (PK) of another table. values in a foreign key (FK) are automatically updated when the PK values in the associated table are updated/changed. FK constraints must reference PK or the UNIQUE constraint of another table. number of columns for FK must be same as PK or UNIQUE constraint. if the WITH NOCHECK option is used, the FK constraint will not validate existing data in a table. no index is created on the columns that participate in a FK constraint. FOREIGN KEY Constraint Syntax: [CONSTRAINT constraint_name] [FOREIGN KEY (col_name [, col_name 2 […, col_name 16]])] REFERENCES [owner. ]ref_table [(ref_col [, ref_col 2 […, ref_col 16]])]

FOREIGN KEY Example 24 USE HOTEL 97 GO CREATE TABLE tbl. Room ( Hotel. No Int NOT NULL , Room. No Int NOT NULL, Type Char(50) NULL, Price Money NULL, PRIMARY KEY (Hotel. No, Room. No), FOREIGN KEY (Hotel. No) REFERENCES tbl. Hotel ) GO

CHECK Constraint 25 Restricts values that can be entered into a table. Can contain search conditions similar to a WHERE clause Can reference columns in the same table. The data validation rule for a CHECK constraint must evaluate to a boolean expression. Can be defined for a column that has a rule bound to it. CHECK Constraint Syntax: [CONSTRAINT constraint_name] CHECK [NOT FOR REPLICATION] (expression)

CHECK Constraint 26 USE HOTEL 97 GO CREATE TABLE tbl. Room ( Hotel. No Int NOT NULL, Room. No Int NOT NULL, Type Char(50) NULL, Price. Money NULL, PRIMARY KEY (Hotel. No, Room. No), FOREIGN KEY (Hotel. No) REFERENCES tbl. Hotel CONSTRAINT Valid_Type CHECK (Type IN (‘Single’, ‘Double’, ‘Suite’, ‘Executive’)) ) GO

CHECK Constraint 27 CREATE TABLE SALESREPS (Empl_num Int Not Null CHECK (Empl_num BETWEEN 101 and 199), Name Char (15), Age Int Quota Money Hire_Date. Time, CHECK (Age >= 21), CHECK (Quota >= 0. 0), CONSTRAINT Quota. Cap CHECK ((Hire_Date < “ 01 -01 -2004”) OR (Quota <=300000)) )

DEFAULT Constraint 28 Used to supply a value that is automatically added for a column if the user does not supply one. A column can have only one DEFAULT. Cannot be used on columns with timestamp datatype or identity property. Automatically bound to a column when they are created. DEFAULT Constraint Syntax [CONSTRAINT constraint_name] DEFAULT {constant_expression | niladic-function | NULL} [FOR col_name] USE Hotel ALTER TABLE tbl. Hotel Add CONSTRAINT df_city DEFAULT ‘Vancouver’ FOR City

User-defined Datatypes 29 always based on system-supplied datatype can enforce data integrity Length Allow nulls Default Rule

30 Create a user-defined datatype Choose Types under Programmability in your database. Right click and choose ‘New’ ‘User-defined datatype’ Or Execute the sp_addtype system stored procedure. In a New Query window type: sp_addtype ssn, 'varchar(11)', 'NOT NULL' This will add a new user defined data type called SSN.

Example 31 CREATE TABLE SSNTable ( Employee. ID INT Primary Key, Employee. SSN, CONSTRAINT Check. SSN CHECK (Employee. SSN LIKE ‘ [0 -9][0 -9] - [0 -9][0 -9][0 -9] ‘) )

Dropping a Table 32 DROP TABLE [database. ]owner. ]table_name 1. Make sure you have the correct database selected 2. Enter the following statement into a query Window 3. DROP TABLE tbl. Hotel 4. Press CTRL+E