I would like to know how to apply Constraints after creating a table. I was just practicing and creating a database HomeInventory by reading the free e-book from Microsoft. But I'm trying to implement the functions using T-SQL. So How can I do so?
CREATE DATABASE HomeInventory
USE HomeInventory
CREATE TABLE Item
(
ItemID int CONSTRAINT pk1 PRIMARY KEY IDENTITY NOT NULL,
LocationID int CONSTRAINT fk1 FOREIGN KEY REFERENCES dbo.Location(LocationID) NOT NULL,
Description varchar(50) NOT NULL,
Quantity int,
PurchaseDate smalldatetime,
SerialNumber varchar(50),
Cost smallmoney CONSTRAINT ck1 CHECK Cost >= 0,
ItemNotes varchar(250)
)
CREATE TABLE Location
(
LocationID int CONSTRAINT pk2 PRIMARY KEY IDENTITY NOT NULL,
Location varchar(50) NOT NULL
)
CREATE TABLE Photo
(
PhotoID int CONSTRAINT pk3 PRIMARY KEY IDENTITY NOT NULL,
ItemID int CONSTRAINT fk2 FOREIGN KEY REFERENCES dbo.Item(ItemID),
PhotoLocation varchar(100) NOT NULL,
PhotoCaption varchar(50),
PhotoNotes varchar(250)
)
CREATE INDEX Item_Serial_Number_IX
ON dbo.Item(SerialNumber ASC) INCLUDE (Description)
In my first table, I'm referencing a foreign key, but according I want to first create the table Item and then Location. So how do I apply the constraint later?
When I create this table I get dbo.Item, can I change or remove that dbo?
I can;t even access the table structure later by using sp_help dbo.Item. It generates an error saying syntax wrong at '.'
I'm using SQL Server 2005 Express Management Studio
Unsolved 
