Business-Logic Relationships in SQL

Can anyone please tell me how can one estabilis开发者_StackOverflow中文版h 1 to 0..1 and 1 to 1..* relationships between tables in SQL (Server)?

Thank you very much.

1 to 1..*

Create a Foreign key from a parent table to the primary key of the child (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)
)

1 to 0..1

Create a table with the primary key also defined as a Foreign key to 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)

One to many

1. Define two tables (example A and B), with their own primary key
2. Define a column in Table A as having a Foreign key relationship based on the primary key of Table B

This means that Table A can have one or more records relating to a single record in Table B.

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

ALTER TABLE A ADD CONSTRAINT FOREIGN KEY fk_b ( b_id ) references 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