June 30, 2004 at 3:08 am
I have three tables defined as follows:
1. Table name = Companies_T, Columns = ID(PK - auto increment by 1), Name
2. Table name = Departments_T, Columns = ID(PK - auto increment by 1), Company_ID(PK), Name
3. Table name = Groups_T, Columns = ID(PK - auto increment by 1), Department_ID(PK), Name
I have defined the following relationships:
1. Companies_T.ID = Departments_T.Company_ID
2. Departments_T.ID = Groups_T.Department_ID
This all works well but my question is this, when I insert data into the Companies_T table I can specify the same company twice e.g. ID=1, Name=Test : ID=2, Name=Test. The same is true for the other tables. Although I cannot specify the automatically incremented ID field twice I can enter duplicate information for the other tables. So, what would be the easiest way to resolve this problem?
June 30, 2004 at 4:19 am
the problem here is that you have placed an auto number in the companies table and defined it as the primary key.
a primary key is defined as the entity that can be used to uniquly identify a row in a table.
if you want to use an autonumber as a primary key (to save on storage in your related tables) then you need to add a unique contraint to the Company name field in the company table.
MVDBA
June 30, 2004 at 4:43 am
Thanks Mike, so as well as defining the priamry key I must also add a constraint on the relevant columns in the three tables.
June 30, 2004 at 6:08 am
That's correct. if you want a field to not allow duplicates (that isn't the entire primary key) then you need a contraint.
if you are in the UK then visit
http://www.quantix-uk.com/database_storage/database_storage.html
you can get a free SQL server healthcheck.
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply