Reference to primary key with constraints
0
I have 2 tables in my database:
CREATE TABLE Apartment
(
StreetName char(50) not null,
Number Integer not null,
Door Integer not null,
Type char(30) not null,
SizeSquareMeter Integer not null,
NID Integer not null FOREIGN KEY REFERENCES Neighborhood(NID)
CONSTRAINT Address PRIMARY KEY (StreetName, Number, Door)
);
CREATE TABLE Resident
(
RID Integer not null PRIMARY KEY,
FirstName varchar(30) not null,
LastName varchar(30) not null,
BirthDate date not null,
Address
);
Now the primary key in apartment is Address and is based on constraint. I want to create a foreign key Address in the Resident table referencing the
primary key Address in the Apartment table.
How can I do that?
sql primary-key
edited Nov 24 '18 at 21:18
marc_s
573k12811071255
asked Nov 24 '18 at 20:58
kal polakal pola
111
add a comment |
0
I have 2 tables in my database:
CREATE TABLE Apartment
(
StreetName char(50) not null,
Number Integer not null,
Door Integer not null,
Type char(30) not null,
SizeSquareMeter Integer not null,
NID Integer not null FOREIGN KEY REFERENCES Neighborhood(NID)
CONSTRAINT Address PRIMARY KEY (StreetName, Number, Door)
);
CREATE TABLE Resident
(
RID Integer not null PRIMARY KEY,
FirstName varchar(30) not null,
LastName varchar(30) not null,
BirthDate date not null,
Address
);
Now the primary key in apartment is Address and is based on constraint. I want to create a foreign key Address in the Resident table referencing the
primary key Address in the Apartment table.
How can I do that?
sql primary-key
edited Nov 24 '18 at 21:18
marc_s
573k12811071255
asked Nov 24 '18 at 20:58
kal polakal pola
111
You do not "reference" other constraints, you have to add StreetName,Number,Door to Resident. I would change this to use a sequence for apartment and add that to Resident similar to what you did with "RID"
– OldProgrammer
Nov 24 '18 at 21:02
A FK has a column list referencing a column list that forms a PK or UNIQUE key. A key declaration also delcares a constraint that has a(n explicit or implicit) constraint name. A FK doesn't reference a constraint. Read the manual or an introduction to see how you are not following it & how you should follow it.
– philipxy
Nov 24 '18 at 22:38
add a comment |
0
0
0
I have 2 tables in my database:
CREATE TABLE Apartment
(
StreetName char(50) not null,
Number Integer not null,
Door Integer not null,
Type char(30) not null,
SizeSquareMeter Integer not null,
NID Integer not null FOREIGN KEY REFERENCES Neighborhood(NID)
CONSTRAINT Address PRIMARY KEY (StreetName, Number, Door)
);
CREATE TABLE Resident
(
RID Integer not null PRIMARY KEY,
FirstName varchar(30) not null,
LastName varchar(30) not null,
BirthDate date not null,
Address
);
Now the primary key in apartment is Address and is based on constraint. I want to create a foreign key Address in the Resident table referencing the
primary key Address in the Apartment table.
How can I do that?
sql primary-key
edited Nov 24 '18 at 21:18
marc_s
573k12811071255
asked Nov 24 '18 at 20:58
kal polakal pola
111
I have 2 tables in my database:
CREATE TABLE Apartment
(
StreetName char(50) not null,
Number Integer not null,
Door Integer not null,
Type char(30) not null,
SizeSquareMeter Integer not null,
NID Integer not null FOREIGN KEY REFERENCES Neighborhood(NID)
CONSTRAINT Address PRIMARY KEY (StreetName, Number, Door)
);
CREATE TABLE Resident
(
RID Integer not null PRIMARY KEY,
FirstName varchar(30) not null,
LastName varchar(30) not null,
BirthDate date not null,
Address
);
Now the primary key in apartment is Address and is based on constraint. I want to create a foreign key Address in the Resident table referencing the
primary key Address in the Apartment table.
How can I do that?
sql primary-key
sql primary-key
edited Nov 24 '18 at 21:18
marc_s
573k12811071255
asked Nov 24 '18 at 20:58
kal polakal pola
111
edited Nov 24 '18 at 21:18
marc_s
573k12811071255
asked Nov 24 '18 at 20:58
kal polakal pola
111
edited Nov 24 '18 at 21:18
marc_s
573k12811071255
edited Nov 24 '18 at 21:18
marc_s
573k12811071255
edited Nov 24 '18 at 21:18
marc_s
573k12811071255
573k12811071255
asked Nov 24 '18 at 20:58
kal polakal pola
111
asked Nov 24 '18 at 20:58
kal polakal pola
111
asked Nov 24 '18 at 20:58
kal polakal pola
111
111
You do not "reference" other constraints, you have to add StreetName,Number,Door to Resident. I would change this to use a sequence for apartment and add that to Resident similar to what you did with "RID"
– OldProgrammer
Nov 24 '18 at 21:02
A FK has a column list referencing a column list that forms a PK or UNIQUE key. A key declaration also delcares a constraint that has a(n explicit or implicit) constraint name. A FK doesn't reference a constraint. Read the manual or an introduction to see how you are not following it & how you should follow it.
– philipxy
Nov 24 '18 at 22:38
add a comment |
You do not "reference" other constraints, you have to add StreetName,Number,Door to Resident. I would change this to use a sequence for apartment and add that to Resident similar to what you did with "RID"
– OldProgrammer
Nov 24 '18 at 21:02
A FK has a column list referencing a column list that forms a PK or UNIQUE key. A key declaration also delcares a constraint that has a(n explicit or implicit) constraint name. A FK doesn't reference a constraint. Read the manual or an introduction to see how you are not following it & how you should follow it.
– philipxy
Nov 24 '18 at 22:38
You do not "reference" other constraints, you have to add StreetName,Number,Door to Resident. I would change this to use a sequence for apartment and add that to Resident similar to what you did with "RID"
– OldProgrammer
Nov 24 '18 at 21:02
You do not "reference" other constraints, you have to add StreetName,Number,Door to Resident. I would change this to use a sequence for apartment and add that to Resident similar to what you did with "RID"
– OldProgrammer
Nov 24 '18 at 21:02
A FK has a column list referencing a column list that forms a PK or UNIQUE key. A key declaration also delcares a constraint that has a(n explicit or implicit) constraint name. A FK doesn't reference a constraint. Read the manual or an introduction to see how you are not following it & how you should follow it.
– philipxy
Nov 24 '18 at 22:38
A FK has a column list referencing a column list that forms a PK or UNIQUE key. A key declaration also delcares a constraint that has a(n explicit or implicit) constraint name. A FK doesn't reference a constraint. Read the manual or an introduction to see how you are not following it & how you should follow it.
– philipxy
Nov 24 '18 at 22:38
add a comment |
1 Answer
1
active
oldest
votes
0
Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key link.
So you need to change your Resident table to:
CREATE TABLE Resident
(
RID Integer not null PRIMARY KEY,
FirstName varchar(30) not null,
LastName varchar(30) not null,
BirthDate date not null,
StreetName char(50) not null,
Number Integer not null,
Door Integer not null,
);
and then you can add the foreign key constraint:
ALTER TABLE Resident
ADD CONSTRAINT FK_Resident_Apartment
FOREIGN KEY (StreetName, Number, Door) REFERENCES Apartment (StreetName, Number, Door);
The other option would be to add a surrogate column (an artificial, additional column) to Apartment - either as primary key, or (for SQL Server) at least with a unique constraint - so that you could establish the FK link to that surrogate column (using that one, single column - instead of having to replicate three columns of "real" data)
answered Nov 24 '18 at 21:18
marc_smarc_s
573k12811071255
Re "Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key": That isn't true. I guess you mean, wanting to reference that PK in apartment. That still isn't true. If there were a unique constraint on a subset of the PK then one could have a FK to that subset of it. So something like that is true, but not that. Also, the asker is confusing a PK with the associated constraint, see the comments the question.
– philipxy
Nov 25 '18 at 11:06
@philipxy: yes, IF there were a unique constraint on theApartmenttable, then one could reference that using a FK constraint (at least in SQL Server - the OP isn't clear on what RDBMS he's using). But there's no unique constraint in sight - only the composite PK - so the FK needs to match those columns
– marc_s
Nov 25 '18 at 11:56
But your sentence doesn't say "and no others". It's "since" is not sound.
– philipxy
Nov 25 '18 at 12:36
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%2f53462314%2freference-to-primary-key-with-constraints%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key link.
So you need to change your Resident table to:
CREATE TABLE Resident
(
RID Integer not null PRIMARY KEY,
FirstName varchar(30) not null,
LastName varchar(30) not null,
BirthDate date not null,
StreetName char(50) not null,
Number Integer not null,
Door Integer not null,
);
and then you can add the foreign key constraint:
ALTER TABLE Resident
ADD CONSTRAINT FK_Resident_Apartment
FOREIGN KEY (StreetName, Number, Door) REFERENCES Apartment (StreetName, Number, Door);
The other option would be to add a surrogate column (an artificial, additional column) to Apartment - either as primary key, or (for SQL Server) at least with a unique constraint - so that you could establish the FK link to that surrogate column (using that one, single column - instead of having to replicate three columns of "real" data)
answered Nov 24 '18 at 21:18
marc_smarc_s
573k12811071255
Re "Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key": That isn't true. I guess you mean, wanting to reference that PK in apartment. That still isn't true. If there were a unique constraint on a subset of the PK then one could have a FK to that subset of it. So something like that is true, but not that. Also, the asker is confusing a PK with the associated constraint, see the comments the question.
– philipxy
Nov 25 '18 at 11:06
@philipxy: yes, IF there were a unique constraint on theApartmenttable, then one could reference that using a FK constraint (at least in SQL Server - the OP isn't clear on what RDBMS he's using). But there's no unique constraint in sight - only the composite PK - so the FK needs to match those columns
– marc_s
Nov 25 '18 at 11:56
But your sentence doesn't say "and no others". It's "since" is not sound.
– philipxy
Nov 25 '18 at 12:36
add a comment |
0
Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key link.
So you need to change your Resident table to:
CREATE TABLE Resident
(
RID Integer not null PRIMARY KEY,
FirstName varchar(30) not null,
LastName varchar(30) not null,
BirthDate date not null,
StreetName char(50) not null,
Number Integer not null,
Door Integer not null,
);
and then you can add the foreign key constraint:
ALTER TABLE Resident
ADD CONSTRAINT FK_Resident_Apartment
FOREIGN KEY (StreetName, Number, Door) REFERENCES Apartment (StreetName, Number, Door);
The other option would be to add a surrogate column (an artificial, additional column) to Apartment - either as primary key, or (for SQL Server) at least with a unique constraint - so that you could establish the FK link to that surrogate column (using that one, single column - instead of having to replicate three columns of "real" data)
answered Nov 24 '18 at 21:18
marc_smarc_s
573k12811071255
Re "Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key": That isn't true. I guess you mean, wanting to reference that PK in apartment. That still isn't true. If there were a unique constraint on a subset of the PK then one could have a FK to that subset of it. So something like that is true, but not that. Also, the asker is confusing a PK with the associated constraint, see the comments the question.
– philipxy
Nov 25 '18 at 11:06
@philipxy: yes, IF there were a unique constraint on theApartmenttable, then one could reference that using a FK constraint (at least in SQL Server - the OP isn't clear on what RDBMS he's using). But there's no unique constraint in sight - only the composite PK - so the FK needs to match those columns
– marc_s
Nov 25 '18 at 11:56
But your sentence doesn't say "and no others". It's "since" is not sound.
– philipxy
Nov 25 '18 at 12:36
add a comment |
0
0
0
Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key link.
So you need to change your Resident table to:
CREATE TABLE Resident
(
RID Integer not null PRIMARY KEY,
FirstName varchar(30) not null,
LastName varchar(30) not null,
BirthDate date not null,
StreetName char(50) not null,
Number Integer not null,
Door Integer not null,
);
and then you can add the foreign key constraint:
ALTER TABLE Resident
ADD CONSTRAINT FK_Resident_Apartment
FOREIGN KEY (StreetName, Number, Door) REFERENCES Apartment (StreetName, Number, Door);
The other option would be to add a surrogate column (an artificial, additional column) to Apartment - either as primary key, or (for SQL Server) at least with a unique constraint - so that you could establish the FK link to that surrogate column (using that one, single column - instead of having to replicate three columns of "real" data)
answered Nov 24 '18 at 21:18
marc_smarc_s
573k12811071255
Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key link.
So you need to change your Resident table to:
CREATE TABLE Resident
(
RID Integer not null PRIMARY KEY,
FirstName varchar(30) not null,
LastName varchar(30) not null,
BirthDate date not null,
StreetName char(50) not null,
Number Integer not null,
Door Integer not null,
);
and then you can add the foreign key constraint:
ALTER TABLE Resident
ADD CONSTRAINT FK_Resident_Apartment
FOREIGN KEY (StreetName, Number, Door) REFERENCES Apartment (StreetName, Number, Door);
The other option would be to add a surrogate column (an artificial, additional column) to Apartment - either as primary key, or (for SQL Server) at least with a unique constraint - so that you could establish the FK link to that surrogate column (using that one, single column - instead of having to replicate three columns of "real" data)
answered Nov 24 '18 at 21:18
marc_smarc_s
573k12811071255
answered Nov 24 '18 at 21:18
marc_smarc_s
573k12811071255
answered Nov 24 '18 at 21:18
marc_smarc_s
573k12811071255
answered Nov 24 '18 at 21:18
marc_smarc_s
573k12811071255
573k12811071255
Re "Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key": That isn't true. I guess you mean, wanting to reference that PK in apartment. That still isn't true. If there were a unique constraint on a subset of the PK then one could have a FK to that subset of it. So something like that is true, but not that. Also, the asker is confusing a PK with the associated constraint, see the comments the question.
– philipxy
Nov 25 '18 at 11:06
@philipxy: yes, IF there were a unique constraint on theApartmenttable, then one could reference that using a FK constraint (at least in SQL Server - the OP isn't clear on what RDBMS he's using). But there's no unique constraint in sight - only the composite PK - so the FK needs to match those columns
– marc_s
Nov 25 '18 at 11:56
But your sentence doesn't say "and no others". It's "since" is not sound.
– philipxy
Nov 25 '18 at 12:36
add a comment |
Re "Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key": That isn't true. I guess you mean, wanting to reference that PK in apartment. That still isn't true. If there were a unique constraint on a subset of the PK then one could have a FK to that subset of it. So something like that is true, but not that. Also, the asker is confusing a PK with the associated constraint, see the comments the question.
– philipxy
Nov 25 '18 at 11:06
@philipxy: yes, IF there were a unique constraint on theApartmenttable, then one could reference that using a FK constraint (at least in SQL Server - the OP isn't clear on what RDBMS he's using). But there's no unique constraint in sight - only the composite PK - so the FK needs to match those columns
– marc_s
Nov 25 '18 at 11:56
But your sentence doesn't say "and no others". It's "since" is not sound.
– philipxy
Nov 25 '18 at 12:36
Re "Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key": That isn't true. I guess you mean, wanting to reference that PK in apartment. That still isn't true. If there were a unique constraint on a subset of the PK then one could have a FK to that subset of it. So something like that is true, but not that. Also, the asker is confusing a PK with the associated constraint, see the comments the question.
– philipxy
Nov 25 '18 at 11:06
Re "Since your primary key in Apartment is made up from three columns, any table wanting to reference Apartment will also need to have ALL three columns to establish a foreign key": That isn't true. I guess you mean, wanting to reference that PK in apartment. That still isn't true. If there were a unique constraint on a subset of the PK then one could have a FK to that subset of it. So something like that is true, but not that. Also, the asker is confusing a PK with the associated constraint, see the comments the question.
– philipxy
Nov 25 '18 at 11:06
@philipxy: yes, IF there were a unique constraint on the
Apartment table, then one could reference that using a FK constraint (at least in SQL Server - the OP isn't clear on what RDBMS he's using). But there's no unique constraint in sight - only the composite PK - so the FK needs to match those columns– marc_s
Nov 25 '18 at 11:56
@philipxy: yes, IF there were a unique constraint on the
Apartment table, then one could reference that using a FK constraint (at least in SQL Server - the OP isn't clear on what RDBMS he's using). But there's no unique constraint in sight - only the composite PK - so the FK needs to match those columns– marc_s
Nov 25 '18 at 11:56
But your sentence doesn't say "and no others". It's "since" is not sound.
– philipxy
Nov 25 '18 at 12:36
But your sentence doesn't say "and no others". It's "since" is not sound.
– philipxy
Nov 25 '18 at 12:36
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%2f53462314%2freference-to-primary-key-with-constraints%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
You do not "reference" other constraints, you have to add StreetName,Number,Door to Resident. I would change this to use a sequence for apartment and add that to Resident similar to what you did with "RID"
– OldProgrammer
Nov 24 '18 at 21:02
A FK has a column list referencing a column list that forms a PK or UNIQUE key. A key declaration also delcares a constraint that has a(n explicit or implicit) constraint name. A FK doesn't reference a constraint. Read the manual or an introduction to see how you are not following it & how you should follow it.
– philipxy
Nov 24 '18 at 22:38