1

I'm not certain how to title this question, but here's my question. I'm new to Databasing so this might be simple, but no one I've asked has a clue how to do it.

Is there a rule I can write in an access database that will link 3 entries to equal one unique value?

CREATE TABLE x(
_a_ int,
_b_ varchar(255),
_y_ int,
_z_ varchar(255)
);

i.e. the above table, I wish a, b, and y to be unique when looked at together, but each individual entry can be set non-uniquely. How would I go about linking tables with a rule like this when only one entry is present, or should I rewrite the tables to reflect this change?

I'm writing this code in VB.NET to access the database, I'm editing the database using access, and I have VS2012. I've been using OleDb to access the DB, but I suppose that's all mostly irrelavent since I'm asking about the database settings and SQL statements/rules as opposed to the programming.

NationWidePants
  • 447
  • 8
  • 33

1 Answers1

1

Try adding a CONSTRAINT:

CREATE TABLE x(
  _a_ int,
  _b_ varchar(255),
  _y_ int,
  _z_ varchar(255),
CONSTRAINT pk_Keys PRIMARY KEY (_a_, _b_, _y_)
);
LarsTech
  • 80,625
  • 14
  • 153
  • 225
  • 1
    LarsTech, a question about your constraints idea. As I said I'm new, but would a foreign key constraint work to allow each of a,b,y to be non-unique in a row, but constraint the overall table to unique values in a row when each are tied to identify a specific row? What I mean is, if 'a' is a primary key and b and y are foreign can I make 'a' non-unique and make rows unique to the tied values of each individual field on all tables? I'm sorry if my jargon is absent, improper or confusing. – NationWidePants Mar 14 '14 at 15:29
  • @NationWidePants Not sure I follow. If A is a PrimaryKey, it has to unique. – LarsTech Mar 14 '14 at 15:38
  • 1
    Ok, I wasn't aware that was required. I am looking for make entry unique to 3 fields. Any one field can match in a row, but the 3 together make a unique entry. So _a_ in one row can match _a_ in another row as long as _a_, _b_, and _y_ are not the same in the any rows. Is this a constraint best created using a foreign key constraint? – NationWidePants Mar 14 '14 at 16:00
  • @NationWidePants I don't know enough about your project to answer that. You can just use a unique constraint instead: `CONSTRAINT uq_Keys UNIQUE (A, B, Y)`. See [What's wrong with foreign keys?](http://stackoverflow.com/q/83147/719186) – LarsTech Mar 14 '14 at 16:04