Sql, Create a foreign key to table with a primary key composed of multiple columns
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have the following table:
CREATE TABLE Appartment
(
SizeSquareMeter INT,
Type VARCHAR(30) NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
)
And I need to create the following table as well
CREATE TABLE Resident
(
RID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT Resident_Address
FOREIGN KEY (StreetName, Number, Door) REFERENCES Appartment(StreetName, Number, Door)
)
Now the above works but it duplicates the address and I don't want that, is there a way I can create a foreign key from Resident to Appartment without duplicating the address and without creating a new PRIMARY KEY for Appartment?
Note: whether it's important or not it basically should be valid Microsoft DDL.
sql
sql-server asked Nov 23 '18 at 20:39
Aviel FedidaAviel Fedida
1,70553973
add a comment |
0
I have the following table:
CREATE TABLE Appartment
(
SizeSquareMeter INT,
Type VARCHAR(30) NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
)
And I need to create the following table as well
CREATE TABLE Resident
(
RID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT Resident_Address
FOREIGN KEY (StreetName, Number, Door) REFERENCES Appartment(StreetName, Number, Door)
)
Now the above works but it duplicates the address and I don't want that, is there a way I can create a foreign key from Resident to Appartment without duplicating the address and without creating a new PRIMARY KEY for Appartment?
Note: whether it's important or not it basically should be valid Microsoft DDL.
sql
sql-server asked Nov 23 '18 at 20:39
Aviel FedidaAviel Fedida
1,70553973
1
No, you cannot - since your PK inAppartmentis made up of these three columns, any table referencing must also have those three columns to make the FK connection. The only way would be to use a surrogate primary key (an artificial, new e.g.AppartmentId) to avoid this
– marc_s
Nov 23 '18 at 21:03
@HimanshuAhuja I assume it can, basically I need to keep a unique combination of these 3 fields but if I add a different int field as a primary key, will the unique constraint hold with different primary keys having the same address fields?
– Aviel Fedida
Nov 23 '18 at 21:07
Assumption is wrong it wouldnt hold if additional fields are added with the composite unique key as it would result into redundancy somewhere around result is normalisation or seperate id which is either unique or primary key itself Unique constraint though will make sure that your foreign key doesnt get redundant data in it but adding other columns to it which are not primary key will still result in redundancy
– Himanshu Ahuja
Nov 23 '18 at 21:08
In actual for composite if you could make Primary key as Unique(All Fields) in one table Foriegn Key referencing (All fields ) to that table then only it ll be acheived else not.
– Himanshu Ahuja
Nov 23 '18 at 21:16
@HimanshuAhuja I'm not fully understanding, could you write this option as an answer?
– Aviel Fedida
Nov 23 '18 at 21:20
add a comment |
0
0
0
I have the following table:
CREATE TABLE Appartment
(
SizeSquareMeter INT,
Type VARCHAR(30) NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
)
And I need to create the following table as well
CREATE TABLE Resident
(
RID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT Resident_Address
FOREIGN KEY (StreetName, Number, Door) REFERENCES Appartment(StreetName, Number, Door)
)
Now the above works but it duplicates the address and I don't want that, is there a way I can create a foreign key from Resident to Appartment without duplicating the address and without creating a new PRIMARY KEY for Appartment?
Note: whether it's important or not it basically should be valid Microsoft DDL.
sql
sql-server asked Nov 23 '18 at 20:39
Aviel FedidaAviel Fedida
1,70553973
I have the following table:
CREATE TABLE Appartment
(
SizeSquareMeter INT,
Type VARCHAR(30) NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
)
And I need to create the following table as well
CREATE TABLE Resident
(
RID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT Resident_Address
FOREIGN KEY (StreetName, Number, Door) REFERENCES Appartment(StreetName, Number, Door)
)
Now the above works but it duplicates the address and I don't want that, is there a way I can create a foreign key from Resident to Appartment without duplicating the address and without creating a new PRIMARY KEY for Appartment?
Note: whether it's important or not it basically should be valid Microsoft DDL.
sql
sql-server sql
sql-server asked Nov 23 '18 at 20:39
Aviel FedidaAviel Fedida
1,70553973
asked Nov 23 '18 at 20:39
Aviel FedidaAviel Fedida
1,70553973
edited Nov 23 '18 at 21:02
Aviel Fedida
asked Nov 23 '18 at 20:39
Aviel FedidaAviel Fedida
1,70553973
asked Nov 23 '18 at 20:39
Aviel FedidaAviel Fedida
1,70553973
asked Nov 23 '18 at 20:39
Aviel FedidaAviel Fedida
1,70553973
1,70553973
1
No, you cannot - since your PK inAppartmentis made up of these three columns, any table referencing must also have those three columns to make the FK connection. The only way would be to use a surrogate primary key (an artificial, new e.g.AppartmentId) to avoid this
– marc_s
Nov 23 '18 at 21:03
@HimanshuAhuja I assume it can, basically I need to keep a unique combination of these 3 fields but if I add a different int field as a primary key, will the unique constraint hold with different primary keys having the same address fields?
– Aviel Fedida
Nov 23 '18 at 21:07
Assumption is wrong it wouldnt hold if additional fields are added with the composite unique key as it would result into redundancy somewhere around result is normalisation or seperate id which is either unique or primary key itself Unique constraint though will make sure that your foreign key doesnt get redundant data in it but adding other columns to it which are not primary key will still result in redundancy
– Himanshu Ahuja
Nov 23 '18 at 21:08
In actual for composite if you could make Primary key as Unique(All Fields) in one table Foriegn Key referencing (All fields ) to that table then only it ll be acheived else not.
– Himanshu Ahuja
Nov 23 '18 at 21:16
@HimanshuAhuja I'm not fully understanding, could you write this option as an answer?
– Aviel Fedida
Nov 23 '18 at 21:20
add a comment |
1
No, you cannot - since your PK inAppartmentis made up of these three columns, any table referencing must also have those three columns to make the FK connection. The only way would be to use a surrogate primary key (an artificial, new e.g.AppartmentId) to avoid this
– marc_s
Nov 23 '18 at 21:03
@HimanshuAhuja I assume it can, basically I need to keep a unique combination of these 3 fields but if I add a different int field as a primary key, will the unique constraint hold with different primary keys having the same address fields?
– Aviel Fedida
Nov 23 '18 at 21:07
Assumption is wrong it wouldnt hold if additional fields are added with the composite unique key as it would result into redundancy somewhere around result is normalisation or seperate id which is either unique or primary key itself Unique constraint though will make sure that your foreign key doesnt get redundant data in it but adding other columns to it which are not primary key will still result in redundancy
– Himanshu Ahuja
Nov 23 '18 at 21:08
In actual for composite if you could make Primary key as Unique(All Fields) in one table Foriegn Key referencing (All fields ) to that table then only it ll be acheived else not.
– Himanshu Ahuja
Nov 23 '18 at 21:16
@HimanshuAhuja I'm not fully understanding, could you write this option as an answer?
– Aviel Fedida
Nov 23 '18 at 21:20
1
1
No, you cannot - since your PK in
Appartment is made up of these three columns, any table referencing must also have those three columns to make the FK connection. The only way would be to use a surrogate primary key (an artificial, new e.g. AppartmentId) to avoid this– marc_s
Nov 23 '18 at 21:03
No, you cannot - since your PK in
Appartment is made up of these three columns, any table referencing must also have those three columns to make the FK connection. The only way would be to use a surrogate primary key (an artificial, new e.g. AppartmentId) to avoid this– marc_s
Nov 23 '18 at 21:03
@HimanshuAhuja I assume it can, basically I need to keep a unique combination of these 3 fields but if I add a different int field as a primary key, will the unique constraint hold with different primary keys having the same address fields?
– Aviel Fedida
Nov 23 '18 at 21:07
@HimanshuAhuja I assume it can, basically I need to keep a unique combination of these 3 fields but if I add a different int field as a primary key, will the unique constraint hold with different primary keys having the same address fields?
– Aviel Fedida
Nov 23 '18 at 21:07
Assumption is wrong it wouldnt hold if additional fields are added with the composite unique key as it would result into redundancy somewhere around result is normalisation or seperate id which is either unique or primary key itself Unique constraint though will make sure that your foreign key doesnt get redundant data in it but adding other columns to it which are not primary key will still result in redundancy
– Himanshu Ahuja
Nov 23 '18 at 21:08
Assumption is wrong it wouldnt hold if additional fields are added with the composite unique key as it would result into redundancy somewhere around result is normalisation or seperate id which is either unique or primary key itself Unique constraint though will make sure that your foreign key doesnt get redundant data in it but adding other columns to it which are not primary key will still result in redundancy
– Himanshu Ahuja
Nov 23 '18 at 21:08
In actual for composite if you could make Primary key as Unique(All Fields) in one table Foriegn Key referencing (All fields ) to that table then only it ll be acheived else not.
– Himanshu Ahuja
Nov 23 '18 at 21:16
In actual for composite if you could make Primary key as Unique(All Fields) in one table Foriegn Key referencing (All fields ) to that table then only it ll be acheived else not.
– Himanshu Ahuja
Nov 23 '18 at 21:16
@HimanshuAhuja I'm not fully understanding, could you write this option as an answer?
– Aviel Fedida
Nov 23 '18 at 21:20
@HimanshuAhuja I'm not fully understanding, could you write this option as an answer?
– Aviel Fedida
Nov 23 '18 at 21:20
add a comment |
2 Answers
2
active
oldest
votes
1
A foreign key reference can be to any unique key(s) in the table. So you can do:
CREATE TABLE Appartment (
AppartmentId int identity unique not null,
SizeSquareMeter INT,
Type VARCHAR(30) NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
);
CREATE TABLE Resident (
RID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
AppartmentId int not null,
CONSTRAINT Resident_Address
FOREIGN KEY (AppartmentId) REFERENCES Appartment(AppartmentId)
);
That said, I strongly think you should change the data structure so the primary key for the apartment table is an identity column.
answered Nov 24 '18 at 1:36
Gordon LinoffGordon Linoff
800k37321426
Thank you, I know I should change it, I'll see what I can do about that.
– Aviel Fedida
Nov 24 '18 at 9:02
add a comment |
1
You can use only specific Id to do the foreign key. It is breaking the normalization rule(when you using same detail in both tables). Basicly you can create a UniqueId on the Appartment table. Remove StreetName, Number and Door from the Resident table which is already registered in Appartment table. Add AppartmentId to the Resident with the foreign key. Here is the updated create sample :
CREATE TABLE Appartment(
ApartmentId INT NOT NULL ,
SizeSquareMeter int,
Type varchar(30) NOT NULL,
StreetName varchar(30) NOT NULL,
Number int NOT NULL,
Door int NOT NULL,
CONSTRAINT PK_Appartment PRIMARY KEY(ApartmentId)
)
CREATE TABLE Resident(
RID int PRIMARY KEY NOT NULL,
AppartmentId INT NOT NULL,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
BirthDate Date NOT NULL,
CONSTRAINT FK_Resident_Address FOREIGN KEY (AppartmentId) REFERENCES Appartment
)
answered Nov 23 '18 at 20:52
Zeki GumusZeki Gumus
1,445313
I already thought of this solution, that is why my question stateswithout creating a new PRIMARY KEY for Appartment, it's just that according to my specification that I cannot change the combination of 3 of the address fields compose the primary key, do you know a way around without manipulatingAppartment?
– Aviel Fedida
Nov 23 '18 at 20:59
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53452706%2fsql-create-a-foreign-key-to-table-with-a-primary-key-composed-of-multiple-colum%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
A foreign key reference can be to any unique key(s) in the table. So you can do:
CREATE TABLE Appartment (
AppartmentId int identity unique not null,
SizeSquareMeter INT,
Type VARCHAR(30) NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
);
CREATE TABLE Resident (
RID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
AppartmentId int not null,
CONSTRAINT Resident_Address
FOREIGN KEY (AppartmentId) REFERENCES Appartment(AppartmentId)
);
That said, I strongly think you should change the data structure so the primary key for the apartment table is an identity column.
answered Nov 24 '18 at 1:36
Gordon LinoffGordon Linoff
800k37321426
Thank you, I know I should change it, I'll see what I can do about that.
– Aviel Fedida
Nov 24 '18 at 9:02
add a comment |
1
A foreign key reference can be to any unique key(s) in the table. So you can do:
CREATE TABLE Appartment (
AppartmentId int identity unique not null,
SizeSquareMeter INT,
Type VARCHAR(30) NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
);
CREATE TABLE Resident (
RID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
AppartmentId int not null,
CONSTRAINT Resident_Address
FOREIGN KEY (AppartmentId) REFERENCES Appartment(AppartmentId)
);
That said, I strongly think you should change the data structure so the primary key for the apartment table is an identity column.
answered Nov 24 '18 at 1:36
Gordon LinoffGordon Linoff
800k37321426
Thank you, I know I should change it, I'll see what I can do about that.
– Aviel Fedida
Nov 24 '18 at 9:02
add a comment |
1
1
1
A foreign key reference can be to any unique key(s) in the table. So you can do:
CREATE TABLE Appartment (
AppartmentId int identity unique not null,
SizeSquareMeter INT,
Type VARCHAR(30) NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
);
CREATE TABLE Resident (
RID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
AppartmentId int not null,
CONSTRAINT Resident_Address
FOREIGN KEY (AppartmentId) REFERENCES Appartment(AppartmentId)
);
That said, I strongly think you should change the data structure so the primary key for the apartment table is an identity column.
answered Nov 24 '18 at 1:36
Gordon LinoffGordon Linoff
800k37321426
A foreign key reference can be to any unique key(s) in the table. So you can do:
CREATE TABLE Appartment (
AppartmentId int identity unique not null,
SizeSquareMeter INT,
Type VARCHAR(30) NOT NULL,
StreetName VARCHAR(30) NOT NULL,
Number INT NOT NULL,
Door INT NOT NULL,
CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
);
CREATE TABLE Resident (
RID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
BirthDate DATE NOT NULL,
AppartmentId int not null,
CONSTRAINT Resident_Address
FOREIGN KEY (AppartmentId) REFERENCES Appartment(AppartmentId)
);
That said, I strongly think you should change the data structure so the primary key for the apartment table is an identity column.
answered Nov 24 '18 at 1:36
Gordon LinoffGordon Linoff
800k37321426
answered Nov 24 '18 at 1:36
Gordon LinoffGordon Linoff
800k37321426
answered Nov 24 '18 at 1:36
Gordon LinoffGordon Linoff
800k37321426
answered Nov 24 '18 at 1:36
Gordon LinoffGordon Linoff
800k37321426
800k37321426
Thank you, I know I should change it, I'll see what I can do about that.
– Aviel Fedida
Nov 24 '18 at 9:02
add a comment |
Thank you, I know I should change it, I'll see what I can do about that.
– Aviel Fedida
Nov 24 '18 at 9:02
Thank you, I know I should change it, I'll see what I can do about that.
– Aviel Fedida
Nov 24 '18 at 9:02
Thank you, I know I should change it, I'll see what I can do about that.
– Aviel Fedida
Nov 24 '18 at 9:02
add a comment |
1
You can use only specific Id to do the foreign key. It is breaking the normalization rule(when you using same detail in both tables). Basicly you can create a UniqueId on the Appartment table. Remove StreetName, Number and Door from the Resident table which is already registered in Appartment table. Add AppartmentId to the Resident with the foreign key. Here is the updated create sample :
CREATE TABLE Appartment(
ApartmentId INT NOT NULL ,
SizeSquareMeter int,
Type varchar(30) NOT NULL,
StreetName varchar(30) NOT NULL,
Number int NOT NULL,
Door int NOT NULL,
CONSTRAINT PK_Appartment PRIMARY KEY(ApartmentId)
)
CREATE TABLE Resident(
RID int PRIMARY KEY NOT NULL,
AppartmentId INT NOT NULL,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
BirthDate Date NOT NULL,
CONSTRAINT FK_Resident_Address FOREIGN KEY (AppartmentId) REFERENCES Appartment
)
answered Nov 23 '18 at 20:52
Zeki GumusZeki Gumus
1,445313
I already thought of this solution, that is why my question stateswithout creating a new PRIMARY KEY for Appartment, it's just that according to my specification that I cannot change the combination of 3 of the address fields compose the primary key, do you know a way around without manipulatingAppartment?
– Aviel Fedida
Nov 23 '18 at 20:59
add a comment |
1
You can use only specific Id to do the foreign key. It is breaking the normalization rule(when you using same detail in both tables). Basicly you can create a UniqueId on the Appartment table. Remove StreetName, Number and Door from the Resident table which is already registered in Appartment table. Add AppartmentId to the Resident with the foreign key. Here is the updated create sample :
CREATE TABLE Appartment(
ApartmentId INT NOT NULL ,
SizeSquareMeter int,
Type varchar(30) NOT NULL,
StreetName varchar(30) NOT NULL,
Number int NOT NULL,
Door int NOT NULL,
CONSTRAINT PK_Appartment PRIMARY KEY(ApartmentId)
)
CREATE TABLE Resident(
RID int PRIMARY KEY NOT NULL,
AppartmentId INT NOT NULL,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
BirthDate Date NOT NULL,
CONSTRAINT FK_Resident_Address FOREIGN KEY (AppartmentId) REFERENCES Appartment
)
answered Nov 23 '18 at 20:52
Zeki GumusZeki Gumus
1,445313
I already thought of this solution, that is why my question stateswithout creating a new PRIMARY KEY for Appartment, it's just that according to my specification that I cannot change the combination of 3 of the address fields compose the primary key, do you know a way around without manipulatingAppartment?
– Aviel Fedida
Nov 23 '18 at 20:59
add a comment |
1
1
1
You can use only specific Id to do the foreign key. It is breaking the normalization rule(when you using same detail in both tables). Basicly you can create a UniqueId on the Appartment table. Remove StreetName, Number and Door from the Resident table which is already registered in Appartment table. Add AppartmentId to the Resident with the foreign key. Here is the updated create sample :
CREATE TABLE Appartment(
ApartmentId INT NOT NULL ,
SizeSquareMeter int,
Type varchar(30) NOT NULL,
StreetName varchar(30) NOT NULL,
Number int NOT NULL,
Door int NOT NULL,
CONSTRAINT PK_Appartment PRIMARY KEY(ApartmentId)
)
CREATE TABLE Resident(
RID int PRIMARY KEY NOT NULL,
AppartmentId INT NOT NULL,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
BirthDate Date NOT NULL,
CONSTRAINT FK_Resident_Address FOREIGN KEY (AppartmentId) REFERENCES Appartment
)
answered Nov 23 '18 at 20:52
Zeki GumusZeki Gumus
1,445313
You can use only specific Id to do the foreign key. It is breaking the normalization rule(when you using same detail in both tables). Basicly you can create a UniqueId on the Appartment table. Remove StreetName, Number and Door from the Resident table which is already registered in Appartment table. Add AppartmentId to the Resident with the foreign key. Here is the updated create sample :
CREATE TABLE Appartment(
ApartmentId INT NOT NULL ,
SizeSquareMeter int,
Type varchar(30) NOT NULL,
StreetName varchar(30) NOT NULL,
Number int NOT NULL,
Door int NOT NULL,
CONSTRAINT PK_Appartment PRIMARY KEY(ApartmentId)
)
CREATE TABLE Resident(
RID int PRIMARY KEY NOT NULL,
AppartmentId INT NOT NULL,
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
BirthDate Date NOT NULL,
CONSTRAINT FK_Resident_Address FOREIGN KEY (AppartmentId) REFERENCES Appartment
)
answered Nov 23 '18 at 20:52
Zeki GumusZeki Gumus
1,445313
answered Nov 23 '18 at 20:52
Zeki GumusZeki Gumus
1,445313
answered Nov 23 '18 at 20:52
Zeki GumusZeki Gumus
1,445313
answered Nov 23 '18 at 20:52
Zeki GumusZeki Gumus
1,445313
1,445313
I already thought of this solution, that is why my question stateswithout creating a new PRIMARY KEY for Appartment, it's just that according to my specification that I cannot change the combination of 3 of the address fields compose the primary key, do you know a way around without manipulatingAppartment?
– Aviel Fedida
Nov 23 '18 at 20:59
add a comment |
I already thought of this solution, that is why my question stateswithout creating a new PRIMARY KEY for Appartment, it's just that according to my specification that I cannot change the combination of 3 of the address fields compose the primary key, do you know a way around without manipulatingAppartment?
– Aviel Fedida
Nov 23 '18 at 20:59
I already thought of this solution, that is why my question states
without creating a new PRIMARY KEY for Appartment, it's just that according to my specification that I cannot change the combination of 3 of the address fields compose the primary key, do you know a way around without manipulating Appartment?– Aviel Fedida
Nov 23 '18 at 20:59
I already thought of this solution, that is why my question states
without creating a new PRIMARY KEY for Appartment, it's just that according to my specification that I cannot change the combination of 3 of the address fields compose the primary key, do you know a way around without manipulating Appartment?– Aviel Fedida
Nov 23 '18 at 20:59
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53452706%2fsql-create-a-foreign-key-to-table-with-a-primary-key-composed-of-multiple-colum%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
No, you cannot - since your PK in
Appartmentis made up of these three columns, any table referencing must also have those three columns to make the FK connection. The only way would be to use a surrogate primary key (an artificial, new e.g.AppartmentId) to avoid this– marc_s
Nov 23 '18 at 21:03
@HimanshuAhuja I assume it can, basically I need to keep a unique combination of these 3 fields but if I add a different int field as a primary key, will the unique constraint hold with different primary keys having the same address fields?
– Aviel Fedida
Nov 23 '18 at 21:07
Assumption is wrong it wouldnt hold if additional fields are added with the composite unique key as it would result into redundancy somewhere around result is normalisation or seperate id which is either unique or primary key itself Unique constraint though will make sure that your foreign key doesnt get redundant data in it but adding other columns to it which are not primary key will still result in redundancy
– Himanshu Ahuja
Nov 23 '18 at 21:08
In actual for composite if you could make Primary key as Unique(All Fields) in one table Foriegn Key referencing (All fields ) to that table then only it ll be acheived else not.
– Himanshu Ahuja
Nov 23 '18 at 21:16
@HimanshuAhuja I'm not fully understanding, could you write this option as an answer?
– Aviel Fedida
Nov 23 '18 at 21:20