Table creation: Difference between revisions

Content added Content deleted

Inline

Revision as of 14:55, 23 January 2007

In this task, the goal is to create a table to exemplify most commonly used data types and options.

PostgreSQL

-- This is a comment

CREATE SEQUENCE account_seq start 100;
CREATE TABLE account (
  account_id  int4 PRIMARY KEY DEFAULT nextval('account_seq'),
  created     date not null default now(),
  active      bool not null default 't',
  username    varchar(16) unique not null,
  age         int2,
  notes       text
);

CREATE TABLE account_note (
  account_id  int4 not null REFERENCES account,
  created     timestamp not null default now(),
  note        text not null,
  unique(account_id, note)
);

@@ Line 1: / Line 1: @@
 {{task}}
-In this task, the goal is to create a table to store addresses.
+In this task, the goal is to create a table to exemplify most commonly used data types and options.
-==[[UDB DB2]]==
+==[[PostgreSQL]]==
+ -- This is a comment
- CREATE TABLE Address (
- 	addrID		Integer		generated by default as identity,
+ CREATE SEQUENCE account_seq start 100;
- 	addrStreet	Varchar(50)	not null,
+ CREATE TABLE account (
- 	addrCity	Varchar(25)	not null,
+   account_id  int4 PRIMARY KEY DEFAULT nextval('account_seq'),
- 	addrState	Char(2)		not null,
- 	addrZIP		Char(10)	not null
+   created     date not null default now(),
+   active      bool not null default 't',
- )
+   username    varchar(16) unique not null,
+   age         int2,
-==[[MySQL]]==
+   notes       text
- CREATE TABLE `Address` (
-   `addrID`       int(11)     NOT NULL   auto_increment,
-   `addrStreet`   varchar(50) NOT NULL   default '',
-   `addrCity`     varchar(25) NOT NULL   default '',
-   `addrState`    char(2)     NOT NULL   default '',
-   `addrZIP`      char(10)    NOT NULL   default '',
-   PRIMARY KEY (`addrID`)
  );
+ CREATE TABLE account_note (
-==[[PostgreSQL]]==
+   account_id  int4 not null REFERENCES account,
+   created     timestamp not null default now(),
- CREATE SEQUENCE address_seq start 100;
+   note        text not null,
- CREATE TABLE address (
+   unique(account_id, note)
-   addrID   int4 PRIMARY KEY DEFAULT nextval('address_seq'),
-   street   varchar(50) not null,
-   city     varchar(25) not null,
-   state    varchar(2) not null,
-   zip      varchar(20) not null
  );