How do I state my foreign key in my Create Table?
-
Hi Expert,
I tried to create a table with foreign key but I am not sure why it is giving me error -
Usually last time, I just have to write foreign key(departmentId) references tablename(departmentId).
Hope someone can advise me on this part how to write it so that there is no invalid column etc.
CREATE TABLE [dbo].[ePMT_Master_Employee](
[SGGId] [int] IDENTITY(1,1) unique not NULL,
[UserName] [nvarchar](50) NULL,
[RoleId] [int] not null,
[DeptId][int]not null,
primary key(SGGID),
foreign key(roleId) references (ePMT_Master_Role),
foreign key(departmentId)references (ePMT_Master_Department)); - karenworld - Sunday, February 19, 2017 1:26 AMHi Expert,
I tried to create a table with foreign key but I am not sure why it is giving me error -Usually last time, I just have to write foreign key(departmentId) references tablename(departmentId).
Hope someone can advise me on this part how to write it so that there is no invalid column etc.
CREATE TABLE [dbo].[ePMT_Master_Employee](
[SGGId] [int] IDENTITY(1,1) unique not NULL,
[UserName] [nvarchar](50) NULL,
[RoleId] [int] not null,
[DeptId][int]not null,
primary key(SGGID),
foreign key(roleId) references (ePMT_Master_Role),
foreign key(departmentId)references (ePMT_Master_Department));It looks like you need to specify what column contains the referenced primary key. I've also changed the name of your foreign key columns to match the columns names you create.
foreign key (RoleID) references dbo.ePMT_Master_Role(RoleID),
foreign key (DeptID) references dbo.ePMT_Master_Department (DepartmentID)); -
Normally when I create tables with foreign keys, I'll specify the foreign key as part of the column definition. Keeps everything in one place.
eg
CREATE TABLE Stations (
StationID INT IDENTITY PRIMARY KEY,
StarSystemID INT FOREIGN KEY REFERENCES dbo.StarSystems (StarSystemID) NOT NULL,
OfficialName VARCHAR(50) NOT NULL,
CommonName VARCHAR(50),
Planet TINYINT NOT NULL,
Location VARCHAR(15) NOT NULL
);Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Probably a silly question, but if you don't name the foreign key, how do you drop it (the easy way)? Can you name the constraint when you declare it that way?
- pietlinden - Sunday, February 19, 2017 12:57 PM
I had the same question. I have a standard naming convention. I know SQL is happy to assign a name, but I'd rather name it.
-
Ed,
Do you have an example of creating constraints the way Gail does, but naming them? The only examples I have seen require an ALTER TABLE ADD CONSTRAINT style... I searched around and didn't see any.
Thanks!
Pieter - pietlinden - Sunday, February 19, 2017 4:56 PM
Absolutely. Other than the name, the difference is that the constraint is defined separately from the column.
Of course, the real difference is that Gail's example of Stations and Star Systems is much cooler than my boring Employees table. 😛IF OBJECT_ID('dbo.Employees', 'u') IS NOT NULL DROP TABLE dbo.Employees;
IF OBJECT_ID('dbo.Departments', 'u') IS NOT NULL DROP TABLE dbo.Departments;CREATE TABLE dbo.Departments (
ID Integer not null identity (1, 1),
constraint Employees_PK PRIMARY KEY (ID),
Name Varchar(32) not null,
EntryDate Datetime,
UpdateDate Datetime);CREATE TABLE dbo.Employees (
ID Integer not null identity (1, 1),
constraint EmployeesNVC_PK PRIMARY KEY (ID),
FirstName Nvarchar(32) not null,
LastName Nvarchar(32) not null,
DepartmentID Integer not null,
CONSTRAINT Employees_Departments_FK
FOREIGN KEY (DepartmentID)
REFERENCES dbo.Departments (ID),
HireDate Datetime not null); - pietlinden - Sunday, February 19, 2017 12:57 PM
Yes you can. Just add CONSTRAINT <constrain name> in front of FOREIGN KEY
CREATE TABLE StarSystems (
StarSystemID INT IDENTITY PRIMARY KEY,
SectorID INT CONSTRAINT fk_blahblahblah FOREIGN KEY REFERENCES dbo.Sectors (SectorID) NOT NULL,
OfficialName VARCHAR(50) NOT NULL,
CommonName VARCHAR(50),
GalacticLatitude NUMERIC(5,2) NOT NULL,
GalacticLongitude NUMERIC(5,2) NOT NULL,
DistanceFromSol NUMERIC(7,2) NOT NULL,
SpectralType CHAR(2) NOT NULL,
NumberOfPlanets SMALLINT NOT NULL,
Magnitude NUMERIC(4,2) NOT NULL,
IsVariable BIT NOT NULL DEFAULT 0
);Ed Wagner - Sunday, February 19, 2017 9:02 PMIt's a new sample DB that I created for blog posts, articles, conference presentations, etc. Been in planning a long time, I finally say down last month and knocked it out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Oh, like that... (feeling stupid)... definitely easier to see if they're all together, but I get it. Thanks!
- karenworld - Sunday, February 19, 2017 1:26 AMHi Expert,
I tried to create a table with foreign key but I am not sure why it is giving me error -Usually last time, I just have to write foreign key(departmentId) references tablename(departmentId).
Hope someone can advise me on this part how to write it so that there is no invalid column etc.
CREATE TABLE [dbo].[ePMT_Master_Employee](
[SGGId] [int] IDENTITY(1,1) unique not NULL,
[UserName] [nvarchar](50) NULL,
[RoleId] [int] not null,
[DeptId][int]not null,
primary key(SGGID),
foreign key(roleId) references (ePMT_Master_Role),
foreign key(departmentId)references (ePMT_Master_Department));Yourdata model is pretty much wrong. There is no such thing as IDENTITYin RDBMS. This is a physical count of physical insertion attempts toa physical disk that holds the a table. It has nothing whatsoever todo with RDBMS. A table is supposed to model a set, and you have justtold us you have only one employee by the name of that table! I thinkyou probably meant to have a table that models personnel.Identifiers, by definition, cannot be numeric because there is nomath to ever done on them. We need to throw out everything and startover. Thinkabout "role_id"; what kind of role is there in this model?You are using very generic and therefore improper data element names.If you take a course in basic data modeling they would have beatenthis out of you during the first week of the class.
CREATETABLE Personnel
(user_idCHAR(10) NOT NULL PRIMARY KEY,
user_nameNVARCHAR(50) NOT NULL,
foobar_roleCHAR(4) NOT NULL
CHECK(foobar_role IN (..)),
dept_idCHAR(10) NOT NULL REFERENCES (Departments)
);
Unfortunately,this is still a mess. Have you ever read a book on RDBMS thatincluded the chapter on normalization? This table is not a realtable; users have a relationship with departments and departments arenot an attribute of a user! Think about it! Does a book grow out ofthe chest of an author? No, of course not! The author and his bookshave a relationship called authorship. Likewise personnel has arelationship with the departments to which each individual employeeis assigned (1:many relationship), called job assignment or whateveryour use it in your company.
Noticehow I use the CHECK() construct to assure data integrity. My rule isthat if the list of legal values and a constraint is short (whateverthat means currently) and static (whatever that currently means),then we use this construct. If the domain is dynamic or large, thenwe use a references. I seriously doubt that the departments in yourcompany should actually be in a references clause, but I needed toshow this for my example..
Noticehow I created the user ID as opposed to a count of physical insertionattempts on one machine, to one table on one product, etc. as you didin your original.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
Please disregard Joe Celko's rant. His design, although theorically correct, miss the physical design considerations. Using a CHAR(10) would add 6 bytes of storage to each row on Personnel and every time a column needs to reference this table or even more depending on the indexes. IDENTITY is part of the RDBMS, it's available and should be used appropriately.
The CHECK constraint is another subject that has been discussed with him and shown that it has many disadvantages.
He loves to berate about not following best practices, but there's so many things that can be improved in the code that he posts that I won't even start with the corrections. -
Joe I think you need to purchase a new keyboard. Your space bar seems to be broken. At least after berating the OP for an improper design you posted a complete disaster of a "correct" one. None of your code posted here would work and the lack of spaces in your text makes your entire post lose any credibility.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ - Luis Cazares - Tuesday, February 21, 2017 2:12 PM
Let me second what Luis said. There is an IDENTITY and it makes an excellent clustering key. It's what's known as an artificial key because it has nothing to do with the data. The other, a natural key, identifies the row just by being what it is. Granted, the natural key you pick may not be any good a few years down the road and it'll almost certainly add more than 4 or 8 bytes like Luis said. When you consider that every row of every nonclustered index inherits the bytes of the clustering key, keeping the clustering keys small becomes important. Remember, you have to read them and they have to be in memory to be able to use them.
I speak practical a lot better than I speak theoretical.
-
Ed, I agree with you 100%. Mr. Celko posts in a manner that makes people feel disrespected, and frankly, I'm sick of seeing it. It's a shame this forum did not retain the ability to filter out the posts of certain users. Yeah, you MIGHT miss something, but in this case, nothing of value. "Standards" in a field like RDBMS's are guidelines, and not necessarily rigid rules that must be followed or the death penalty is applied. He really needs to get off his high horse and deal with the real world as it is, instead of how he thinks it should be. And maybe, ... just maybe, he should think first instead of just rigidly follow his own "rules". Note that his profile says he's an author. Hope he makes enough to get by, because as a contractor doing actual database work, he wouldn't last long.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Wednesday, February 22, 2017 8:21 AM
LOL. You might look into Joe's resume before you say he wouldn't last long. 😉 From what everyone says he is the nicest person in real life. And he intentionally has an online persona of a derogatory arrogant bully. He has stated that is because of his way of thinking that coincides with some spiritual way of learning or some such rubbish. I think he truly means well but he has repeatedly been bashed by many people over the years to tone down his posts but to no avail. As they say, don't feed the trolls and they will eventually look for food elsewhere.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply