•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 402,860 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,918 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 1093 | Replies: 3
 |
•
•
Join Date: Aug 2007
Posts: 98
Reputation: 
Rep Power: 2
Solved Threads: 0
Junior Poster in Training
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
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
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 59
Question: Why are you explicitly allowing nulls in your primary key column?
for primary keys (two syntactical options):
1.
or 2.
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:
Your ON DELETE CASCADE ON UPDATE CASCADE should be fine and not need changing.
for primary keys (two syntactical options):
1.
BCustID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
constraint pkSomeName primary key (ColumnName)
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.
Last edited by hollystyles : Oct 29th, 2007 at 11:36 am.
•
•
Join Date: Aug 2007
Posts: 98
Reputation:
Rep Power: 2
Solved Threads: 0
Junior Poster in Training
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?
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?
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,165
Reputation:
Rep Power: 7
Solved Threads: 59
>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.
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 12:39 pm.
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Other Threads in the MS SQL Forum
- Previous Thread: Developing SQL Server 2000
- Next Thread: Problem
