[SOLVED] sql database: table with 2 column (id name) and 2 primary key Third Normal Form Boyce-Codd Normal Form

sql database: table with 2 column (id name) and 2 primary key Third Normal Form Boyce-Codd Normal Form

Imagine the following table. In my case, I am completely sure that name needs to be unique and not null [unique+not null = primary key]. Therefore name is a primary key. For some reasons (probably by habit), I have naturally created a primary key id column of type int.

Other assumptions: I absolutely need to keep name in my table and I am absolutely sure that name (of type varchar) will never exceed 20 characters.

Now my first question is [probably close question with yes or no expected]: do I respect BCNF Boyce-Codd Normal Form if I create such a table?

Second optional question [probably open question]: is it good practice to create column id in this case?

  CREATE TABLE Y (
    id int,
    name varchar(20),
    PRIMARY KEY(id, name)
    );

Solution

If each column is unique, you probably want either

CREATE TABLE Y (
  id int primary key,
  name varchar(20) not null unique,
);

CREATE TABLE Y (
  id int not null unique,
  name varchar(20) not null unique
);

The FDs here are id->name and name->id.

Informally, you satisfy BCNF when every arrow in your FDs is an arrow out of a candidate key. So this satisfies BCNF.

It's not a good thing to create a surrogate id column out of habit. Think first, and make yourself aware of the tradeoffs and side effects.