Referencing using T-SQL?

[GLT]

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

[hollystyles]

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

for primary keys (two syntactical options):
1.

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

or 2.

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:

constraint fkSomeName foreign key (ColumnName) references PrimaryTableName(ColumnName)

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

[GLT]

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?

[hollystyles]

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