Business Logical Relationships in SQL

Question

Can someone tell me how you can establish the relations 1 to 0..1 and 1 to 1..* between tables in SQL (Server)?

Thank you very much.

Answer 1

from 1 to 1 .. *

Create a foreign key from the parent table into the primary key of the child element (lookup table).

 CREATE TABLE A ( id int NOT NULL IDENTITY(1,1) PRIMARY KEY, Somecolumn int, SomeOtherColumn Varchar(50), B_id int CONSTRAINT FOREIGN KEY REFERENCES B(id), -- ...other columns ) CREATE TABLE B ( id int NOT NULL IDENTITY(1,1) PRIMARY KEY, Name Varchar(50) ) 

from 1 to 0..1

Create a table with a primary key, also defined as a foreign key for the parent table

 CREATE TABLE [Master] ( id int NOT NULL IDENTITY(1,1) PRIMARY KEY, Somecolumn int, SomeOtherColumn Varchar(50), -- ...other columns ) CREATE TABLE [Child] ( id int NOT NULL PRIMARY KEY, OtherColumn Varchar(50), ) ALTER TABLE Child ADD CONSTRAINT FK_Master FOREIGN KEY (id) REFERENCES Master(id) 

Answer 2

One for many

• Define two tables (examples A and B) with their own primary key
• Define the column in table A as having a foreign key relationship based on the primary key of table B

This means that table A can contain one or more records related to one record in table B.

If you already have tables, use the ALTER TABLE statement to create a foreign key constraint:

ALTER TABLE A ADD CONSTRAINT FOREIGN KEY fk_b (b_id) links b (id)

 * fk_b: Name of the foreign key constraint, must be unique to the database * b_id: Name of column in Table A you are creating the foreign key relationship on * b: Name of table, in this case b * id: Name of column in Table B