Re: Database design question

sreedhardasi@xxxxxxxxx wrote:

I have a database design questions. How can we specify one of two
columns in a table should be not null? In other words, if one column is

null then another column should not be null. Here is an example. Let's
say we have document and we need to specify permissions to the
document. The document has either individual or group level
permissions.

Document table
doc_id int primary,
doc_name varchar(50) not null


User table
user_id varchar(50) primary,
user_name varchar(100) not null


Group table
group_id int primary,
group_name varchar(50) not null


UserGroup table
user_group_id int primary,
group_id int not null,
user_id varchar(50) not null


DocumentPermission table
doc_id int,
group_id int,
user_id varchar(50)


So, either group_id or user_id should not be null in the
DocumentPermission table. Is there anyway we can have constraint like
this on the table? Is creating another group for that user the only
solution (Involves more administration)? Any help would be appreciated.



Thanks, Sreedhar

This is a simple CHECK constraint:

alter table DocumentPermission
add constraint SomeValidUser
check (group_id is not null or user_id is not null)

Also you should have:

alter table DocumentPermission
add constraint doc_id_fk
foreign key (doc_id)
references document(doc_id)

and similar statements for group_id and user_id.

You also need a primary key for DocumentPermission. Unless there is at
most one user and/or one group permitted to each document, you will want
all 3 _id columns to be part of the primary key, which means they must
all be declared NOT NULL which, in turn, violates your design. You
probably need 2 permission tables, DocumentUserPermission and
DocumentGroupPermission.
.

Relevant Pages

  • SQL Server confused about primary keys. So am I.
    ... We are generating a script to convert a client's database to Sql ... ALTER TABLE dbo.Categories ADD ... CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.server)
  • Re: Alter table help
    ... alter table table_name add constraint constraint_name primary key ... > "Tom Pennington" wrote in message ... >> Okay, I'm trying to modify a tables primary key, actually, I'm trying to ...
    (microsoft.public.sqlserver.programming)
  • Re: Drop Primary Key with SQL/VBA
    ... >> Syntax error in ALTER TABLE statement. ... > Do you want to drop the Primary Key constraint on a field..... ... > CREATE TABLE tblShipping ...
    (microsoft.public.access.queries)
  • Re: unknown symbol in ER diagram
    ... main_ID INTEGER NOT NULL PRIMARY KEY ... CONSTRAINT fk__sub1 FOREIGN KEY ... REFERENCES Main ... ALTER TABLE Sub1 ADD CONSTRAINT ch__sub1 ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Create Table Issues
    ... unique index on BookID. ... > CONSTRAINT PubPK PRIMARY KEY ... > CONSTRAINT PubFK FOREIGN KEY REFERENCES Publisher ...
    (microsoft.public.access.gettingstarted)