MS SQL

Applying Constraints after creating a table.

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Aug 2007

Posts: 111

Reputation: ChaseVoid is an unknown quantity at this point

Solved Threads: 12

ChaseVoid

Offline

Junior Poster

Applying Constraints after creating a table.

Dec 1st, 2007

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?

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)
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

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: Applying Constraints after creating a table.

Dec 5th, 2007

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..

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous