Defining a 1: 0 ...1 relationship in sql command

Question

I want to set up two tables in sql command.

I have a Customer table and a User Account table.

A Customer has none or at most 1 User_account (0...1) relationship.

But a USER_ACCOUNT will always have a CUSTOMER.

How can I do it via SQL command?

EDIT Here's what I have tried so far:

ALTER TABLE UserAccount DROP CONSTRAINT FKUserAccountToCustomer
GO

DROP TABLE Customer;
DROP TABLE UserAccount;
GO

CREATE TABLE Customer
(
    CustomerID INT NOT NULL IDENTITY,
    (...)
);
GO

CREATE TABLE UserAccount
(
    UserAccountID INT NOT NULL IDENTITY,
    CustomerID INT NOT NULL,
    (...)
);
GO

ALTER TABLE Customer ADD PRIMARY KEY (CustomerID);
GO

ALTER TABLE UserAccount ADD PRIMARY KEY(UserAccountID);
GO

IF NOT EXISTS (SELECT * FROM sysdiagrams WHERE name = 'FKUserAccountToCustomer')
BEGIN
    ALTER TABLE UserAccount
    ADD CONSTRAINT FKUserAccountToCustomer
    FOREIGN KEY(CustomerID)
    REFERENCES Customer(CustomerID)
END;

Questions asking for code must demonstrate a minimal effort. Please show us what you've tried. — Kermit, Jan 17 '14 at 14:59
It is unclear what you are trying to do. Do you want to create the tables? Or query existing data? — musefan, Jan 17 '14 at 15:01
@Philipp Now is it ok? I have rewrote the post to reflect the needs. — hsim, Jan 17 '14 at 15:10

score 1 · Accepted Answer · answered Jan 17 '14 at 15:21

1

If a customer can have 0 or 1 user account, then make the UserAccount Id the same as the Customer Id, enforced with primary key and foreign key constraints like so:

CREATE TABLE Customer
 (
   CustomerId     int  not null identity(1,1)
    constraint PK_Customer
     primary key clustered
   ,(...)
 )

CREATE TABLE UserAccount
 (
   UserAccountId  int  not null
    constraint PK_UserAccount
     primary key clustered
    constraint FK_UserAccount__Customer
     foreign key references Customer (CustomerId)
   ,(...)
 )

This way:

you cannot create a user account without first creating a customer
you cannot have two or more user accounts assigned to the same CustomerId
if "your" CustomerId is not in UserAccount, then you have no user account

answered Jan 17 '14 at 15:21

Philip Kelley

39,426
11
57
92

Wow! Ok, I will give it a try. – hsim Jan 17 '14 at 15:45
Interesting :) Seems like this did the job. What does the `clustered` words means? – hsim Jan 17 '14 at 15:56
1

Describing clustered vs. non-clustered indexes is a long discussion. Here's the SQL Books Online description: http://technet.microsoft.com/en-us/library/ms190457.aspx – Philip Kelley Jan 18 '14 at 00:22

score 0 · Answer 2 · answered Jan 17 '14 at 15:10

0

You can create a unique constraint to the CustomerId in UserAccount to ensure that Customer cannot have several account..

Should be doable like this

ALTER TABLE UserAccount ADD CONSTRAINT IX_Customer UNIQUE(CustomerId)

answered Jan 17 '14 at 15:10

C.Champagne

5,381
2
23
35

Following the way I wrote my script, how would you write it to make it unique? And how would you refer it to Customer? – hsim Jan 17 '14 at 15:15

Defining a 1: 0 ...1 relationship in sql command

2 Answers2