0

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

2
Contributors
1
Reply
2
Views
9 Years
Discussion Span
Last Post by Ramy Mahrous
0

I didn't understand you, but you can do anything(anything logic) after creating table using "alter" and in DB design we create the one table and after that the many tables..

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.