SQL-DDL - ddl PDF

Preview

Summary

ddl...

Description

SQL Server CREATE TABLE The following statement creates a new table named visits to track the customer in-store visits: CREATE TABLE sales.visits ( visit_id INT PRIMARY KEY IDENTITY (1, 1), first_name VARCHAR (50) NOT NULL, last_name VARCHAR (50) NOT NULL, visited_at DATETIME, phone VARCHAR(20), store_id INT NOT NULL, FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) );

The visits table contains six columns. 



The visit_id column is the primary key column of the table. The IDENTITY(1,1) instructs SQL Server to automatically generate integer numbers for the column starting from one and increasing by one for each new row. At the end of the table definition is a FOREIGN KEY constraint. This foreign key ensures that the values in the store_id column of the visits table must be available in the store_id column in the stores table.

SQL Server Identity To create an identity column for a table, you use the IDENTITY property as follows: IDENTITY[(seed,increment)]

In this syntax:  

seed is the value of the first row loaded into the table. increment is the incremental value added to the identity value of the previous row.

SQL Server IDENTITY example Let’s create a new schema named hr for practicing: CREATE SCHEMA hr;

1

The following statement creates a new table using the IDENTITY property for the personal identification number column: CREATE TABLE hr.person ( person_id INT IDENTITY(1,1) PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, gender CHAR(1) NOT NULL ); First, insert a new row into the person table: INSERT INTO hr.person(first_name, last_name, gender) OUTPUT inserted.person_id VALUES('John','Doe', 'M'); Second, insert another row into the person table: INSERT INTO hr.person(first_name, last_name, gender) OUTPUT inserted.person_id VALUES('Jane','Doe','F');

SQL Server DROP TABLE Sometimes, you want to remove a table that is no longer in use. To do this, you use the following DROP TABLE statement: DROP TABLE [IF EXISTS] [database_name.][schema_name.]table_name; When SQL Server removes a table, it also deletes all data, triggers, constraints, permissions of that table. Moreover, SQL Server does not explicitly drop the views and stored procedures that reference the dropped table. To explicitly drop these objects, you must use the DROP VIEW and DROP PROCEDURE statement.

A) Drop a table that does not exist The following statement removes a table named revenues in the sales schema: DROP TABLE IF EXISTS sales.revenues; In this example, the revenues table does not exist. Because it uses the IF EXISTS clause, the statement executes successfully with no table deleted.

2

B) Drop a single table example The following statement creates a new table named delivery in the sales schema: CREATE TABLE sales.delivery ( delivery_id INT PRIMARY KEY, delivery_note VARCHAR (255) NOT NULL, delivery_date DATE NOT NULL );

To remove the delivery table, you use the following statement: DROP TABLE sales.delivery;

C) Drop a table with foreign key constraint The following statement creates two new tables named supplier_groups and suppliers in the procurement schema: CREATE SCHEMA procurment; GO CREATE TABLE procurment.supplier_groups ( group_id INT IDENTITY PRIMARY KEY, group_name VARCHAR (50) NOT NULL ); CREATE TABLE procurment.suppliers ( supplier_id INT IDENTITY PRIMARY KEY, supplier_name VARCHAR (50) NOT NULL, group_id INT NOT NULL, FOREIGN KEY (group_id) REFERENCES procurement.supplier_groups (group_id) Foreign key group_id of procurement.supplier_groups.

procurement.suppliers

references

the

group_id

of

Let’s try to drop the supplier_groups table: DROP TABLE procurement.supplier_groups;

SQL Server issued the following error: Could not drop object 'procurement.supplier_groups' because it is referenced by a FOREIGN KEY constraint.

3

SQL Server does not allow you to delete a table that is referenced by a foreign constraint. To delete this table, you must drop the referencing foreign key constraint or referencing table first. In this case, you have to drop delete the foreign key constraint in the suppliers table or the suppliers table first before removing the supplier_groups table. DROP TABLE procurement.supplier_groups; DROP TABLE procurement.suppliers;

If you use a single DROP TABLE statement to remove both tables, the referencing table must be listed first as shown in the query below: DROP TABLE procurement.suppliers, procurement.supplier_groups;

SQL Server TRUNCATE TABLE You will learn how to use the SQL Server TRUNCATE TABLE statement to remove all rows from a table faster and more efficiently. The following example creates a new table named customer_groups and inserts some rows into the table: CREATE TABLE sales.customer_groups ( group_id INT PRIMARY KEY IDENTITY, group_name VARCHAR (50) NOT NULL ); INSERT INTO sales.customer_groups (group_name) VALUES ('Intercompany'), ('Third Party'), ('One time');

To delete all rows from it using the TRUNCATE TABLE statement: TRUNCATE TABLE sales.customer_groups; Or, DELETE FROM sales.customer_groups;

The TRUNCATE TABLE is similar to the DELETE statement without a WHERE clause. However, the TRUNCATE statement executes faster and uses a fewer system and transaction log resources. 4

TRUNCATE TABLE vs. DELETE The TRUNCATE TABLE has the following advantages over the DELETE statement:

Use less transaction log The DELETE statement removes rows one at a time and inserts an entry in the transaction log for each removed row. On the other hand, the TRUNCATE TABLE statement deletes the data by deallocating the data pages used to store the table data and inserts only the page deallocations in the transaction logs.

SQL Server Rename Table In this tutorial, you will learn how to rename a table using Transact SQL and SQL Server Management Studio. What is a Stored Procedure? A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

SQL Server does not have any statement that directly renames a table. However, it does provide you with a stored procedure named sp_rename that allows you to change the name of a table. The following shows the syntax of using the sp_rename stored procedure for changing the name of a table: EXEC sp_rename 'old_table_name', 'new_table_name'

Let’s see the following example. First, create a new table named sales.contr for storing sales contract’s data: CREATE TABLE sales.contr ( contract_no INT IDENTITY PRIMARY KEY, start_date DATE NOT NULL, 5

expired_date DATE, customer_id INT, amount DECIMAL (10, 2) );

Second, use the sp_rename stored procedure to rename the sales.contr table to sales.contract : EXEC sp_rename 'sales.contr', 'sales.contracts';

SQL Server rename table using SSMS Another way to rename a table is to use the function provided by SQL Server Management Studio. The following example illustrates how to rename the product_history table to product_archive . First, right-click on the table name and choose Rename menu item:

6

Second, type the new name of the table e.g., product_archive and press Enter:

SQL Server ALTER TABLE ADD Column 7

In this tutorial, you will learn how to use SQL Server ALTER TABLE ADD statement to add one or more columns to a table. The following statement creates a new table named sales.quotations : CREATE TABLE sales.quotations ( quotation_no INT IDENTITY PRIMARY KEY, valid_from DATE NOT NULL, valid_to DATE NOT NULL );

To add a new column named description to the sales.quotations table, you use the following statement: ALTER TABLE sales.quotations ADD description VARCHAR (255) NOT NULL;

The following statement adds two new columns named amount and customer_name to the sales.quotations table: ALTER TABLE sales.quotations ADD amount DECIMAL (10, 2) NOT NULL, customer_name VARCHAR (50) NOT NULL;

SQL Server ALTER TABLE ALTER COLUMN SQL Server allows you to perform the following changes to an existing column of a table:   

Modify the data type Change the size Add a NOT NULL constraint

Modify column’s data type: The new data type must be compatible with the old one, otherwise, you will get a conversion error in case the column has data and it fails to convert. First, create a new table with one column whose data type is INT : CREATE TABLE t1 (c INT); Second, insert some rows into the table: INSERT INTO t1 VALUES 8

(1), (2), (3);

Next, modify the data type of the column from INT to VARCHAR : ALTER TABLE t1 ALTER COLUMN c VARCHAR (2);

Third, insert a new row with a character string data: INSERT INTO t1 VALUES ('@');

Fourth, modify the data type of the column from VARCHAR back to INT : ALTER TABLE t1 ALTER COLUMN c INT;

SQL Server issued the following error: Conversion failed when converting the varchar value '@' to data type int.

Change the size of a column: The following statement creates a new table with one column whose data type is VARCHAR(10) : CREATE TABLE t2 (c VARCHAR(10));

Let’s insert some sample data into the t2 table: INSERT INTO t2 9

VALUES ('SQL Server'), ('Modify'), ('Column')

You can increase the size of the column as follows: ALTER TABLE t2 ALTER COLUMN c VARCHAR (50);

However, when you decrease the size of the column, SQL Server checks the existing data to see if it can convert data based on the new size. If the conversion fails, SQL Server terminates the statement and issues an error message. For example, if you decrease the size of column c to 5 characters: ALTER TABLE t2 ALTER COLUMN c VARCHAR (5);

SQL Server issued the following error: String or binary data would be truncated.

Add a NOT NULL constraint to a nullable column

The following statement creates a new table with a nullable column: CREATE TABLE t3 (c VARCHAR(50)); The following statement inserts some rows into the table: INSERT INTO t3 VALUES ('Nullable column'), (NULL);

If you want to add the NOT NULL constraint to the column c , you must update NULL to nonnull first for example: UPDATE t3 SET c = '' 10

WHERE c IS NULL; And then add the NOT NULL constraint: ALTER TABLE t3 ALTER COLUMN c VARCHAR (20) NOT NULL;

SQL Server ALTER TABLE DROP COLUMN This is used to remove one or more columns from existing table. Sometimes, you need to remove one or more unused or obsolete columns from a table. To do this, you use the ALTER TABLE DROP COLUMN statement as follows: ALTER TABLE table_name DROP column_name;

In this syntax: 

First, specify the name of the table from which you want to delete the column.



Second, specify the name of the column that you want to delete.

If the column that you want to delete has a CHECK constraint, you must delete the constraint first before removing the column. Also, SQL Server does not allow you to delete a column that has a PRIMARY KEY or a FOREIGN KEY constraint.

11

SQL Server ALTER TABLE DROP COLUMN examples Let’s create a new table named sales.price_lists for the demonstration. CREATE TABLE sales.price_lists( product_id int, valid_from DATE, price DEC(10,2) NOT NULL CONSTRAINT ck_positive_price CHECK(price >= 0), discount DEC(10,2) NOT NULL, surcharge DEC(10,2) NOT NULL, note VARCHAR(255), PRIMARY KEY(product_id, valid_from) );

The following statement drops the note column from the price_lists table: ALTER TABLE sales.price_lists DROP COLUMN note;

The price column has a CHECK constraint, therefore, you cannot delete it. If you try to execute the following statement, you will get an error: ALTER TABLE sales.price_lists DROP COLUMN price;

Here is the error message: The object 'ck_positive_price' is dependent on column 'price'.

To drop the price column: First, delete its CHECK constraint: ALTER TABLE sales.price_lists DROP CONSTRAINT ck_positive_price;

Then, delete the price column: ALTER TABLE sales.price_lists DROP COLUMN price;

12

SQL Server PRIMARY KEY If the primary key consists of only one column, you can define use PRIMARY KEY constraint as a column constraint: CREATE TABLE table_name ( pk_column data_type PRIMARY KEY, ... ); In case the primary key has two or more columns, you must use the PRIMARY KEY constraint as a table constraint: CREATE TABLE table_name ( pk_column_1 data_type, pk_column_2 data type, ... PRIMARY KEY (pk_column_1, pk_column_2) ); SQL Server automatically sets the NOT NULL constraint for all the primary key columns if the NOT NULL constraint is not specified for these columns.

SQL Server PRIMARY KEY constraint examples The following example creates a table with a primary key that consists of one column: CREATE TABLE sales.activities ( activity_id INT PRIMARY KEY IDENTITY, activity_name VARCHAR (255) NOT NULL, activity_date DATE NOT NULL );

In this sales.activities table, the activity_id column is the primary key column. It means the activity_id column contains unique values. The IDENTITY property is used for the activity_id column to automatically generate unique integer values.

13

The following statement creates a new table named sales.participants whose primary key consists of two columns: CREATE TABLE sales.participants( activity_id int, customer_id int, PRIMARY KEY(activity_id, customer_id) );

In this example, the values in either activity_id or customer_id column can be duplicate, but each combination of values from both columns must be unique. Typically, a table always has a primary key defined at the time of creation. However, sometimes, an existing table may not have a primary key defined. In this case, you can add a primary key to the table by using the ALTER TABLE statement. Consider the following example:

The following statement creates a table without a primary key: CREATE TABLE sales.events( event_id INT NOT NULL, event_name VARCHAR(255), start_date DATE NOT NULL, duration DEC(5,2) );

To make the event_id column as the primary key, you use the following ALTER TABLE statement: ALTER TABLE sales.events ADD PRIMARY KEY(event_id);

Note that if the sales.events table already has data, before promoting the event_id column as the primary key, you must ensure that the values in the event_id are unique.

14

SQL Server FOREIGN KEY In this tutorial, you will learn how to use the SQL Server foreign key constraint to enforce a link between the data in two tables. Consider the following vendor_groups and vendors tables: CREATE TABLE procurement.vendor_groups ( group_id INT IDENTITY PRIMARY KEY, group_name VARCHAR (100) NOT NULL ); CREATE TABLE procurement.vendors ( vendor_id INT IDENTITY PRIMARY KEY, vendor_name VARCHAR(100) NOT NULL, group_id INT NOT NULL, );

Each vendor belongs to a vendor group and each vendor group may have zero or more vendors. The relationship between the vendor_groups and vendors tables is one-to-many. For each row in the vendors table, you can always find a corresponding row in the vendor_groups table. To enforce the link between data in the vendor_groups and vendors tables, you need to establish a foreign key in the vendors table. A foreign key is a column or a group of columns in one table that uniquely identifies a row of another table (or the same table in case of self-reference).To create a foreign key, you use the FOREIGN KEY constraint. The following statements drop the vendors table and recreate it with a FOREIGN KEY constraint: DROP TABLE vendors; CREATE TABLE procurement.vendors ( vendor_id INT IDENTITY PRIMARY KEY, vendor_name VARCHAR(100) NOT NULL, group_id INT NOT NULL, CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES procurement.vendor_groups(group_id) );

15

The vendor_groups table now is called the parent table that is the table to which the foreign key constraint references. The vendors table is called the child table that is the table to which the foreign key constraint is applied.

SQL Server FOREIGN KEY constraint example First, insert some rows into the vendor_groups table: INSERT INTO procurement.vendor_groups(group_name) VALUES('Third-Party Vendors'), ('Interco Vendors'), ('One-time Vendors');

Second, insert a new vendor with a vendor group into the vendors table: INSERT INTO procurement.vendors(vendor_name, group_id) VALUES('ABC Corp',1);

The statement worked as expected. Third, try to insert a new vendor whose vendor group does not exist in the vendor_groups table: INSERT INTO procurement.vendors(vendor_name, group_id) VALUES('XYZ Corp',4); SQL Server issued the following error: The INSERT statement conflicted with the FOREIGN KEY constraint "fk_group". The conflict occurred in database "BikeStores", table "procurement.vendor_groups", column 'group_id'. In this example, because of the FOREIGN KEY constraint, SQL Server rejected the insert and issued an error.

16

More Referential actions CREATE TABLE child_table ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ...

CONSTRAINT fk_name FOREIGN KEY (child_col1, child_col2, ... child_col_n) REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n) [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

SQL Server CHECK Constraint The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

SQL CHECK on CREATE TABLE The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you can not have any person below 18 years: 17

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) ); To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes') );

SQL CHECK on ALTER TABLE To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL: ALTER TABLE Persons ADD CHECK (Age>=18); To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax: ALTER TABLE Persons ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

DROP a CHECK Constraint To drop a CHECK constraint, use the following SQL:

18

ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;

SQL Server UNIQUE Constraint The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE The following SQL creates a UNIQUE constrai...