How Can I Create 1 To N Relationship Within A Create Table Statement In Mssql?

Question

0.00/5 (No votes)

See more:

how can i create 1 to many relationship within a create table statement in MSSQL?
where I've 5 create table queries??
please give me an example at your earliest.

Posted 28-Sep-15 8:29am

RTK The Limited Edition

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-09-28T08:41:00

Solution 1

If I understand the question correctly, you define a foreign key when creating the child table. For example

SQL

CREATE TABLE Parent (
  ParentId int          NOT NULL PRIMARY KEY,
  SomeData varchar(100) NOT NULL
);

CREATE TABLE Child (
  ChildId   int          NOT NULL PRIMARY KEY,
  ParentId  int          NOT NULL FOREIGN KEY REFERENCES Parent (ParentId),
  OtherData varchar(100) NOT NULL
);

...

For more information, see Create Foreign Key Relationships[^]

Posted 28-Sep-15 8:41am

Wendelius

Comments

[no name] 28-Sep-15 14:49pm

Now for about 70% I can agree (so a 5 is ok I think). But to be _very_ exactly: The primary key of the Detail table needs to be theoretically the Primary key of it's master and "something other" like a line number.
Bruno

[Edit]
I have repeatedly violated this law and later for several times punished
[/Edit]

Wendelius 28-Sep-15 15:07pm

Thanks for the comment.

I don't quite understand why a compound key would be required when creating a one-to-many relationship, could you explain a bit more?

[no name] 28-Sep-15 15:19pm

Quote: "I don't quite understand why a compound key would be required when creating a one-to-many relationship"... I don't belive realy that you don't understand this ... it is more probably my English :(

Maybe this explains,but I'm not sure about:https://en.wikipedia.org/wiki/Compound_key[^]

For me it is simply:
To make a Detail unique, one needs a compound key (Line number what ever else). A seperate/additional primary/sequence key I also add usually, but the _real primary_ is still the detail's reference to the primary and "something like" a line number of the Detail. Simply according to https://en.wikipedia.org/wiki/Referential_integrity[^]

Not really more informative.... ooooh my English...sorry.
Bruno

Wendelius 28-Sep-15 15:43pm

I'm still quite puzzled. I do know what a compound key is but what I don't understand is why the foreign key column should be part of the key of the child table. I can see that this could be a design principle of a specific system but in general I haven't heard of such requirement.

Let's take an example; You have a Person table (in this example it's a parent) and then you have an Order table. Among all other columns the Order table has these four columns:
- ReceivedBy, foreign key to Person, nullable
- PackedBy, foreign key to Person, nullable
- ShippedBy, foreign key to Person, nullable
- BilledBy, foreign key to Person, nullable

Now each one of these columns defines a separate one-to-many relationship between Person and Order, however, none of these is logically part of a key. After all the same combination is most likely to exist several times. So the order would probably have some completely other primary key.

Personally I like to use surrogate key[^]. If you think about the concept of a surrogate key it is forbidden to use any actual data as part of the key, otherwise it won't be surrogate. However, using a surrogate key does not mean that I cannot define a one-to-many relationship. The key of the child table is separate from the relationship definition.

Perhaps there's something I'm missing or misunderstood...

Oh, and from what I see there's nothing wrong with your English :)

[no name] 28-Sep-15 16:23pm

Dear Mika Wendelius
First of all, thank you very much that you are willing to discuss this with a low rep mem, I really appreciate this!

Now on a fist glance I would not "unite" "ReceivedBy", "PackedBy", "ShippedBy" and "BilledBy" in one and the same table. For me this are different actions, which Need also different Detail data.

Give me please "un rato" to answer more precisely.

Thank you, Bruno

Wendelius 28-Sep-15 16:59pm

With a low rep member? Why on earth would the amount of reputation affect my opinion on a member??? The amount of points you have (or do not have) tell absolutely nothing about your knowledge or you as a person, they are just points...

To be honest, some of the best comments I've got have come from "low rep" members with a lot of knowledge :)

However, I'm afraid we're flooding this question so if you don't mind let's continue the discussion elsewhere. I added a post in my personal profile page so when you have time feel free to post the comments over there.

Wendelius 28-Sep-15 16:08pm

I just realized: If you're only referring to the uniqueness of the detail table then I partially agree what you're saying. In that case what you're after is a natural key[^]. And yes, it's a good practice to always have at least one natural key. However, it's not mandatory.

Still, what comes to the foreign key definition there is no requirement that the foreign key must be part of the natural key. In many situations one of the foreign keys acts as part of the key but not in all cases.

The main idea of a natural key is to prevent logical duplicates (same person, same product, same order etc). Now if the key of the child table would contain the foreign key column to the master table plus the additional, generated line number this would give no more protection against duplicates as would a surrogate key. Each time a new child row would be added for the same parent a new line number would be generated.

Now if there would be a rule in the system that only one child row for a single parent row can be added per day then the foreign key column along with the date column could define a good natural key. But as said this highly depends on the system and the requirements.

[no name] 29-Sep-15 12:52pm

<blockquote class="quote"><div class="op">Quote:</div>I just realized: If you're only referring to the uniqueness of the detail table</blockquote> Yes, that was the thing I was "fixed on only" unfortunately :(

ok again, tags do not work always:
Quote "I just realized: If you're only referring to the uniqueness of the detail table"

Yes, that was the thing I was "fixed on only" unfortunately :( :)

Matt T Heffron 28-Sep-15 18:57pm

+5And, if I could, I would give +5 to both of you for the follow-up discussion as very informative, as well!

Wendelius 29-Sep-15 0:53am

Thank you sir!

[no name] 29-Sep-15 10:10am

Thank you, this is very motivating.