How to make the combination of two column as primary key?

Question

Hi,
I made a table named staff_info containing 4 column name,day,duty_start,duty_finish. This table still have no parimary key. Now assume that there are 4 staff and they do their work in each day of the week.That means in each day there work 4 staff and 1 staff work in 7 days. Ultimately i can't either make name or day column as my primary key.But combination of two column value is unique.Is there any way that i can make the combination of these two column as any kind of primary key? I think i made myself clear.

Answer 1

What you're referring to is a composite primary key.



In SQL, something like:



CREATE TABLE staff_info

(

name,

day,

duty_start,

duty_finish,

PRIMARY KEY (name, day)

);



In Access table designer, you can control-click on the columns, and click the Key icon.

Answer 2

Access Primary Key Two Fields

Answer 3

What if a staff member has two duties on a day? One in the morning and one in the evening. Just name and day wouldn't be a good primary key in this case.



I would add another column named staff_id. Make it an integer, auto increment and the primary key and you're done.



If you still want to make name and day the PK, then this query should work.



ALTER TABLE staff_info

ADD PRIMARY KEY (name)

ADD PRIMARY KEY (day)

Answer 4

Access the Database Server

1. Locate the SQL Server Management Studio icon by clicking "Start", "All Programs", "Microsoft SQL Server 2005" (or 2008).

2. Select "Database Engine" from the Server Type drop-down menu.

3. Enter the server name or select it from the "Server name" drop-down menu (if it already exists).

4. Select the authentication option from the "Authentication" drop-down menu (either "Windows Authentication" or "SQL Server Authentication"). You are required to enter your user name and password if using "SQL Server Authentication".

5. Click "Connect". Set a Primary Key Using T-SQL

6. Click "New Query" from the "File" menu or "Standard" toolbar.

7. Type "ALTER TABLE dbo.Books ADD CONSTRAINT" and press "Enter" on your keyboard.

8. Type "PK_Books PRIMARY KEY CLUSTERED (ISBN)" and press "Enter" on your keyboard.

9. Type "WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]".

10. Click the "Execute" button on the SQL toolbar or press "F5" on your keyboard. Set a Primary Key Using the Table Designer

11. Expand the database containing the "[dbo].[Books]" table in the "Object Browser".

12. Right-click on the table and left-click "Design" in the menu that appears.

13. Right-click on the "ISBN" field and left-click "Set Primary Key" in the menu that appears.

14. Click the "Save" (disk) icon on the toolbar or use the "Ctrl + S" keyboard combination to save the changes.

Answer 5

Access the Database Server

1. Locate the SQL Server Management Studio icon by clicking "Start", "All Programs", "Microsoft SQL Server 2005" (or 2008).

2. Select "Database Engine" from the Server Type drop-down menu.

3. Enter the server name or select it from the "Server name" drop-down menu (if it already exists).

4. Select the authentication option from the "Authentication" drop-down menu (either "Windows Authentication" or "SQL Server Authentication"). You are required to enter your user name and password if using "SQL Server Authentication".

5. Click "Connect". Set a Primary Key Using T-SQL

6. Click "New Query" from the "File" menu or "Standard" toolbar.

7. Type "ALTER TABLE dbo.Books ADD CONSTRAINT" and press "Enter" on your keyboard.

8. Type "PK_Books PRIMARY KEY CLUSTERED (ISBN)" and press "Enter" on your keyboard.

9. Type "WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]".

10. Click the "Execute" button on the SQL toolbar or press "F5" on your keyboard. Set a Primary Key Using the Table Designer

11. Expand the database containing the "[dbo].[Books]" table in the "Object Browser".

12. Right-click on the table and left-click "Design" in the menu that appears.

13. Right-click on the "ISBN" field and left-click "Set Primary Key" in the menu that appears.

14. Click the "Save" (disk) icon on the toolbar or use the "Ctrl + S" keyboard combination to save the changes.

Answer 6

Yes, a primary key does not have to be a single column. See http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx