Ad:

MS SQL Discussion Thread
Unsolved
Views: 2503

Oct 29th, 2007

Referencing using T-SQL?

Expand Post »

my second thread of the day...

hey guys,

I was wondering if someone could help me. I am new to SQL Server therefore new to T-SQL, I have worked with SQL before. I am trying to reference foreign keys when creating tables. The code I have in SQL is :-

CREATE TABLE BCust (
BCustID INT IDENTITY NULL, BrokerID INT NULL,
CompanyName VARCHAR NULL,
ContactName VARCHAR NULL,
Address VARCHAR (50) NULL,
Address2 VARCHAR (50) NULL,
EmailAddress VARCHAR (30) NULL,
Telephone VARCHAR (20) NULL,
FaxNumber VARCHAR (20) NULL,
ExtraInfo TEXT NULL,
PRIMARY KEY (BCustID)
CONSTRAINT BrokerID FOREIGN KEY (BrokerID)
REFERENCE Broker (BrokerID)
ON DELETE CASCADE ON UPDATE CASCADE
);

How would I change this code for the reference part for T-SQL?

Thanks!

GLT

Similar Threads

SQL Server vs MYSQL vs MSQL (i'm stopping now) in MS SQL

GLT

Reputation Points: 18

Solved Threads: 0

Junior Poster in Training

Offline

98 posts
since Aug 2007

Permalink

Oct 29th, 2007

Re: Referencing using T-SQL?

Question: Why are you explicitly allowing nulls in your primary key column?

for primary keys (two syntactical options):
1.

(Toggle Plain Text)

BCustID INT IDENTITY(1,1) NOT NULL PRIMARY KEY

or 2.

MS SQL Syntax (Toggle Plain Text)
constraint pkSomeName PRIMARY KEY (ColumnName)
constraint pkSomeName primary key (ColumnName)

This will create a clustered primary key by default. There are lots of other options that can be also specified in this statement read books online for them all.

For foreign keys:

MS SQL Syntax (Toggle Plain Text)
constraint fkSomeName FOREIGN KEY (ColumnName) REFERENCES PrimaryTableName(ColumnName)
constraint fkSomeName foreign key (ColumnName) references PrimaryTableName(ColumnName)

Your ON DELETE CASCADE ON UPDATE CASCADE should be fine and not need changing.

Last edited by hollystyles; Oct 29th, 2007 at 12:36 pm.

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Permalink

Oct 29th, 2007

Re: Referencing using T-SQL?

Hi, thanks for your reply!

i have put:-
BCustID INT IDENTITY NOT NULL

I have left out (1,1) and I put PRIMARY KEY at the bottom as I didnt know I had to put this in here. What is the (1,1) for? will I have to alter the table to put it in, if so how? and does the PRIMARY KEY have to be entered at this stage instead if at the bottom?

GLT

Reputation Points: 18

Solved Threads: 0

Junior Poster in Training

Offline

98 posts
since Aug 2007

Permalink

Oct 29th, 2007

Re: Referencing using T-SQL?

>What is the (1,1) for?
It seeds the identity. Its beginning value and step. You could start at 10 and increment in steps of 10 for example (10,10) so inserts would go; 10,20,30,40 ...

>will I have to alter the table to put it in, if so how?
No I beleive it defaults to (1,1) anyway.

>and does the PRIMARY KEY have to be entered at this stage instead if at the bottom?
No it can be entered at the bottom if you like.

Last edited by hollystyles; Oct 29th, 2007 at 1:39 pm.

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Developing SQL Server 2000

Next Thread in MS SQL Forum Timeline: Problem

DaniWeb Message

Cancel Changes

User Name
Password