Create Table With Constraints

Let's take a break from XML. : )
To build a foundation for other topics(indexing, insert, update, merge) today we'll learn how to create a table. This is part of chapter 8, lesson 1 in 70-461 training kit. You can read more about the syntax here.

--
--Create customers and orders table on dbo schema of the AdventureWorks2012 sample database.
USE AdventureWorks2012;
GO
CREATE TABLE dbo.Customers (
   custid    BIGINT NOT NULL PRIMARY KEY,
   lastname  VARCHAR(30) NOT NULL,
   firstname VARCHAR(30) NOT NULL 
);
GO
CREATE TABLE dbo.Orders (
   orderid BIGINT NOT NULL PRIMARY KEY,
   orderdate DATE NOT NULL ,
   totalamount DECIMAL(10,4) NOT NULL,
   returned CHAR(1) NOT NULL DEFAULT('N'), --indicate the order was returned by customer
   custid BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.Customers(custid),
   CHECK (totalamount > 0.00), 
   CHECK (returned IN ('Y','N'))
);
GO

Notice that the dbo.Customers table has a primary key constraint. The dbo.Orders table has a primary key, 1 foreign key, 1 default and 2 check constraints. Let's write some queries to insert some records to the 2 tables above and explore about the constraints.

 
--
--This query fails because it doesn't specify firstname, 
-- but dbo.Customers.firstname column doesn't allow NULL mark.
INSERT INTO dbo.Customers(custid, lastname, firstname)
VALUES(1, 'Foster', NULL);

Result: Cannot insert the value NULL into column 'firstname', table 'AdventureWorks2012.dbo.Customers'; column does not allow nulls. INSERT fails. The statement has been terminated.

 
--
--This query fails because it violates the Primary Key constraint. 
--Every custid must be unique in the dbo.Customers table.
INSERT INTO dbo.Customers(custid, lastname, firstname)
VALUES
 (1, 'Foster', 'Jack')
,(1, 'Corby' , 'Larry');

Result: Violation of PRIMARY KEY constraint 'PK__Customer__973AFEFEABC60791'. Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (1). The statement has been terminated.

 
--
--This query fails because totalamount < 0.00 
-- but dbo.Orders has a constraint for this totalamount > 0.00
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', -10.00, 'N', 1);

Result: The INSERT statement conflicted with the CHECK constraint "CK__Orders__totalamo__4F9CCB9E". The conflict occurred in database "AdventureWorks2012", table "dbo.Orders", column 'totalamount'. The statement has been terminated.

 
--
--This query fails because returned value is not in ('Y','N').
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', 10.00, '?', 1);

Result: The INSERT statement conflicted with the CHECK constraint "CK__Orders__returned__5090EFD7". The conflict occurred in database "AdventureWorks2012", table "dbo.Orders", column 'returned'. The statement has been terminated.

--
-- This query fails because the dbo.Orders.custid column is a foreign key
--   that references the dbo.Customers.custid column,
--   but no customer with custid = 101 in the dbo.Customers.
-- Basically, in this case an order must have a customer exists in the dbo.Customers table.
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', 10.00, 'N', 101);

Result: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Orders__custid__4EA8A765". The conflict occurred in database "AdventureWorks2012", table "dbo.Customers", column 'custid'. The statement has been terminated.

--
-- This query fails because the can't insert a string into a column with a decimal data type.
-- Note data type is also considered as a constraint.
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', 'money', 'N', 101);

Result: Error converting data type varchar to numeric.

So, table constraints are rules to keep unwanted data out of a table.



Reference:
MS 70-461 Training Kit
http://technet.microsoft.com/en-us/library/ms174979.aspx